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.

Answer 1

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);