Get the InsertId with Prepared Statements AND Stor

Question

I had some Prepared Statements working in PHP using mysqli. The requirements changed and now I'm supposed to move them to the DB, as Stored Procedures. This worked fine for most of the PSs, except for a couple that read the insertId for some further processing. Ex:

$idAsdf = $stmtAsdf->insert_id;

where the PS performs an INSERT operation. I've tried using an OUT parameter on the procedure which works fine on PHPMyAdmin, but can't connect it with the PHP server code outside the DB. I haven't found any example of this combination of elements being done. How can I get this insertId using both SPs and PSs?

Thanks

Answer 1

For PDO Prepared Statement you can use PDO::lastInsertId -http://php.net/manual/en/pdo.lastinsertid.php

<?php 
try { 
$dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'password'); 

$stmt = $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)"); 

try { 
    $dbh->beginTransaction(); 
    $tmt->execute( array('user', '[e-mail]')); 
    print $dbh->lastInsertId();
    $dbh->commit(); 
} catch(PDOExecption $e) { 
    $dbh->rollback(); 
    print "Error!: " . $e->getMessage() . "</br>"; 
} 
} catch( PDOExecption $e ) { 
print "Error!: " . $e->getMessage() . "</br>"; 
} 
?>

Just remember when using transaction return lastInsertId or store lastInsertId before commit.

For Stored Procedure - use LAST_INSERT_ID();

BEGIN
  INSERT INTO test (name, email) VALUES ('value1', 'value2');
  SET out_param = LAST_INSERT_ID();
END

EDIT 1 :

If you using MySQLi - then use mysqli_insert_id - http://php.net/manual/en/mysqli.insert-id.php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = mysqli->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "[e-mail]";
$stmt->execute();

printf ("New Record has id %d.\n", $stmt->insert_id);

/* close connection */
$mysqli->close();

If facing problem with out_param, use select to return last insert id as result.

BEGIN
  DECLARE last_id INT DEFAULT 0;
  INSERT INTO test (name, email) VALUES ('value1', 'value2');
  SET last_id = LAST_INSERT_ID();
  SELECT last_id;
END

EDIT 2 :

If you are facing problem in retrieving Stored Procedure result set use following code -

if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
           $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

To access the out param use follwing code -

// execute the stored Procedure
// @uid - IN param, @userCount - OUT param
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

Answer 2

insert_id is a property of mysqli class, while you are trying to get it from a statement object.

Answer 3

inside SP you can set like this outParam = LAST_INSERT_ID(); LAST_INSERT_ID() returns the most recently generated ID is maintained in the server on a per-connection basis.