我正在尝试使用 PDO 调用 SP(存储过程)。

try {
    // Connecting using the PDO object.
    $conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);        

    $stmt = $conn->prepare('CALL sp_user(?,?,@user_id,@product_id)');    
    $stmt->execute(array("demouser", "demoproduct"));
    $result = $stmt->fetchAll();
    print_r($result);
}
// Catching it if something went wrong.
catch(PDOException $e) {
  echo "Error : ".$e->getMessage();
}

SP执行成功，往相关表中插入数据，假设返回新插入的id。但是当我打印结果时，我得到一个空数组。

有什么建议吗？

下面是我使用的SP:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_user`$$
CREATE PROCEDURE `sp_user`(
    IN user_name VARCHAR(255),
    IN product_name VARCHAR(255),
    OUT user_id INT(11),
    OUT product_id INT(11)
)
BEGIN       
    START TRANSACTION;      
        INSERT INTO `user` (`name`) VALUES(user_name);
        SET user_id := LAST_INSERT_ID();        
        INSERT INTO `product` (`name`) VALUES(product_name);        
        SET product_id := LAST_INSERT_ID();         
        INSERT INTO `map_user_product` (`user_id`,`product_id`) VALUES(user_id,product_id); 
    commit;
END$$
DELIMITER ;

编辑:没关系。

我想通过使用 $result 变量我将能够获取 OUT 变量的值。但后来我发现我需要使用另一个 SQL 查询来获取这些变量。

$stmt = $conn->query("SELECT @user_id,@product_id");

最佳答案

试试这个...

try 
{
    // Connecting using the PDO object.
    $conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);        

    $stmt = $conn->prepare('CALL sp_user(:demouser, :demoproduct, @user_id, @product_id)'); 

    $demouser = "demouser";
    $demoproduct = "demoproduct";

    $stmt->bindParam(":demouser", $demouser, PDO::PARAM_STR, 255);
    $stmt->bindParam(":demoproduct", $demoproduct, PDO::PARAM_STR, 255);
    $stmt->execute();

    $result = $conn->query("select @user_id, @product_id;")->fetchAll();

    print_r($result);

    foreach($result as $row)
    {
        echo $row["@user_id"];
        echo $row["@product_id"];
    }

}
// Catching it if something went wrong.
catch(PDOException $e) 
{
  echo "Error : ".$e->getMessage();
}

关于PHP PDO : Stored Procedure Return Empty Result，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14210654/