How to Retrieve LAST_INSERT_ID() from MySQL Stored Procedure Using PHP PDO?

Retrieve LAST_INSERT_ID() from MySQL Stored Procedure using PHP PDO

Retrieving the LAST_INSERT_ID() value from a MySQL stored procedure in PHP PDO can be a confusing task. Despite extensive documentation, it remains a common source of challenges for developers. Let's dive into a solution to this issue.

The primary issue lies in the fact that MySQL uses variables for IN, INOUT, and OUT parameters within stored procedures. To obtain the LAST_INSERT_ID(), a two-step process is required:

Step 1: Execute the Stored Procedure with Variables

$stmt = $db->prepare("CALL simpleProcedure(:name, :returnid)");
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':returnid', $returnid, PDO::PARAM_INT, 11);
$stmt->execute();

This step executes the stored procedure, passing input values as well as declaring variables to receive the LAST_INSERT_ID() value. However, we cannot directly access the value from the PHP variables.

Step 2: Retrieve the Variables' Values

To obtain the LAST_INSERT_ID(), we need to execute a separate query to retrieve the values assigned to the SQL variables. This involves setting the SQL User variables and executing a query to select their values.

$sql = "SELECT @returnid AS last_insert_id";
$result = $db->query($sql)->fetch();
$last_insert_id = $result['last_insert_id'];

By following these steps, you can successfully retrieve the LAST_INSERT_ID() value from a MySQL stored procedure using PHP PDO. It is important to note that binding PHP variables to INOUT or OUT parameters can lead to errors. Instead, use the SQL User variables to handle variable parameters.

The above is the detailed content of How to Retrieve LAST_INSERT_ID() from MySQL Stored Procedure Using PHP PDO?. For more information, please follow other related articles on the PHP Chinese website!