Executing mysqli insert query then immediately selecting ID of new row

Question

I've been spending a couple of hours trying to write mysqli queries to insert a new row in a database (with a primary key ID) and then select the ID of the new row. My code as it currently is:

<?php
    include('connectionData.php');

    $conn = mysqli_connect($server, $user, $pass, $dbname, $port)
    or die('Connection error');

    if(isset($_POST['submit'])) {
        $pnum = $_POST['pnum'];

        $phone_insert_text = "INSERT INTO `voterdatabase`.`phone` (`pnum`) VALUES (?)";
        $phone_insert_query = $conn->prepare($phone_insert_text);
        $phone_insert_query->bind_param('s', $pnum);
        $phone_insert_query->execute();

        $phone_select_text = "SELECT phone_id FROM voterdatabase.phone WHERE pnum=?";
        $phone_select_query = $conn->prepare($phone_select_text);
        $phone_select_query->bind_param('s', $pnum);
        $phone_select_query->execute();
        $phone_select_query->bind_result($phone_id);

        echo $phone_id;
?>

$phone_insert_query executes without issue. But $phone_select_query doesn't appear to run at all, as echo $phone_id; has no effect. What might be going on here? I'm able to run the query directly in MySQLWorkbench.

Note that I previously tried doing this in one query using SELECT LAST_INSERT_ID();, but mysqli fails to execute any query containing that.

Well, first of all check if the value you're inserting actually appears in the database and if it assigns ID properly. — Timekiller, Dec 13 '15 at 19:16
Note: Remember the final closing bracket\parenthesis - It is missing. Unexpected effects are fun, but time consuming. — Peter_James, Dec 13 '15 at 19:30

score 2 · Accepted Answer · answered Dec 13 '15 at 19:16

2

Please try this

 $lastInsertID= mysqli_insert_id($conn);

answered Dec 13 '15 at 19:16

Vishal Kamal

1,104
2
10
35

That worked, what an easy fix! Thank you! (If I don't accept your answer in the next 10 mins, remind me) – ck2 Dec 13 '15 at 19:17
I feel a "race condition" coming on... How can you be certain the last insert_id the correct one? What if someone jumps in front of the final execution. – Peter_James Dec 13 '15 at 19:18
I definitely will, it's just that StackExchange doesn't let you accept an answer within the first 10 minutes. – ck2 Dec 13 '15 at 19:19
1

@Peter_James [this question](http://stackoverflow.com/questions/8796365/race-conditions-with-mysql-last-id) could be of interest relating to race conditions – Benjy1996 Dec 13 '15 at 19:23
@Benjy1996 I will eat my "Race Condition" in the same linear fashion as PHP. One thread at a time... – Peter_James Dec 13 '15 at 19:25
@Peter_James Hehe, when I first saw this function I thought the same too anyway. Great minds think alike an' all that. – Benjy1996 Dec 13 '15 at 19:26
Would have been nice to use `$phone_insert_query->insert_id` as the questioner was using the OO style in all their code. – RiggsFolly Dec 13 '15 at 19:31

score 0 · Answer 2 · answered Dec 13 '15 at 19:20

Use insert_id property:

<?php
    include('connectionData.php');

    $conn = mysqli_connect($server, $user, $pass, $dbname, $port)
    or die('Connection error');

    if(isset($_POST['submit'])) {
        $pnum = $_POST['pnum'];

        $phone_insert_text = "INSERT INTO `voterdatabase`.`phone` (`pnum`) VALUES (?)";
        $phone_insert_query = $conn->prepare($phone_insert_text);
        $phone_insert_query->bind_param('s', $pnum);
        $phone_insert_query->execute();
        $phone_id = $conn->insert_id;

        echo $phone_id;
?>

Well you certainly cut that in half. – Peter_James Dec 13 '15 at 19:21 — Peter_James, Dec 13 '15 at 19:21

score 0 · Answer 3 · answered Dec 13 '15 at 19:30

If you wish to be able to use the available functions to get the last inserted id, like mysqli_insert_id(), your table must have an AUTO_INCREMENT column. If not you will not get the id.

Also, even if you have the required columns, this will require two calls. To get around this, what you could do is something like create a stored procedure to do your insert for you and return the inserted id from the procedure.

Executing mysqli insert query then immediately selecting ID of new row

3 Answers3