Question about multiple selects in a MySQL procedure and mysqli

[tgallagher]

Hello all,

I have a mysql procedure that I call with mysqli.  I am working with left and right keys with a tree and I use a procedure to add nodes to the tree.  Here is the procedure:

BEGIN
  SELECT @myLeft := L , @myID := CBSid  FROM dnt_cbs_table WHERE CBSid=parentCBSid;
  UPDATE dnt_cbs_table SET R = R + 2 WHERE R > @myLeft;
  UPDATE dnt_cbs_table SET L = L + 2 WHERE L > @myLeft;
  INSERT INTO dnt_cbs_table(name, L, R, PARid,cbstreeroot,CRid,State)
  VALUES (newNode, @myLeft + 1, @myLeft + 2,@myID,rootCBSid,owner,state);
select last_insert_id();
END

when I run this procedure from mysql command prompt, I get this return:

mysql> call AddNodeSP2(541,541,"a2",234,"Active");
+--------------+----------------+
| @myLeft := L | @myID := CBSid |
+--------------+----------------+
|            1 |            541 |           
+--------------+----------------+
1 row in set (0.00 sec)

+------------------+
| last_insert_id() |
+------------------+
|              585 |
+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 3 warnings (0.00 sec)

[/code]

 

So 2 things are being returned, the problem is when I run this method from PHP:

public function NewNode($uid, $sessionid, $aryObj)
{
	#$mysqli = new MySQLI('localhost','admin','123456','danati');
	$mysqli = $this->DBConnect();
	$sql = "CALL AddNodeSP2('$aryObj->parid','$aryObj->root','$aryObj->name','$uid','$aryObj->state')";
	#$sql = "CALL AddNodeSP2('559','541','training','234','happy')";
	$result = $mysqli->query($sql);
	$data = $result->fetch_assoc();
	#$result->free();
	var_dump($data);
}	
}

My dump shows this

array
  '@myLeft := L' => string '1' (length=1)
  '@myID := CBSid' => string '541' (length=3)

MYSQLI is only getting a return from the first select.  Is there a way to get the row return / the 2nd select statement???

 

Thanks for the help,

timgerr