Problem in retrieving the out parameter of a mysql stored procedure through php script

Hi I have a simple MYSQL stored procedure which has an IN (int) and an OUT (int) parameters. I am calling the stored procedure from a PHP script.

Stored Procedure code:

  DELIMITER $$
  USE `testing`$$

  DROP PROCEDURE IF EXISTS `test_sp_inout_params`$$

  CREATE DEFINER=`root`@`localhost` PROCEDURE `test_sp_inout_params`(IN username VARCHAR(30), OUT user_id INT)
  BEGIN
   INSERT INTO users (`name`) VALUES (username);
   SET user_id = LAST_INSERT_ID();
  END$$

 DELIMITER ;

I am able to run the stored procedure from mysql query window with:

 CALL test_sp_inout_params('TestName', @user_id);
 SELECT @user_id

But from PHP script I am geting errors.. :(

    mysqli_query($conn, "CALL test_sp_inout_params('surya', @user_id)");
    $rs = mysqli_query($conn, "SELECT @user_id");
    开发者_如何学Go$row = mysqli_fetch_assoc($rs);
    var_dump($row);

[EDITED] The above PHP script executed perfectly, when it is running individually. But I have another stored procedure.. and when I am calling that before the above php-script, I am geting that sort of warning message. So, the script I am using is (before the above mentioned php script):

    $rs1 = mysqli_query($conn, "CALL test_sp_no_params()");
    $arr = array();
    while($row1 = mysqli_fetch_assoc($rs1))
        $arr[] = $row1;

What is the problem when I am calling the second stored procedure?

Can anyone help me out please!

Thanks in advance, SuryaPavan

---

I have recently been having troubles with MySQLI as well. I am using MySQLI::multi_query but I would assume that this may be a solution for you as well. After you call the query, try using this function and let me know if it brings back what you're expecting. Seeing as you're only doing a single query instead of a multi_query I'm sure you could trim some of the fat off of this function as well.

public function getResultSets() {
    $retVal = array();
    do {
        if ($result = $this->mysqli->store_result())
            $retVal[] = $result;
    } while ($this->mysqli->next_result());
    return $retVal;
}

I will say, however, that a stored procedure call will not return anything. If you would like it to then you have to set out parameters and pass them along. For instance, if I have a stored procedure with 2 ins and 2 outs, I would pass this into the MySQLI::multi_query (the multi_query part of it means you can specify multiple queries to pass along the same way you would in mysql workbench).

$thisGuy = new MySQLI(inputParamsHere);
$retVal = $thisGuy->multi_query("CALL myDB.storedProc($var1, $var2, @return1, @return2); select @return1 as firstValue, @return2 as secondValue;");
if ($retVal) getResultSets();

getResultSets() will then return an array that contains the results of each query within it (so long as there is something in the result set, the initial stored procedure call returns nothing and therefore there is an empty result set within the mysqli object that we dispose of it in the function).