CODEWITHSUNDEEP
phpmysqlstored-procedurespdo
Neeraj
Neeraj

Reputation: 9155

PHP PDO: Stored Procedure Return Empty Result

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

Upvotes: 3

Views: 3966

Answers (3)

Adonias Vasquez
Adonias Vasquez

Reputation: 1024

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

Upvotes: 0

Carlos H
Carlos H

Reputation: 584

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

Upvotes: 1

user985189
user985189

Reputation:

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

Upvotes: 0

Related Questions