PHP -> PDO -> Prepare -> Call Procedure -> Insert

Question

using this procedure

CREATE PROCEDURE `Insert_New_Return_Id`(IN Insert_Stmnt varchar(1000), OUT IDNum int)
BEGIN
    SET @buffer = Insert_Stmnt;
    PREPARE stmt FROM @buffer;
    EXECUTE stmt;
    SELECT LAST_INSERT_ID() INTO IDNum;
    DEALLOCATE PREPARE stmt;
END 

the following code works fine :

$statement=$con->prepare("CALL Insert_New_Return_Id (\"INSERT INTO users (first_name,last_name)VALUES('test','test')\",@ID)");
$statement->execute();
$statement=$con->query("SELECT @ID");
while ($row = $statement->fetch()){echo "Last ID Insert : " . $row['@ID'];}

but when i'm trying to bind parameters the values are ?

$first_name = "test";
$last_name = "test";    
$statement=$con->prepare("CALL Insert_New_Return_Id (\"INSERT INTO users (first_name,last_name)VALUES('?','?')\",@ID)");
    $statement->bindParam(1, $first_name, PDO::PARAM_STR);
    $statement->bindParam(2, $last_name, PDO::PARAM_STR);
    $statement->execute();
    $statement=$con->query("SELECT @ID");
    while ($row = $statement->fetch()){echo "Last ID Insert : " . $row['@ID'];}

If i try VALUES(?,?) returns an error.

How can i make this work? Call a procedure with prepare statement and binding parameters?

Thank you

Answer 1

$statement->bindParam(1, 'test', PDO::PARAM_STR);
$statement->bindParam(2, 'test', PDO::PARAM_STR);

You must use a variable instead of the string 'test'. PDOStatement::bindParam binds variables by reference. By definition, you cannot do this with a string. Use a variable instead.

$statement->bindParam(1, $str1, PDO::PARAM_STR);
$statement->bindParam(2, $str2, PDO::PARAM_STR);

Also, when you want to use CALL to call a stored procedure, just call the stored procedure by name. Do not repeat the query. Of course, this assumes you've done the work of adding the stored procedure to MySQL.

$statement=$con->prepare('CALL Insert_New_Return_Id(?,?)');

If you need a third parameter, add it to the stored procedure in MySQL and call it like this.

$statement=$con->prepare('CALL Insert_New_Return_Id(?,?,?)');