How to use mysqli_insert_id with prepared statements?

Question

I have the following code and I'm getting crazy with calling an auto_increment id

$sql = "INSERT INTO tablename (x1, x2) VALUES(?,?)";
if($query = $db->prepare($sql)){
$query->bind_param('ss', $x1, $x2);
$query->execute();

$id = mysqli_insert_id($query);

For a reason I don't know why this is not working. I also tried

$id = mysqli_insert_id($sql);

And

$id = mysqli_insert_id();

I just decided to work with mysqli. Before that, I only used MySQL where I had no problem with

$id = mysql_insert_id();

yes. thats all fine. it is working well. the query will executed into db. i just need that id for mkdir(). — John, Mar 02 '12 at 09:27

score 3 · Answer 1 · answered Dec 10 '12 at 08:24

You must pass the mysqli link to mysqli_insert_id(), not the query:

$link   = mysqli_connect("localhost", "my_user", "my_password", "world");
$sql    = "INSERT INTO tablename (x1, x2) VALUES(?,?)"
$result = mysqli_query($link, $sql);

$id     = mysqli_insert_id($link);

or since you were using object oriented style:

// ...
$id     = $mysqli->insert_id;

score 0 · Answer 2 · answered Jan 23 '21 at 22:48

It's best to stick to OOP style. It will cause you less confusion. In OOP style you only need to access a property on the object. For example:

$stmt = $db->prepare($sql);
$stmt->bind_param('ss', $x1, $x2);
$stmt->execute();
// Either one will work
$stmt->insert_id;
$db->insert_id;

If you want to use the procedural mysqli style then you have to remember that there are two functions. One if for the mysqli object and the other is for mysqli_stmt object. You need to pick either one of them, but you have to pass the right object to it.

// For mysqli_stmt object
mysqli_stmt_insert_id($stmt);
// For mysqli object
mysqli_insert_id($db);

score 0 · Accepted Answer · answered Mar 02 '12 at 09:20

0

Probably something like

$query->commit(); OR $query->close();

answered Mar 02 '12 at 09:20

elrado

4,960
1
17
15

hello, no when i use commit it will show Fatal error: Call to undefined method mysqli_stmt::commit() – John Mar 02 '12 at 09:25
when close: Warning: mysqli_insert_id() expects parameter 1 to be mysqli, object given in – John Mar 02 '12 at 09:32

score -1 · Answer 4 · answered Mar 02 '12 at 09:27

-1

 $result = mysql_query($sql);

        if(!$result) {
        {
             die('Error: ' . mysql_error());

        } else {

        echo "<p>Done!.</p>";

        }

Try this and check the output...

answered Mar 02 '12 at 09:27

chinna_82

6,353
17
79
134

How to use mysqli_insert_id with prepared statements?

4 Answers4