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 ;
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;
and show the output value with this:
$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'];
By the way...I need to show the output value via echo.
Thanks in advance.