Retrieving the last insert id from MySql using PHP

Question

I have two tables in MySql database, one is sales_order and the other is sales_order_details which contains the details of the order in the sales_order table. Obviously, there is a one to many relationship from sales_order to sales_order_details.

When a customer places an order, the first entry is made into the sales_order table and based on the auto_increment id of the sales_order table, the corresponding entry is made into the sales_order_details table.

I'm using the last_insert_id() MySql function to retrieve the corresponding order_id from the sales_order table, Something like this.

insert into sales_order_details(order_id, prod_id, prod_price)values(last_insert_id(), 5, 1500);

It's working. The last_insert_id() function retrieves the last inserted id from the respective table which is unique to a particular connection (as far as I know).

Now, I need the same order_id which is retrieved and inserted most recently by the last_insert_id() function to send it as an invoice number to a payment system.

I can try using the PHP function mysql_insert_id() but I'm not sure whether it works as specified. What is the correct way to retrieve the last insert id which always guarantees to retrieve the specific id which is always associated with a particular order?

Answer 1

 1. Database

    CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
    )


-------------------------------------------------------------------
  1.   Example (MySQLi Object-oriented)

-------------------------------------------------------------------
    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";

    if ($conn->query($sql) === TRUE) {
        $last_id = $conn->insert_id;
        echo "New record created successfully. Last inserted ID is: " . $last_id;
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();
    ?>

------------------------------------------------------------------- 
   2.  Example (MySQLi Procedural)

-------------------------------------------------------------------
    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";

    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";

    if (mysqli_query($conn, $sql)) {
        $last_id = mysqli_insert_id($conn);
        echo "New record created successfully. Last inserted ID is: " . $last_id;
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

    mysqli_close($conn);
    ?>

------------------------------------------------------------------- 
  3.  Example (PDO)

-------------------------------------------------------------------
    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDBPDO";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "INSERT INTO MyGuests (firstname, lastname, email)
        VALUES ('John', 'Doe', 'john@example.com')";
        // use exec() because no results are returned
        $conn->exec($sql);
        $last_id = $conn->lastInsertId();
        echo "New record created successfully. Last inserted ID is: " . $last_id;
        }
    catch(PDOException $e)
        {
        echo $sql . "<br>" . $e->getMessage();
        }

    $conn = null;
    ?> 

Answer 2

Both *LAST_INSERT_ID()* and *mysql_insert_id()* work as advertised i.e.: they will retrieve the last id inserted into any table during the current session/connection.

As long as you do not insert into more than one auto incremented table before retrieving this id, you're sure it's the correct one.

Also be careful when inserting multiple values into the same table, you'll receive the value for the first one of them but they are not necessarily consecutive (a different session/connection could have inserted some new records too).

I usually like to do things like this:

START TRANSACTION;
    INSERT INTO sales_order ...;
    SET @last_order = LAST_INSERT_ID();
    INSERT INTO sales_order_details (order_id) VALUES ($last_order);
    SELECT @last_order;
COMMIT;

The result-set of this query should contain a single column and a single row holding the value of the last order.

But then again I usually do that for transactional safety of updates most of all.

START TRANSACTION;
    SELECT * FROM sales_order WHERE order_id = 2 FOR UPDATE;
    UPDATE sales_order_details SET quantity = 9 WHERE order_id = 2 AND prod_id = 3;
    UPDATE sales_order SET price_to_pay = (SELECT SUM(quantity*price) FROM sales_order_details WHERE order_id = 2);
COMMIT;

The transaction should ensure the operations are atomic (all done without interruption by other processes);

If you were to do the same without a transaction or from the application code, the quantity might be updated by another thread and read by a third thread before you were done updating the price. Yielding a false "price_to_pay" to the user.

Answer 3

There are 2 ways which you could solve this:

After inserting the entry in the sales_order:

1. Get the last_insert_id and store it in a php variable, then inject this value into the relevant queries.

2. Store the last_insert_id in a MySql user-defined variable and use the variable in the query instead of last_insert_id.

sample code for option 2

SET @last_order_id = last_insert_id();

insert into sales_order_details (order_id, prod_id, prod_price)
values (@last_order_id, 5, 1500);`

insert into sales_invoice (order_id, invoice_id)
values (@last_order_id, 1);`

Answer 4

mysql_insert_id( ) always returns the id generated for an AUTO_INCREMENT collumn by the previous query. (See the PHP Manual)

I don't understand exactly which id you want to fetch, but in order to retrieve it using PHP you should always do so directly after the query. For example:

// Query A

mysql_query( 'INSERT INTO table_a ( id, name ) VALUES ( NULL, "Henry" )' );
$idA = mysql_insert_id( );

// Query B

mysql_query( 'INSERT INTO table_b ( id, name, a_id ) VALUES ( NULL, "Wotton", ' . $idA . ' )' );
$idB = mysql_insert_id( );

// Query C
mysql_query( 'INSERT INTO table_c ( id, name, a_id ) VALUES ( NULL, "Rick", ' . $idA . ' )' );

The advantage of storing them in a PHP variable is that your system or framework can't mess up your queries by performing another query while you are not aware of it.

Answer 5

Just use mysql_insert_id() right after insering sales_order. You'll be fine.

Answer 6

You would want to restructure you queries just a bit.

1. Insert the record into the sales_order table
2. Use the php function mysql_insert_id() to retreive the id of the sales_order record: $sales_order_id = mysql_insert_id()
3. Rewrite your insert like so: `insert into sales_order_details(order_id, prod_id, prod_price)values($sales_order_id, 5, 1500);

Answer 7

http://ca.php.net/manual/en/mysqli.insert-id.php

is the property you are looking for. It is completely reliable.

mysql_insert_id (and all mysql_ functions) is deprecated.