asked on

How to get and use the last inserted id in php?

Hi, I am trying to use the last inserted row id for another table to be able to reference it. This is what I have so far:

$query = <<<MySQL
   INSERT INTO $type (metal_id, color_id, name, caption, price, quantity)
   VALUES ($title, $description, $price, $qty)
   INSERT INTO images (ProductId, SRC)
   VALUES (PDO::lastInsertId(['id']), $primary)
   INSERT INTO images (ProductId, SRC)
   VALUES (PDO::lastInsertId(['id']), $other1)
MySQL;

Open in new window

I think my syntax for lastInsertId is wrong but I'm having trouble finding a good example for it. All the examples for it don't really seem to even use it... can someone help me with this?

SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FairyBusiness

ASKER

is there a way to store the LAST_INSERT_ID() as variable? bc I only want the last id of the first insert statement (therefore the last insert gets the wrong id of the one previous of it). I tried this but its not working:

$query = <<<MySQL
   INSERT INTO $type (name, caption, price, quantity)
   VALUES ($title, $description, $price, $qty);
   SET lastId = LAST_INSERT_ID();
   INSERT INTO images (ProductId, SRC)
   VALUES (lastId, $primary);
   INSERT INTO images (ProductId, SRC)
   VALUES (lastId, $other1);
MySQL;

Open in new window

Julian Hansen

That is why I gave two examples in my post - for that very reason - if you look at the second post you will see each query is run separately with a call to mysqli_insert_id() being assigned to $id I post again for reference

$query = <<<MySQL
   INSERT INTO $type (metal_id, color_id, name, caption, price, quantity)
   VALUES ($title, $description, $price, $qty)
MYSQL;

mysql_query($query);

//
// SAVING ID HERE
//

$id = mysql_insert_id();

// USING id HERE
$query = <<<MySQL
   INSERT INTO images (ProductId, SRC)
   VALUES ($id, $primary)
MySQL;

mysql_query($query);

// USING SAME id HERE AGAIN
$query = <<<MySQL
   INSERT INTO images (ProductId, SRC)
   VALUES ($id, $other1)
MySQL;

mysql_query($query);

Open in new window

FairyBusiness

ASKER

sorry I didnt get that before. I am trying to switch from mysql to pdo for my php so this is what I have so far:

$query = <<<MySQL
   INSERT INTO $type (name, caption, price, quantity)
   VALUES ($title, $description, $price, $qty);
MySQL;

    $stmt = $conn->prepare($query);
    if(!$stmt) die('Bad statement: ' . $conn->error);  
    $stmt->execute();    
    $lastId = $stmt->lastInsertId();
    echo $lastId;
    
$query = <<<MySQL
   INSERT INTO images (ProductId, SRC)
   VALUES ($lastId, $primary);
   INSERT INTO images (ProductId, SRC)
   VALUES ($lastId, $other1);
MySQL;

    $stmt = $conn->prepare($query);
    if(!$stmt) die('Bad statement: ' . $conn->error);    
    $stmt->execute();

Open in new window

Its not working though. Do you know how to get this working using pdo version?

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FairyBusiness

ASKER

thank you so much!