PHP mysqli 中的存储过程和准备语句 [英] Stored Procedures and Prepared Statements ins PHP mysqli
问题描述
你能帮我解决这个问题吗:我正在编写 PHP 代码并在 MySQL 中创建了一个存储过程,该过程接收两个输入值并返回一个输出参数
Could you please help me with this: I'm coding PHP and have created a stored procedure in MySQL that receives two input values and return one output parameter
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `insert_sp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_sp`(
IN ID VARCHAR(8),
IN Nombre VARCHAR(50),
OUT msg VARCHAR(50)
)
BEGIN
INSERT INTO empleado_php VALUES (ID, Nombre);
SELECT "Todo bien" INTO msg;
END$$
DELIMITER ;
并通过mysqli调用它,如果我使用下面剪下的代码,一切都很好
and call this via mysqli, everythings works just fine if I use the code snipped below
if (!$mysqli->query("SET @msg = ''") ||
!$mysqli->query("CALL insert_sp('" . $id . "', '" . $nombre . "', @msg)"))
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
if (!($res = $mysqli->query("SELECT @msg as _p_out")))
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
并用这个显示输出值:
$row = $res->fetch_assoc();
echo $row['_p_out'];
我的问题是:如果我必须使用准备好的语句,我应该如何编码?你能给我举个例子吗...这是我目前得到的但不起作用
My question is: How am I suppose to code if I have to use prepared statements? could you please give me an example...this is what I' got so far but doesn't work
if (!$mysqli->prepare("SET @msg = ''") ||
!$mysqli->prepare("CALL insert_sp(?,?,@msg)"))
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
if (!($res = $mysqli->prepare("SELECT @msg as _p_out")))
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
if(!$mysqli->bind_param("ss", $id, $nombre)){
echo "Error en bind-param ingresar_sp.php " .$mysqli->connect_error;
}
$mysqli->execute();
$row = $res->fetch_assoc();
echo $row['_p_out'];
顺便说一下……我需要通过 echo 显示输出值.
By the way...I need to show the output value via echo.
提前致谢.
推荐答案
很简单,PHP 官网说你必须使用会话变量(MySQL 会话,而不是 PHP),所以,我的问题的答案如下图所示:
It was very easy, PHP oficial website remarks that you've got to use session variables (MySQL sessions, not PHP), so, the answers to my question is show below:
// bind the value of the first IN parameter to the session variable @id
$stmt = $mysqli->prepare("SET @id = ?");
$stmt->bind_param('s', $id);
$stmt->execute();
// bind the value of the second IN parameter to the session variable @nombre
$stmt = $mysqli->prepare("SET @nombre = ?");
$stmt->bind_param('s', $nombre);
$stmt->execute();
//bind the value of the OUT parameter
$stmt = $mysqli->prepare("SET @msg = ''");
$stmt->execute();
// execute the stored Procedure
$result = $mysqli->query('call insert_sp(@id, @nombre, @msg)');
// getting the value of the OUT parameter
$r = $mysqli->query('SELECT @msg as _p_out');
$row = $r->fetch_assoc();
echo $row['_p_out'];
这篇关于PHP mysqli 中的存储过程和准备语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!