Page 1 of 1

PHP handling stored procedure results

Posted: Tue May 11, 2010 5:05 pm
by dhui

Code: Select all

function getsomething($sID, $num)
	{
		$result = mysql_query('CALL GetSomething($sID)');
		$num_rows = mysql_num_rows($result); //HERE IS WHERE ERROR OCCURS
		
		$numbers = range(0, $num_rows-1);
		shuffle($numbers);
		
		for($x = 0; $x < $num_rows; $x++)
		{
			$row = mysql_fetch_array($result);
			$qIDArr[$x] = $row['qID'];
			echo $qIDArr[$x] . " ";
		}
        }

Code: Select all

getSomething(1, 10);
I keep getting a mysql_num_rows() expects parameter 1 to be resource, boolean given error. I am new to stored procedures so I am obviously not handling it correctly in PHP. Any help would be great!

Re: PHP handling stored procedure results

Posted: Tue May 11, 2010 5:58 pm
by minorDemocritus
PHP Manual wrote: For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
I don't know CALL syntax or stored procedures, but this looks like a good place to start. You're expecting $result to be a resource, but it's giving you TRUE or FALSE

Re: PHP handling stored procedure results

Posted: Tue May 11, 2010 6:11 pm
by John Cartwright
What does

Code: Select all

$result = mysql_query('CALL GetSomething('. (int)$sID .')') or die(mysql_error()); 
say? Also note that variable replacements are not performed in PHP when in single quotes.

Re: PHP handling stored procedure results

Posted: Tue May 11, 2010 6:57 pm
by mikosiko
Dhui,

Looks like John C. is correct... try to write your mysql_query in his way or

$result = mysql_query('"CALL GetSomething($sID)");

if that doesn't work post your SProcedure... maybe there is an error.

.. this link will help you with different examples too:
http://www.joeyrivera.com/2009/using-m ... ysqlipdo/

Re: PHP handling stored procedure results

Posted: Wed May 12, 2010 8:15 am
by dhui
Thanks for the replies, but now I have another issue.

Code: Select all

function getsomething($sID, $num)
	{
		$result =  mysql_query('CALL GetSomething('.  (int)$sID  .')'); 

		var_dump($result); // returns what I want which is a resource

		$num_rows = mysql_num_rows($result);
		
		$numbers = range(0, $num_rows-1);
		shuffle($numbers);
		
		for($x = 0; $x < $num_rows; $x++)
		{
			$row = mysql_fetch_array($result);
			$qIDArr[$x] = $row['qID'];
		}
		
		for($i = 0; $i < $num; $i++)
		{
			$selectedqIDs[$i] = $qIDArr[$numbers[$i]];
		}
		
		$sql = "SELECT * FROM question WHERE stestTypeID = " . $sID . " AND qID = " . $selectedqIDs[0];
		
		for($j = 1; $j < count($selectedqIDs); $j++)
		{
			$sql = $sql . " OR qID = " . $selectedqIDs[$j];
		}
		
		$sql = $sql . " ORDER BY RAND()";
		
		$result = mysql_query($sql);

		var_dump($result); //RETURNS A BOOL not what I want :/
		
		$num_rows = mysql_num_rows($result); //ERROR OCCURS HERE
		
		for($x = 0; $x < $num_rows; $x++)
		{
			$row = mysql_fetch_array($result);
			echo $row['qID'];
		}
		
		//return mysql_query($sql);
	}
	
	getsomething(1, 10);
Please look at the two "var_dump($result)". I ran the stored procedure I think correctly this time and it returns a resource on var_dump. But on the second var_dump it returns true/false which creates an error. I printed out the 2nd query to screen and copy and pasted to my DBs query analyzer and the SQL statement runs fine.

Any reason why the 2nd $result is getting turned into a bool?? If I change the procedure on the first $result to an in-line SQL statement the whole function works.

Re: PHP handling stored procedure results

Posted: Wed May 12, 2010 10:06 am
by AbraCadaver
I find it hard to believe that it's ever true. What does this show:

Code: Select all

$result = mysql_query($sql) or die(mysql_error());

Re: PHP handling stored procedure results

Posted: Wed May 12, 2010 10:37 am
by mikosiko
I have been dealing with the same issue for long time... and all my search point to some "funny" behavior of Stored Procedure's calls.

I've used 2 different solutions that overcome the problem:
1) After calling your SP close your connection and open a new one before to execute the second SQL.
2) Use mysqli instead of mysql and implement your functionality using mysqli_multi_query, mysqli_store_result, mysqli_use_result sentences... example

Code: Select all

<?php

$link = mysqli_connect("localhost", "username", "password", "database");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "CALL p_test();";   // change for your procedure
$query .= "SELECT * FROM authusers";  // change for your select

/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_use_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

/* close connection */
mysqli_close($link);
?>

Re: PHP handling stored procedure results

Posted: Wed May 12, 2010 10:58 am
by dhui
AbraCadaver wrote:I find it hard to believe that it's ever true. What does this show:

Code: Select all

$result = mysql_query($sql) or die(mysql_error());
Right it is never true, but the problem isn't whether its true or false? its whether its a bool or a resource.

Re: PHP handling stored procedure results

Posted: Wed May 12, 2010 11:17 am
by AbraCadaver
dhui wrote:
AbraCadaver wrote:I find it hard to believe that it's ever true. What does this show:

