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");

Carlos H · Accepted Answer · 2013-11-28 21:34:53Z

1

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

answered Nov 28, 2013 at 21:34

Carlos H

5846 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user985189 · Accepted Answer · 2013-01-08 08:13:36Z

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 8, 2013 at 8:13

user985189

5 Comments

Neeraj Over a year ago

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?

user985189 Over a year ago

I don't see where your SP is actually returning those IDs

Neeraj Over a year ago

Look at SET user_id := LAST_INSERT_ID(); and SET product_id := LAST_INSERT_ID(); and those variables are OUT variables.

user985189 Over a year ago

Gotcha. When you call this query inside of a mysql client or terminal, do you get back the two IDs?

user985189 Over a year ago

Check this out to see if it helps: stackoverflow.com/questions/6422030/…

Adonias Vasquez · Accepted Answer · 2014-10-27 11:28:04Z

0

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

answered Oct 27, 2014 at 11:28

Adonias Vasquez

1,01414 silver badges12 bronze badges

Collectives™ on Stack Overflow

PHP PDO: Stored Procedure Return Empty Result

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related