从一个表中获取最后一个插入ID以放入另一个表中

Question

probably a simple one for you developers out there

I have this code to insert an order_id and order_name into the 'orders' table:

<?php
// start the session handler
require_once('dbfunction.php');

//connect to database
$conn = DB();

require_once('header.php');

//should we process the order?
if (isset($_POST['process'])) {

$order_name = $_POST['order_name'];

//create initial order
$stmt = $conn2->prepare("INSERT INTO orders (order_name) VALUES (?)");

//bind the parameters
    $stmt->bind_param('s', $order_name);

    // Execute query
    $stmt->execute();

I now want to insert the order items into the order_items table and I cant seem to keep that same ID that was created when inserting into the 'orders' table and add it to the 'order_items' table along with the order_items. Here is my code:

//this gets the most recent auto incremented ID from the database - this is the order_id we have just created
$order_id = mysql_insert_id();

//loop over all of our order items and add to the database
foreach ($_SESSION['order'] as $item) {

  $prod_id = $item['prod_id'];
  $quantity = $item['quantity'];
  $prod_type = $item['prod_type'];

  $stmt = $conn2->prepare("INSERT INTO order_items (order_id, prod_id, quantity, prod_type) VALUES (?, ?, ?, ?)");

  //bind the parameters
    $stmt->bind_param('iiis', $order_id, $prod_id, $quantity, $prod_type);

    // Execute query
    $stmt->execute();
}

    echo "<p class='black'>Order Processed</p>";

Answer 1

I would guess it's because whatever database library you are using is doing something to invalidate the mysql_insert_id (assuming it's even using the mysql functions). I'd suggest you look into the library to find out what method they suggest you use instead.