问题:
如何检索 LAST_INSERT_ID() 值使用 PHP 和 PDO 来自 MySQL 存储过程?
过程:
考虑以下 PHP 代码:
<?php $stmt = $db->prepare("CALL simpleProcedure(:name,:returnid)"); $stmt->bindValue(':name', $name, PDO::PARAM_STR); $stmt->bindParam(':returnid', $returnid, PDO::PARAM_INT, 11); $stmt->execute(); echo $returnid; ?>
虽然此代码可能看起来正确,由于 PDO 中长期存在的错误,它不起作用。
解决方案:
要从 MySQL 过程检索输出,请按照以下步骤操作:
第 1 步:运行过程
使用您的输入运行过程并指定哪些 MySQL 变量将存储结果。例如:
<?php $stmt = $db->prepare("CALL simpleProcedure(:name, @returnid)"); $stmt->bindValue(':name', $name, PDO::PARAM_STR); $stmt->execute(); ?>
步骤 2:查询 MySQL 变量
运行单独的查询以选择 MySQL 变量:
<?php $stmt = $db->query("SELECT @returnid"); $result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); $returnid = $result[0]; ?>
示例:
以下代码包含更详细的示例,显示不同参数类型(IN、INOUT、OUT)使用变量:
SQL 过程:
CREATE PROCEDURE `demoSpInOutSqlVars`( IN pInput_Param INT, /* --- */ INOUT pInOut_Param INT, OUT pOut_Param INT) BEGIN /* * Pass the full names of SQL User Variable for these parameters. e.g. '@varInOutParam' * These 'SQL user variables names' are the variables that Mysql will use for: * 1) finding values * 2) storing results * * It is similar to 'variable variables' in PHP. */ SET pInOut_Param := ABS(pInput_Param) + ABS(pInOut_Param); SET pOut_Param := ABS(pInput_Param) * -3; END$$
PHP 代码:
<?php // Bind PHP variables $phpInParam = 5; $phpInOutParam = 404; $phpOutParam = null; // Prepare the SQL procedure call $sql = "call demoSpInOut(:phpInParam, @varInOutParam, @varOutParam)"; $stmt = $db->prepare($sql); $stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT); // Set the SQL User INOUT variables $db->exec("SET @varInOutParam = $phpInOutParam"); // Execute the procedure $stmt->execute(); // Get the SQL variables into PHP variables $sql = "SELECT @varInOutParam AS phpInOutParam, @varOutParam AS phpOutParam FROM dual"; $results = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC); $phpInOutParam = $results[0]['phpInOutParam']; $phpOutParam = $results[0]['phpOutParam']; // Display the PHP variables echo "phpInParam: $phpInParam<br>"; echo "phpInOutParam: $phpInOutParam<br>"; echo "phpOutParam: $phpOutParam<br>"; ?>
按照以下步骤,您可以使用 PDO 有效地检索 MySQL 存储过程的输出。
以上是如何在 PHP 中使用 PDO 从 MySQL 存储过程检索输出?的详细内容。更多信息请关注PHP中文网其他相关文章!