0

I'm sorry about my PHP skills, but I'm just not figuring out how to do this simple task which is INSERT a new row and save its ID into a variable. Here's what I got:

    // mysql inserting a new row
$sql = "INSERT INTO `order` (orderTitle, orderDescription, orderPrice,userID, categoryID)
  VALUES('$title', '$description','$price','$userID','$category');";

$sql .= "SELECT LAST_INSERT_ID();";
$result = mysqli_multi_query($con,$sql);

$result_get_id= mysqli_next_result($con);
$row = mysqli_fetch_row($result_get_id);
$order_id = $row[0]; // <-- how to get this value??

I realized row[0] doesn't work, which is why I would like to know how to extract the LAST_INSERT_ID() value correctly.

jruivo
  • 447
  • 1
  • 8
  • 22
  • 3
    Any reason you're not using `mysqli_insert_id()`? – Barmar Apr 12 '17 at 23:13
  • I've never seen any code that justified using `mysqli_multi_query()`. IMHO, it just makes things confusing. Your question is no different. – Barmar Apr 12 '17 at 23:14
  • You should also use a prepared query instead of substituting variables into the SQL. Maybe the `INSERT` is getting an error. – Barmar Apr 12 '17 at 23:16
  • @Barmar I wasn't using mysqli_insert_id() because I didn't know about it, but that seems to have done the trick, so thank you very much. – jruivo Apr 12 '17 at 23:19

2 Answers2

2

A couple of things here...

  1. Don't use mysqli_multi_query - it's unnecessary in your example. Use mysqli_query on the INSERT only. No need to query last insert id in SQL.
  2. To get the last insert id, call mysqli_insert_id directly after your INSERT query. You can assign this to a variable, such as $order_id = mysqli_insert_id();
Rob W
  • 9,134
  • 1
  • 30
  • 50
0

The database class you're using has built in functions for this e.g. mysqli_insert_id(), or for PDO $db->lastInsertId().

$mysqli->query("INSERT INTO order ... ");
printf ("Primary key of new record: %d.\n", $mysqli->insert_id);

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

Goldbug
  • 605
  • 6
  • 8