Return the id of the last MySQL insert in PHP

user3053484

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.

Giacomo1968

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Both sql LAST_INSERT_ID() and PHP insert_id return 0

From Dev

How to get last insert id from mysql database in php?

From Dev

Get last insert id return null

From Dev

MYSQL last_insert_id() and concurrency

From Dev

LAST_INSERT_ID after restarting MySQL

From Dev

MYSQL last_insert_id() and concurrency

From Dev

MySQL - Return group of last inserted ID's

From Dev

How to do a multiple insert using LAST_INSERT_ID() MySQL?

From Dev

How to do a multiple insert using LAST_INSERT_ID() MySQL?

From Dev

PHP : return last inserted Id on statement

From Dev

MySQL - Insert values if not exists or update and return id

From Dev

How to Get last Insert Id in php without insert any data?

From Dev

PDO not being able to return the last_insert_id

From Dev

Can't return the last known ID after an INSERT statement

From Dev

Last insert id of a database table by insert_id() not Return to Controller file from Model in Codeigniter

From Dev

Get last inserted id in PHP and MySQL

From Dev

MySQL: Get ID if exists, else insert and return ID

From Dev

MySQL LAST_INSERT_ID() - how does it works

From Dev

Thread safety of MySQL's Select Last_Insert_ID

From Dev

MySQL: Get last update id from a table to insert it in an other table

From Dev

MySQL : Stored procedure returns null for last_insert_id

From Dev

MySQL retrieve last_insert_id from stored procedure

From Dev

MySQL: Can't get LAST_INSERT_ID()

From Dev

MySQL Function with LAST_INSERT_ID() giving unexpected results

From Dev

MySQL: Get last update id from a table to insert it in an other table

From Dev

Getting ID of last insert

From Dev

how to get LAST_INSERT_ID via stored procedure in php

From Dev

Using select last_insert_id() from php not working

From Dev

How to get last id after insert a value in php

Related Related

  1. 1

    Both sql LAST_INSERT_ID() and PHP insert_id return 0

  2. 2

    How to get last insert id from mysql database in php?

  3. 3

    Get last insert id return null

  4. 4

    MYSQL last_insert_id() and concurrency

  5. 5

    LAST_INSERT_ID after restarting MySQL

  6. 6

    MYSQL last_insert_id() and concurrency

  7. 7

    MySQL - Return group of last inserted ID's

  8. 8

    How to do a multiple insert using LAST_INSERT_ID() MySQL?

  9. 9

    How to do a multiple insert using LAST_INSERT_ID() MySQL?

  10. 10

    PHP : return last inserted Id on statement

  11. 11

    MySQL - Insert values if not exists or update and return id

  12. 12

    How to Get last Insert Id in php without insert any data?

  13. 13

    PDO not being able to return the last_insert_id

  14. 14

    Can't return the last known ID after an INSERT statement

  15. 15

    Last insert id of a database table by insert_id() not Return to Controller file from Model in Codeigniter

  16. 16

    Get last inserted id in PHP and MySQL

  17. 17

    MySQL: Get ID if exists, else insert and return ID

  18. 18

    MySQL LAST_INSERT_ID() - how does it works

  19. 19

    Thread safety of MySQL's Select Last_Insert_ID

  20. 20

    MySQL: Get last update id from a table to insert it in an other table

  21. 21

    MySQL : Stored procedure returns null for last_insert_id

  22. 22

    MySQL retrieve last_insert_id from stored procedure

  23. 23

    MySQL: Can't get LAST_INSERT_ID()

  24. 24

    MySQL Function with LAST_INSERT_ID() giving unexpected results

  25. 25

    MySQL: Get last update id from a table to insert it in an other table

  26. 26

    Getting ID of last insert

  27. 27

    how to get LAST_INSERT_ID via stored procedure in php

  28. 28

    Using select last_insert_id() from php not working

  29. 29

    How to get last id after insert a value in php

HotTag

Archive