CODEWITHSUNDEEP
phpmysqlsqlmysqli
ck2
ck2

Reputation: 41

Executing mysqli insert query then immediately selecting ID of new row

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.

Upvotes: 3

Views: 193

Answers (3)

inquam
inquam

Reputation: 12942

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.

Upvotes: 0

Covik
Covik

Reputation: 862

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

Upvotes: 0

Vishal Kamal
Vishal Kamal

Reputation: 1124

Please try this

 $lastInsertID= mysqli_insert_id($conn);

Upvotes: 2

Related Questions