How to get lastInsertID when executing a Stored Procedure on a mySQL Server in PHP?

Question

Method 1 works fine for a standard prepared Query (I believe that's what its called) but I can't get either method 2 or 3 to return a LastInsertID - similar but different and not sure of advantages of either.

My main thing at this stage is getting the LastInsertID when using a Stored Procedure.

MySQL Table

CREATE TABLE `forms` (
  `FORM_ID` bigint(20) NOT NULL,
  `OUID` bigint(20) NOT NULL,
  `OID` bigint(20) NOT NULL,
  `UPDATED_DATE` timestamp NOT NULL DEFAULT current_timestamp(),
  `FORM_JSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`FORM_JSON`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL Stored Procedure- for Methods 2 & 3

DROP PROCEDURE `FORM_SAVEAS`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `FORM_SAVEAS`
(IN `V_OUID` INT, IN `V_OID` INT, IN `V_FORM_JSON` JSON) 
NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER INSERT INTO `forms` 
(OUID ,OID ,FORM_JSON ) 
VALUEs (V_OUID ,V_OID ,V_FORM_JSON)

Method 1) Prepared statement(This Works - returns LastInsertID ok) - but I'd like to use a Stored Proc

<?php  

$V_OUID      = 1;
$V_OID       = 2;
$V_FORM_JSON = '{"var1":"data 1","var2":"Data two","var3":"DATA III"}';

include('database_connection.php');

$query = "INSERT INTO forms (OUID       , OID    , FORM_JSON) 
                     VALUES ($V_OUID    , $V_OID , '" . $V_FORM_JSON . "')";

echo "The Query : $query";
try{
  $DB_CON -> exec($query);
  $form_id = $DB_CON->lastInsertId();
  echo "New Record created Successfully ID is: " . $form_id;
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
  echo $query;
}

Method 2) This Inserts the data but doesn't return the lastInsertId

<?php  

$V_OUID      = 1;
$V_OID       = 2;
$V_FORM_JSON = '{"var1":"data 1","var2":"Data two","var3":"DATA III"}';

include('database_connection.php');

$query = "CALL FORM_SAVEAS($V_OUID    , $V_OID , '" . $V_FORM_JSON . "')";

echo "The Query : $query";
try{
  $DB_CON -> exec($query);
  $form_id = $DB_CON->lastInsertId();
  echo "New Record created Successfully ID is: " . $form_id;
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
  echo $query;
}


?>

Method 3) This also Inserts the data but doesn't return the lastInsertId

<?php  

$V_OUID      = 1;
$V_OID       = 2;
$V_FORM_JSON = '{"var1":"data 1","var2":"Data two","var3":"DATA III"}';

include('database_connection.php');

$statement = $DB_CON->prepare('CALL FORM_SAVEAS(:V_OUID,:V_OID,:V_FORM_JSON)');
$statement->bindParam(':V_OUID', $V_OUID, PDO::PARAM_STR);
$statement->bindParam(':V_OID', $V_OID, PDO::PARAM_STR);
$statement->bindParam(':V_FORM_JSON', $V_FORM_JSON, PDO::PARAM_STR);
$statement->execute();
$form_id = $DB_CON->lastInsertID();

echo "New Record created Successfully ID is: " . $form_id;

?>

Of methods 2 & 3 which is best or is there a better way?

Methods #1 and #2 are extremely problematic because of SQL injection. The third one looks safe and should work. — tadman, Jul 07 '20 at 17:40
@tadman - thanks for taking a look but it, #3, always returns Zero (0).. I read something about need to get ID before commit but I don't know how - any thoughts? — Jason Duncan-King, Jul 07 '20 at 19:59
Are you sure there's no errors or issues? What does `execute()` return? — tadman, Jul 08 '20 at 00:49
That's a negative result, so it's likely the query failed for some reason. — tadman, Jul 08 '20 at 15:09

score 2 · Answer 1 · answered Jul 08 '20 at 11:10

It's a Solution but not a perfect answer

I'd liked to have used PDO lastInsertID()

Instead I've given the Stored Procedure an OUT Parameter which sends back the LAST_INSERT_ID() from mySQL... The Stored Procedure now looks like this.

CREATE DEFINER=`root`@`localhost` PROCEDURE `FORM_SAVEAS`
  (IN `V_OUID` INT, IN `V_OID` INT, IN `V_FORM_JSON` JSON, OUT `V_FORM_ID` INT(11)) 
  NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
  BEGIN 
    INSERT INTO `forms` (OUID ,OID ,FORM_JSON ) 
    VALUEs (V_OUID ,V_OID ,V_FORM_JSON);

    set V_FORM_ID := last_insert_id(); 
  END

The PHP became...

$statement = $DB_CON->prepare('CALL FORM_SAVEAS(:V_OUID,:V_OID,:V_FORM_JSON,@V_FORM_ID);');
$statement->bindParam(':V_OUID', $V_OUID, PDO::PARAM_STR);
$statement->bindParam(':V_OID', $V_OID, PDO::PARAM_STR);
$statement->bindParam(':V_FORM_JSON', $V_FORM_JSON, PDO::PARAM_STR);
$statement->execute();
$statement->closeCursor();

// execute the second query to get result from the OUT parameter
$row = $DB_CON->query("SELECT @V_FORM_ID AS V_FORM_ID")->fetch(PDO::FETCH_ASSOC);
$V_FORM_ID = $row['V_FORM_ID'];

echo "New Record created Successfully ID is: " . $V_FORM_ID;

NOTE all the additions to this code of V_FORM_ID that would have been unnecessary if PDO lastInsertID had worked. I'd still be happy to know if it's a PDO bug or I'm doing something wrong.

score 0 · Answer 2 · answered Feb 12 '22 at 14:54

Lots of confusing information on this subject throughout the Web. To put it simply, in your stored procedure, after you have completed your insert query (into what we'll cal "YourTable"), end it with a semi-colon, then do a select on that table, with the following:

SELECT LAST_INSERT_ID() AS WhateverVariableYouChose FROM YourTable;

That ID is then returned, just as any select. So, in your PHP, you simply do

$R = $stmt->fetch(); $LastInsert = $R[WhateverVariableYouChose];

How to get lastInsertID when executing a Stored Procedure on a mySQL Server in PHP?

2 Answers2

It's a Solution but not a perfect answer

Linked