Return the id of the last MySQL insert in PHP

Question

I'm trying to grab the id of the last inserted auto-increment row and cannot successfully grab it.

error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$title = mysqli_real_escape_string($conxn,$_POST['blog_title']);
$entry = mysqli_real_escape_string($conxn,$_POST['blog_entry']);
$sourceName = mysqli_real_escape_string($conxn,$_POST['blog_source_name']);
$sourceLink = mysqli_real_escape_string($conxn,$_POST['blog_source_link']);

if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO blog (blog_title, blog_entry, blog_source, blog_link)
VALUES ('$title','$entry','$sourceName','$sourceLink')";

$lastID = $mysqli->insert_id;

if (!mysqli_query($conxn,$sql)) {
die('Error: ' . mysqli_error($conxn));
}

When I echo $lastID a "0" is returned after every submit.

1) Show code that sets `$mysqli`, and code that executes `$sql`. 2) Make sure you handle errors. 3) Stop concatenating values; use [bound parameters](http://us3.php.net/manual/en/mysqli-stmt.bind-param.php) instead. — Mike Sherrill 'Cat Recall', May 03 '14 at 02:03
Start by putting some quotes in `[blog_title]` as in `['blog_title']` then do the same for the others. Plus, add error reporting to the top of your file(s) `error_reporting(E_ALL); ini_set('display_errors', 1); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` — Funk Forty Niner, May 03 '14 at 02:11
thank you - error handling and single quotes added to the code above. mysqli wasn't defined in my connection script but now is. I do get a return now on echo but it's a constant "0" — user3053484, May 03 '14 at 02:25
It’s because your code is a mish-mash of competing methods. Check my latest edits to my answer, but you need to now make sure ALL of your calls to MySQLi commands are OOP. — Giacomo1968, May 03 '14 at 02:34

Giacomo1968 · Accepted Answer · 2014-05-03T02:36:16.967

You need to place the $mysqli->insert_id() after the actual mysqli_query(). See below.

if (!mysqli_query($conxn,$sql)) {
  die('Error: ' . mysqli_error($conxn));
}

$lastID = $mysqli->insert_id;

That said, there are other issues with your code. First & foremost, you are mixing up the Object oriented style of calling mysqli_* with the procedural style. For example the OOP method of $mysqli->real_escape_string equates to the procedural method of mysqli_real_escape_string.

So this:

$lastID = $mysqli->insert_id;

Should be this:

$lastID = mysqli_insert_id($conxn);

So without seeing the rest of your code, unclear how to handle. Know the difference & experiment. But here are my suggestions in good faith based on the code you have presented.

For example, your references to $_POST values do not have single quotes, so I added that. Also, since you are using double quotes—which handle string substitution—you can condense your INSERT variable setting by getting rid of the . concatenation.

$title = mysqli_real_escape_string($conxn, $_POST['blog_title']);
$entry = mysqli_real_escape_string($conxn, $_POST['blog_entry']);
$sourceName = mysqli_real_escape_string($conxn, $_POST['blog_source_name']);
$sourceLink = mysqli_real_escape_string($conxn, $_POST['blog_source_link']);

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO blog (blog_title, blog_entry, blog_source, blog_link)
VALUES ('$title','$entry','$sourceName','$sourceLink')";

if (!mysqli_query($conxn,$sql)) {
  die('Error: ' . mysqli_error($conxn));
}

$lastID = mysqli_insert_id($conxn);

That done, this code chunklet can be cleaned up even more, and this is how I would handle it. I have made an array of the $_POST values you are grabbing so you don’t have to repeat code. Also added comments to make it clearer what is happening. And I have used the procedural format for all commands here. If OOP is what you want, then you need to change all of the commands to match OOP format.

// Set all of the `$_POST` values into an array.
$post_items = array('blog_title','blog_entry','blog_source_name', 'blog_source_link');

// Roll through those values with a `foreach` loop.
foreach ($post_items as $post_item) {
  $$post_item = mysqli_real_escape_string($conxn, $_POST[$post_item]);
}

// MySQL connection error check.
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Set the SQL values.
$sql = "INSERT INTO blog (blog_title, blog_entry, blog_source, blog_link)
VALUES ('$blog_title','$blog_entry','$blog_source_name','$blog_source_link')";

// Run the query.
if (!$mysqli_query($conxn, $sql)) {
  die('Error: ' . mysqli_error($conxn));
}

// Get the last insert ID via object oriented method.
// $lastID = $mysqli->insert_id;

// Get the last insert ID via procedural method.
$lastID = mysqli_insert_id($conxn);

thank you - i've cleaned up the insert to add the straight variables. — user3053484, May 03 '14 at 02:26
awesome, thanks for the array - i had a note to go back through and try to clean the repetition up through my pages. this is perfect and helps me out a ton. thank you again! I'm returning "0" now on $lastID so I think it's moving in the right direction. — user3053484, May 03 '14 at 02:29
@user3053484 Check my latest edits. Your key issue seems to be mixing up OPP format with procedural. So use `mysqli_insert_id` instead of `$mysqli->insert_id`. — Giacomo1968, May 03 '14 at 02:32
that's terrific! works perfectly now. thanks again for all the help and sparing time for a newb. off to figure out how to start writing that $lastID to a lookup table. :) — user3053484, May 03 '14 at 02:36

Return the id of the last MySQL insert in PHP

1 Answers1