Multiple MySQL Queries on One PHP Page

Question

I have the following PHP/MySQL code:

function executeMultirowQuery($query) {
   $result = mysql_query($query);
   $table = array(); 

   if ($result == false) {
      $error = mysql_error();
      echo $error;
   } else {
      while ($row = mysql_fetch_assoc($result)) {
         $table[] = $row;
      }
      mysql_free_result($result);
   }

   return $table;
}

It basically executes the given query, and returns the result as a multi-dimensional array. The second call (and all subsequent calls) to this function result in a "Commands out of sync; you can't run this command now" error from MySQL.

Since I'm calling mysql_free_result() before I return I don't think its that I still have a request out there, but I can't think of any other reason it might be failing.

Any ideas?

EDIT: If it makes any difference, I'm using this function to call stored procedures like this:

function getGroups($groupTypeID) {
   echo "getGroups";
   $query = sprintf("CALL getGroups('%s');", mysql_real_escape_string($groupTypeID));

   return $this->executeMultirowQuery($query);
}

This is typical of the way the executeMultirowQuery() function is used.

EDIT: Here is my connection code:

$this->link = mysql_connect($host, $user, $password);
$success = mysql_select_db($database);

if (!$this->link) {
   die('Could not connect: ' . mysql_error());
}

Also, here is a sample query from the stored procedure:

CREATE DEFINER=`blah`@`blah` PROCEDURE `getGroups`(
    IN inGroupTypeID INT )
BEGIN
   SELECT * FROM `db`.`Groups`
   WHERE GroupTypeID = inGroupTypeID;
END

Again, this is typical of how the function is being used. None of the stored procedures returns multiple results sets (which I understand doesn't work in PHP/MySQL).

well...you do not call it before you return, read your code again. the line before the return is } and not the free result call.Any way, why do you try to invent the wheel, are you in learning phase? — Itay Moav -Malimovka
– Itay Moav -Malimovka, Commented Jul 30, 2011 at 15:47
The code looks alright to me; free_result is only called if there is a valid mysql result. I suspect the issue is with the sql queries that are beign run rather than the php code. Maybe... — Flambino
– Flambino, Commented Jul 30, 2011 at 15:52
@Itay I'm just learning my way around PHP/MySQL. I'm not using fancy tools like Zend Framework, etc., because they would be overkill for the kinds of things that I'm doing. — Graham
– Graham, Commented Jul 30, 2011 at 16:07
@Graham - once you have a good grasp of php+mysql it will get you much much faster to where you want to get using tools/FW. But, I think it is wise in the beginning to experiment with the raw code. — Itay Moav -Malimovka
– Itay Moav -Malimovka, Commented Jul 30, 2011 at 16:24

Jon Black · Accepted Answer · 2011-07-30 17:38:42Z

1

A stored procedure returns two resultsets - your data plus a count of the number of rows fetched so you need to call

next_result()

after each db call.

You'd also be better off using mysqli vs mysql (only the latest mysql extension supports sproc calls) then you can use the faster fetch_all method to populate your array.

$table = $result->fetch_all(MYSQLI_ASSOC);

Hope this helps

answered Jul 30, 2011 at 17:38

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

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

1 Comment

Graham Over a year ago

It worked great, except for the fetch_all() call, which hung. But thanks!

Abhay · Accepted Answer · 2011-07-30 18:32:25Z

0

I think you should do a mysql_free_result() even when $result == false. Thus, move mysql_free_result($result) outside the else{} and just before the return.

The following links might help:

http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html

http://mike-hostetler.com/blog/2007/04/php-5-and-mysql-5-stored-procedures-error-and-solution-qcodo

answered Jul 30, 2011 at 18:32

Abhay

6,6852 gold badges26 silver badges17 bronze badges

Collectives™ on Stack Overflow

Multiple MySQL Queries on One PHP Page

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related