Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

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?
0
FairyBusiness
Asked:
FairyBusiness
  • 3
  • 3
2 Solutions
 
Julian HansenCommented:
try this
$query = <<<MySQL
   INSERT INTO $type (metal_id, color_id, name, caption, price, quantity)
   VALUES ($title, $description, $price, $qty)
MYSQL;

mysql_query($query);

$query = <<<MySQL
   INSERT INTO images (ProductId, SRC)
   VALUES (LAST_INSERT_ID(), $primary)
MySQL;

mysql_query($query);

$query = <<<MySQL
   INSERT INTO images (ProductId, SRC)
   VALUES (LAST_INSERT_ID(), $other1)
MySQL;

mysql_query($query)

Open in new window

Although that might not work if your images table has an autonumber on it.

You might need to do this
$query = <<<MySQL
   INSERT INTO $type (metal_id, color_id, name, caption, price, quantity)
   VALUES ($title, $description, $price, $qty)
MYSQL;

mysql_query($query);
$id = mysql_insert_id();

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

mysql_query($query);

$query = <<<MySQL
   INSERT INTO images (ProductId, SRC)
   VALUES ($id, $other1)
MySQL;

mysql_query($query);

Open in new window


Or you can create a stored procedure to do it.
0
 
FairyBusinessAuthor Commented:
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

0
 
Julian HansenCommented:
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

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
FairyBusinessAuthor Commented:
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?
0
 
Julian HansenCommented:
call the ->lastInsertId() with the pdo object not the statement
$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 = $conn->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

0
 
FairyBusinessAuthor Commented:
thank you so much!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now