PHP PDO: Stored Procedure Return Empty Result

Question

I am trying to call SP(stored procedure) using PDO.

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 is executed successfully and inserted data into relevant tables and suppose to return the new inserted id. But when I print the result, I get an empty array.

Any suggestion?

Below is the SP I am using:

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 ;

Edit: NEVER MIND.

I thought by using $result variable i will be able to fetch the OUT variable's value. But later i found that i needed to use another SQL query to fetch those out variables.

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

score 1 · Answer 1 · answered Nov 28 '13 at 21:34

try this...

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();
}

score 0 · Answer 2 · answered Jan 08 '13 at 08:13

0

You can get the last inserted id by using this: PDO::lastInsertId();

I think you are currently return the array from fetchAll, which I don't think includes the id: http://php.net/manual/en/pdostatement.fetchall.php

answered Jan 08 '13 at 08:13

I am returning two ids (user_id and product_id) via SP. I don't think your solution will work in this case or will? In other words i want to fetch OUT variables using PDO.... how would i do that? – Neeraj Jan 08 '13 at 08:14
I don't see where your SP is actually returning those IDs – Jan 08 '13 at 08:16
Look at SET user_id := LAST_INSERT_ID(); and SET product_id := LAST_INSERT_ID(); and those variables are OUT variables. – Neeraj Jan 08 '13 at 08:17
Gotcha. When you call this query inside of a mysql client or terminal, do you get back the two IDs? – Jan 08 '13 at 08:18
1

Check this out to see if it helps: http://stackoverflow.com/questions/6422030/php-pdo-cannot-get-out-parameter-value – Jan 08 '13 at 08:19

score 0 · Answer 3 · answered Oct 27 '14 at 11:28

TRY THIS

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_user`$$
CREATE PROCEDURE `sp_user`(
    IN user_name VARCHAR(255),
    IN product_name VARCHAR(255)
)
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;

    SELECT user_id, product_id;

END$$
DELIMITER ;

in PHP

$result = $conn->query("CALL sp_user(demouser, demoproduct)")->fetchAll();
var_dump($result);

PHP PDO: Stored Procedure Return Empty Result

3 Answers3