Member Avatar for zicomerc

Hi,

I've just discovered mysql stored procedures and have been using PHP4 a few years now, but not as my day job. I can get a mysql stored procedure to execute and work in the PHP script, but I'm having problems executing multiple stored procedures. Each of them returns one recordset.

My connection string looks like this: mysql_connect(hostname,dbname,password,TRUE, 131072); A sample of my code that produces the error looks like this:

include('connection.php');

$sql = "CALL PlayerListPosition (2, 3, 4);";

$result = mysql_query($sql);

while ($rowresult = mysql_fetch_row($result))
{
	echo "$rowresult[0] <BR />";
}

$sql = "CALL PlayerListPosition (2, 1, 1);";

$result = mysql_query($sql);

while ($rowresult = mysql_fetch_row($result))
{
	echo "$rowresult[0] <BR />";
}

Now the first recordset works just fine, but when its time to run the second recordset I get a mysql_fetch_row error as below. Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource If I executed the second procedure on its own that works too.

Do I need to close the recordset before using the next one? How would I do this? I've tried running multiple in line sql SELECT statements and I dont get a problem - its only when I use multiple stored procedures in a page - that this error occurs.

I really would like to start using stored procedures now that I've discovered them so any help in getting around this problem would be great! Thanks.

  • 3 Contributors
  • 3 Replies
  • 187 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

This thread’s symptom (the second CALL producing “not a valid MySQL result resource”) almost always means the previous CALL left results or status messages on the connection that weren’t consumed, so the next query fails. Stored procedures can produce multiple rowsets plus a final status, and the client must consume or free every result before issuing another CALL. (dev.mysql.com)

was correct to point at freeing the result resource; when using the old ext/mysql API the short‑term fix is to ensure the first result is fully fetched and then freed, and to check mysql_error() if mysql_query() returns false. The PHP mysql_free_result page documents freeing the result memory. If the procedure produced additional (hidden) result/status frames those must be cleared too — otherwise subsequent queries can fail. (php.net)

A more robust, long‑term solution is to move to mysqli or PDO, which provide explicit multi‑result handling. With mysqli use multi_query/store_result + a do/while loop with more_results()/next_result() to fetch/free every rowset; with PDO use PDOStatement::nextRowset() to advance through rowsets. These APIs let you call stored procedures reliably without “leftover” results breaking later queries. Example patterns (shortened):

// mysqli (procedural)
$mysqli = new mysqli(...);
if ($mysqli->multi_query("CALL PlayerListPosition(2,3,4)")) {
  do {
    if ($res = $mysqli->store_result()) {
      while ($row = $res->fetch_row()) { /* process */ }
      $res->free();
    }
  } while ($mysqli->more_results() && $mysqli->next_result());
}
// PDO
$pdo = new PDO(...);
$stmt = $pdo->query('CALL PlayerListPosition(2,3,4)');
do {
  $rows = $stmt->fetchAll(PDO::FETCH_NUM);
  /* process $rows */
} while ($stmt->nextRowset());

See mysqli::multi_query and PDOStatement::nextRowset for details and edge cases. (php.net)

Additional notes: if still on ext/mysql double‑check the mysql_connect parameter order and any client_flags you pass (the client must support multi‑result processing), but moving to mysqli/PDO is strongly recommended — ext/mysql is legacy and lacks the modern conveniences for stored procedures. (php.net)

Recommended Answers

All 3 Replies

Member Avatar for zicomerc

Can anyone help? Surely someone must know how to run 2 stored procedures on a single PHP page?

Member Avatar for RCrizt

same problem here :( .....

can anyone help us......

Member Avatar for pritaeas

You need to use mysql_free_result($result) before you can use it again, or use a different variable name the second time.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.