Code: Select all

$result = mysql_query($sql) or die(mysql_error());
Right it is never true, but the problem isn't whether its true or false? its whether its a bool or a resource.

If it's false then the query failed, so use the die() code to see what the problem was.

Re: PHP handling stored procedure results

Posted: Wed May 12, 2010 11:49 am
by mikosiko
AbraCadaver wrote:
If it's false then the query failed, so use the die() code to see what the problem was.
AbraCadaver... trust me... it is a waste of time... that is not the problem... read my previous post.

I've been trying to find an article that I found almost 2 years ago with a clear explanation to this issue but I really don't remember where I placed it.... If my memory doesn't fail the error/behavior is caused for the way that mysql manage the SP's calls and the type/amount of data that an SP is supposed to return... I really wish to find that article :)

EDIT: Finally I found something:
"Executing Stored Procedures

First thing’s first, you shouldn’t treat calls to stored procedures which return a resultset as you would normal SQL queries. The reason being is that when executed, a stored procedure will give you two resultsets back. One with the actual resultset and another which sends the status of the stored procedure (OK/ERR).

So when you do something like calling another query later on in your script , this will produce a MySQL error: “Lost connection to MySQL server during query”. Or if you didn’t free any results, it will produce the following error “Commands out of sync; you can’t run this command now.”

Why does this happen?

When you use the first result from the stored procedure (remember stored procs produce 2), you’ve still got another result waiting in line.

This is not allowed and the mysql database regards this as an incorrect order of commands (commands out of sync). The error makes sense. Why are you trying to create another resultset whilst there is still an unused result in the queue? Here’s the solution!
Buffer results/Use Result/Free buffered results

In order to resolve issues we are experiencing with stored procedures, we need to handle the second resultset being dished out by the stored procedure. To do this, we need to do the following

buffer the resultsets
use or assign gathered data from the first resultset
free the first resultset
then loop through the remaining resultset(s) and free them with each iteration.
And the technical reasons according to MySql Manual (read the 3 or 4th paragraph)
http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html

Re: PHP handling stored procedure results

Posted: Thu May 13, 2010 8:51 am
by dhui
Thanks for the explanation. At least now I understand what it's doing.

Still have trouble getting it to work however. Been trying to free the results and everything but it doesn't seem to want to cooperate. I'll probly switch over to mysqli and see if thats any better.

Re: PHP handling stored procedure results

Posted: Thu May 13, 2010 10:13 am
by mikosiko
OK... let me post a complete example and the results... maybe could be good for someone else

First... Here is a simple stored procedure:

Code: Select all

DELIMITER $$

DROP PROCEDURE IF EXISTS `p_test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`()
BEGIN
   SELECT "First-Select" FROM dual;
   SELECT "Second-Select", "Other-Result" FROM dual;
END $$

DELIMITER ;
Notice how this procedure has 2 selects returning 1 and 2 values respectively.

Here is the msqli Multi-Query implementation to get those values correctly

Code: Select all

<?php

   /*** Connection Parameters ***/ 
   $hostname = 'localhost'; 
   $username = 'you-username'; 
   $password = 'your-password'; 
   $dbname = 'your-database'; 

   /*** create a new mysqli object with defined database***/ 
   $link = @new mysqli($hostname, $username, $password, $dbname); 

   if (mysqli_connect_errno()) 
    {
      printf("Connection failed: %s\n", mysqli_connect_error());
      exit();
    }

// Here I define the Query... notice how its is calling the SP and in addition add other new SELECT sentence
// So basically I need to manage the results of 3 SQL sentences
$query  = "CALL p_test();";
$query .= "SELECT 123 FROM dual";

$i = 0;
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
   do {
                 /* This line shows the iteration... only to see the effect... can be deleted */
	        echo "<br />" . "DO While Iteration = " . $i++ . "<br />"; 
	
                /* store result set */
		if ($result = mysqli_store_result($link)) {
		   // This Shows the ResultSet... can be delete also
		   echo "<pre>";
		   print_r($result);
		   echo "</pre>";	
		   
		   // Process ResultSet
		   while ($row = mysqli_fetch_row($result)) {
		      for ( $counter = 0; $counter <= sizeof($row); $counter++) {
			   
                               printf("%s %s %s\n", "Inner while counter ---> ", $counter, $row[$counter]);
				echo "<br />";
		      }	
		   }
         }
	  
   } while (mysqli_next_result($link));
}

/* close connection */
mysqli_close($link);
?>
HERE are the results:
[text]Value i = 0
mysqli_result Object
(
[current_field] => 0
[field_count] => 1
[lengths] =>
[num_rows] => 1
[type] => 0
)
Inner while ---> First-Select
Inner while --->
Value i = 1
mysqli_result Object
(
[current_field] => 0
[field_count] => 2
[lengths] =>
[num_rows] => 1
[type] => 0
)
Inner while ---> Second-Select
Inner while ---> Other-Result
Inner while --->
Value i = 2
Value i = 3
mysqli_result Object
(
[current_field] => 0
[field_count] => 1
[lengths] =>
[num_rows] => 1
[type] => 0
)
Inner while ---> 123
Inner while --->
................FINISHED ...............[/text]

Analyzing the code and the results you sure will have a better understanding about how it works.

Hope this help
Miko