PHP Return ID[uniqueidentifier] after sql INSERT

Question

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.

I would like to point out that @JohnConde's comment is inaccurate - this works with SQL Server but only if the column has an identity insert - since uniqueidentifier doesn't have this option, this method won't work. — Aaron Mason
– Aaron Mason, Commented Mar 14, 2024 at 22:46

웃웃웃웃웃 · Accepted Answer · 2013-11-22 14:12:43Z

1

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');

edited Nov 22, 2013 at 14:12

answered Nov 22, 2013 at 14:06

웃웃웃웃웃

12k15 gold badges63 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeroen Over a year ago

tried this, isn't working! In my case it should be; return $connect->lastInsertId('ID'); right?

웃웃웃웃웃 Over a year ago

Yes Corrected my answer @Jeroen

John Conde · Accepted Answer · 2013-11-22 14:05:52Z

0

Use lastInsertId

$conn->lastInsertId();

answered Nov 22, 2013 at 14:05

John Conde

220k100 gold badges464 silver badges504 bronze badges

Comments

Jeroen · Accepted Answer · 2013-11-25 12:12:38Z

0

(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.

answered Nov 25, 2013 at 12:12

Jeroen

2415 silver badges23 bronze badges

1 Comment

Aaron Mason Over a year ago

Something else that will work is

INSERT INTO <table> [(column1, column2, ... ,columnN)] OUTPUT inserted.id_column VALUES('value1', 'value2', ..., 'valueN')

rolf oosting · Accepted Answer · 2020-11-05 11:09:21Z

0

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

answered Nov 5, 2020 at 11:09

rolf oosting

1

2 Comments

Tomerikoo Over a year ago

Can you try to phrase this more as an answer than a question?

Useme Alehosaini Over a year ago

Welcome, and thank you @rolf oosting, what you wrote is more likely comment but not answer so rephrasing it in a more structured answer would be more organized.

Collectives™ on Stack Overflow

PHP Return ID[uniqueidentifier] after sql INSERT

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related