PHP - MySQL gets value of out parameter from a stored procedure

Question

I have called a MySQL stored procedure from PHP using mysqli. This has one out parameter.

$rs = $mysqli->query("CALL addNewUser($name,$age,@id)");

Here, @id is the out parameter. Next, I fire the following query to get the value of the out parameter:

$rs2 = $mysqli->query("SELECT @id");
while($row = $rs->fetch_object()){
    echo var_dump($row);
}

The output of var_dump is as follows.

object(stdClass)#5 (1) { ["@id"]=> string(6) "100026" }

So, now I want to retrieve the value of @id, which I am unable to. I tried $row[0]->{@id} but this gave following error:

PHP Fatal error: Cannot use object of type stdClass as array

TerryE · Accepted Answer · 2012-07-28 14:51:23Z

7

Or even just do a "SELECT @id AS id" then $row->id will work fine. I always rename select columns to keep the name meaningful when necessary :-)

BTW, you can simply concatenate the call and select @... (with a ; statement delimiter) and the RS will be the returned value. Unfortunately this returns a mutli-resultset and you need to flush the full set otherwise the subsequent queries will stall. See following examples:

$db->multi_query( "CALL addNewUser($name,$age,@id);SELECT @id as id" );
$db->next_result();            // flush the null RS from the call
$rs=$db->store_result();       // get the RS containing the id
echo $rs->fetch_object()->id, "\n";
$rs->free();

Alternatively add the select into the addNewUser and return a RS instead of out param

$rs = $db->query( "CALL addNewUser($name,$age)" );
echo $rs->fetch_object()->id, "\n";
$rs->close();
$db->next_result();            // flush the null RS from the call

The first returns a multiquery (NULL, RS) set and the second a (RS, NULL) set, hence you can use a simple query() call which embeds the first fetch_object(), but you still need to flush the RS stack.

edited Jul 28, 2012 at 14:51

answered Jul 27, 2012 at 22:08

TerryE

10.9k5 gold badges28 silver badges48 bronze badges

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

3 Comments

mtk Over a year ago

Hey, Can you please elaborate on the 2nd suggestion you gave. Thanks :)

TerryE Over a year ago

@mtk, Sorry, I should check BTWs against my code :-( I used the second version.

mtk Over a year ago

Hey, thanks a lot for the explanation. All examples work well. Was looking for just one solution but got three :)

Madara's Ghost · Accepted Answer · 2012-07-27 18:16:09Z

4

Just $row->{"@id"} would work here. You can't use an stdClass as an array ($row[0]...).

edited Jul 27, 2012 at 18:16

answered Jul 27, 2012 at 18:07

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

2 Comments

cHao Over a year ago

You need to quote the column name, in order to avoid notices and such. @id becomes 'id' (unless you have a defined constant named id).

mtk Over a year ago

@cHao .@MadaraUchiha @ [email protected]

Palladium · Accepted Answer · 2012-07-27 18:10:01Z

3

Alternatively, you can just fetch the data as an array using mysqli::fetch_assoc() and access the data using $row['@id'].

answered Jul 27, 2012 at 18:10

Palladium

3,7734 gold badges17 silver badges19 bronze badges

Comments

Prabakar Sebastin · Accepted Answer · 2015-11-26 19:27:21Z

3

Another correct methods its working fine: Cheers!!

$procedureName = 'VALIDATE_USER';
$procedure = "CALL $procedureName('$username','$pwd',@p_userid)";
$results1 = $dbconnection->query($procedure);
$results2 = $dbconnection->query("SELECT @p_userid");
$num_rows = $results2->num_rows;
if ($num_rows > 0) {

    while($row = $results2->fetch_object())
    {
    echo $row->{"@p_userid"};

    }
}

edited Nov 26, 2015 at 19:27

answered Nov 26, 2015 at 19:19

Prabakar Sebastin

312 bronze badges

Comments

SATYAJEET RANJAN · Accepted Answer · 2017-11-03 19:46:48Z

0

Here is the working solution:

enter code $res = $conn->multi_query( "CALL PROCNAME(@x);SELECT @x" );
if( $res ) {
  $results = 0;
  do {
    if ($result = $conn->store_result()) {
      printf( "<b>Result #%u</b>:<br/>", ++$results );
      while( $row = $result->fetch_row() ) {
        foreach( $row as $cell ) echo $cell, "&nbsp;";
      }
      $result->close();
      if( $conn->more_results() ) echo "<br/>";
    }
  } while( $conn->next_result() );
}
$conn->close();

answered Nov 3, 2017 at 19:46

SATYAJEET RANJAN

1413 silver badges7 bronze badges

Collectives™ on Stack Overflow

PHP - MySQL gets value of out parameter from a stored procedure

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related