How do I get the id of the row I just inserted using a single prepared statement?

Question

I'm inserting a row in the following way:

require("localhost_credentials.php");

$conn = new mysqli($db_servername, $db_username, $db_password, $db_name);
if($conn->connect_error)
{
    die("Connection failed: " . $conn->connect_error);
}

$q_title = $fixed_title;
$q_tags = $_POST['tag_input'];
$q_mod = "n";
$q_t_create = date("m/d/Y @ G:i:s");
$q_t_modified = date("m/d/Y @ G:i:s");

$querystr  = "INSERT INTO mytable (title, tags, moderator, time_created, time_last_modified) ";
$querystr .= "VALUES (?, ?, ?, ?, ?);";

$statement = $conn->prepare($querystr);
$statement->bind_param("sssss", $q_title, $q_tags, $q_mod, $q_t_create, $q_t_modified);
$statement->execute();

I would like to get the id of the row I just inserted without having to do a second query. I've seen a few methods to do this on SO, but every time there's a debate as to which way it should and should not be done and I'm kind of confused.

Using prepared statements, how do I get the id of a newly inserted row using only one query?

Use PHP's built-in function http://php.net/manual/en/mysqli.insert-id.php — Jay Blanchard, Aug 03 '15 at 16:56

score 1 · Accepted Answer · answered Aug 03 '15 at 17:03

1

As long as you do not execute multi insert, you can use

$conn->insert_id

It is populated automatically when a statement created from that connection executes INSERT query.

answered Aug 03 '15 at 17:03

Przemysław Jan Wróbel

284
2
8

Actually now it's always returning zero? – xcdemon05 Aug 03 '15 at 17:36
Figured it out. It was failing because I was sending the same data over and over, and the title column in the table is set to unique so I couldn't add a row with the same title. – xcdemon05 Aug 03 '15 at 17:56
Great you figured it out! :-) Keep in mind that Statement object can also give you *insert_id* the same way the connection can. – Przemysław Jan Wróbel Aug 03 '15 at 17:58

score 0 · Answer 2 · answered Aug 03 '15 at 17:00

0

you can use something like this :

$last_id = $statement->insert_id($conn);

this will return the last inserted row id .

answered Aug 03 '15 at 17:00

SayJeyHi

1,559
4
19
39

How do I get the id of the row I just inserted using a single prepared statement?

2 Answers2