Get last inserted id in oracle with php

Question

I have two tables created with this:

CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY);
CREATE TABLE t2 (id NUMBER GENERATED AS IDENTITY);

Im inserting some data into t1 and then i need to insert the ID generated in that insert into t2.

For example:

"insert into t1 (name, description) values ('asd','dsa')"
**getting that autogenerated ID
"insert into t2 (idFromt1,name) values (idFromt1,'sarasa')"

I saw many examples doing:

"insert into t1 (name, description) values ('asd','dsa') returning ID into inserted_id";

But i cant/dont know how to retrieve that "inserted_id" variable from php to make the second insert.

Answer 1

I was googling about PDO and come into this:

$qry="INSERT INTO t1 (NOMBRE) VALUES ('sarasa') returning ID into :inserted_id";

$parsed = oci_parse($this->conn, $qry);

oci_bind_by_name($parsed, ":inserted_id", $idNumber,32);
oci_execute($parsed);
echo $idNumber;

so i asumed oci_bind_by_name was one direction only, but no. That saves into the variable $idNumber the generated id and was exactly what i needed. Thanks all for your answers.

Answer 2

You can (should?) use the returning value.

For example, with PDO :

$query = "insert into t1 (name, description) values ('asd','dsa') returning ID";

$stmt = $pdo->query($query);

$id = $stmt->fetchColumn();

Answer 3

Use PDO::lastInsertID

Example:

$stmt = $db->prepare("...");
$stmt->execute();
$id = $db->lastInsertId();

Don't use

SELECT MAX(id) FROM table;

Because it's not safe if some other insert query will be executed before select and it will not work for you in a transaction.

Answer 4

None of these answers quite worked for me - I kept getting NULL out as my last inserted indentity. By chance (literally trying everything) I found that before you do:

$stmt->bindParam('idfoo', $idfoo, PDO::PARAM_INT, 8);

You need to set $idfoo as an integer. I don't know why. Like so:

$idfoo = (int)null;

It can be any integer but this is clearer for me that I am just "initialising" it to an empty value of a certain type.

Full example:

$query = "insert into t2 (name, description) values (:name, :description) returning ID into :idfoo";
$stmt = $connection->prepare($query);  

$name = 'adsjim';
$description = 'lead dev';

$params = array('name'=>$name, 'description'=>$description);
foreach ($params as $key=>$value){
      $stmt->bindValue($key,$value);
}
$idfoo = (int)null;
$stmt->bindParam('idfoo', $idfoo, PDO::PARAM_INT, 8);

$stmt->execute();
// insert id is now in the $idfoo variable
var_dump($idfoo);

The identity column is literally called ID in the database - replace with whatever your column is called.

Answer 5

This question is about PHP using PDO and Oracle. None of these solutions worked for me, Emepese's answer uses the native OCI driver instead of PDO, and PDO::lastInsertId does NOT work with Oracle.

Cid's answer got me the closest, along with an answer from how to get sequence id of last inserted record in oracle 11g xe with php?

But my situation was a little more complex. I already had a query with $stmt->prepare($query) and $stmt->execute(array('name'=>$name, 'desc'=>$desc)); To make it work, I wrote this:

$query = "insert into t1 (name, desc) values (:name, :desc) returning ID into :PK_ID";
// move params to an array:
$params = array('name'=>$name, 'desc'=>$desc);
foreach ($params as $key=>$value){
      $stmt->bindValue($key,$value);
}
$stmt->bindParam('PK_ID',$id,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,32);
// Important! Remove all parameters from the execute command:
$stmt->execute();
// insert id is now in the $id variable - do something with it
echo "I inserted $id!";

I hope this helps someone else save hours trying to play with Identity columns, Oracle Sequences, and bind parameters.

Answer 6

@Volod’s answer is correct, but here is a fuller example of how you would go about it using PDO.

$db = new PDO(…);                                               //  1
$prepared = 'insert into t1 (name, description) values (?,?)';  //  2
$prepared -> execute(['asd','dsa']);                            //  3
$id = $db -> lastInsertId();                                    //  4
//  etc

This example uses a prepared statement for safety.

1. Create a new PDO object connecting to the database.
2. Create a new PDOStatement object which contains a prepared statement. A prepared statement is interpreted without actual data; the question marks (?) are placeholders for the data. Even though they represent strings, the don’t include quotes.
3. Run the prepared statement with an array of values to be inserted.
4. Regardless of the actual DBMS, PDO will fetch the newest auto generated id with the lastInsertId() method.

One of the points of PDO is that it handles the differences between DBMSs by using its own methods. In this case, we don’t care how Oracle fetches the id, but rely on PDO to work it out.

Using prepared statements is important if you’re using data from an untrusted sources (such as a user) or if you need to run a statement multiple times. If you’re just running it once with trusted data, you can do it more simply:

$db = new PDO(…);
$db -> exec ("insert into t1 (name, description) values ('asd','dsa')';
$id = $db -> lastInsertId();
//  etc

Answer 7

Either use PDO::lastInsertId or

SELECT MAX(id) FROM table;