CODEWITHSUNDEEP
phpsqlsql-serveruniqueidentifier
Jeroen
Jeroen

Reputation: 241

PHP Return ID[uniqueidentifier] after sql INSERT

Like the title says, I want the id of a row to be returned after INSERT to the database.

I've got 2 functions, one to make a connection to the database:

function db_connect() {
    $host = "host";
    $user = "user";
    $pwd = "pwd";
    $database = "db";
    $con;
    try{
        $conn = new PDO( "sqlsrv:Server= ".$host." ; Database = ".$database." ", $user, $pwd);
    }
    catch(Exception $e){
        die(print_r($e));
    }
    return $conn;
}

And one to insert a new record:

function setTiptile($name,$cols,$rows) {
    $connect = db_connect();
    $query = "INSERT INTO data(ID, name, cols, rows) VALUES(NEWID(),?,?,?)";
    $stmt = $connect->prepare($query);
    $stmt->bindValue(1, $name);
    $stmt->bindValue(2, $cols);
    $stmt->bindValue(3, $rows);
    $stmt->execute();


    return $connect->lastInsertId('ID'); // This should work, but doesn't, why?

}

I need the last function to return the ID of the inserted row, how should I do this?

EDIT: Like the title says, the ID is an uniqueidentifier, no idea if that changes things.

EDIT: Ok, apparently I've got to use:$connect->lastInsertId('ID');, but this isn't returning anything at all. What can be the cause of that? The new row ís created in the database.

Upvotes: 0

Views: 1666

Answers (4)

rolf oosting
rolf oosting

Reputation: 1

why not first do a select newid() and then use that id in the insert

Upvotes: 0

Jeroen
Jeroen

Reputation: 241

(My) solution:

The parameter of lastInsertId() has to be the table name instead of the row name. Besides that, the table must have a row with the parameter IDENTITY checked, this is the row which is returned.

That brings to the conclusion that it's impossible to return a uniqueidentifier with lastInsertId() since this cannot have the parameter IDENTITY checked.

Upvotes: 0

웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

From the Manual:

Returns the ID of the last inserted row, or the last value from a sequence object, depending on the underlying driver. For example, PDO_PGSQL() requires you to specify the name of a sequence object for the name parameter.

It should be something like:

return $connect->lastInsertId('yourIdColumn');

Upvotes: 1

John Conde
John Conde

Reputation: 219804

Use lastInsertId

$conn->lastInsertId();

Upvotes: 0

Related Questions