blog.tomfite.com

MySQL 5 has support for stored procedures, which makes me happy. I’d much prefer keeping the database and SQL wrapped up nicely than to have a bunch of SQL queries vomited all over my PHP code. In order to use these handy stored procedures, you have to use PHP’s MySQLi (improved) extension instead of the normal php_mysql.

Well, everything was going great with this PHP stored procedure-y goodness until I tried to write a page that made use of two stored procedures. I used to do this all of the time in PHP using inline SQL statements — I would just assign a different $results variable for each statement. But when I tried to do this with mysqli and stored procedures, something went horribly wrong.

Here’s what my code looked like:

$query1 = "CALL sp_SampleStoredProc($input_param)";
$results1 = mysqli_query($link, $query1);
$row = $results1->fetch_assoc();

$query2 = "CALL sp_AnotherSampleStoredProc()";
$results2 = mysqli_query($link, $query2);
$num_rows = mysqli_num_rows($results2);


Yes, yes… I am mixing procedural and object oriented PHP. Phphpthphtpt.

Anyway, when I tried to run this, I wouldn’t get the results of the second query. I stuck in a

mysqli_error($link)

to see what was going on. Here’s what it said:

Commands out of sync; you can’t run this command now

Oh, gee. Thanks MySQL. Really helpful. I started digging around in the internets for this error message, and everything I read pointed to this example in the PHP documentation:


/* Select queries return a resultset */
if ($result = mysqli_query($link, "SELECT Name FROM City LIMIT 10")) {
printf("Select returned %d rows.\n", mysqli_num_rows($result));

/* free result set */
mysqli_free_result($result);
}


The mysqli_free_result($result) was supposed to be the key. The command should do some black voodoo magic in MySQL to prepare it for another query. HOWEVER — it doesn’t work with stored procedures. You have to do a different command with stored procs: mysqli_next_result($link). It took me THREE HOURS to figure this out. There was much banging my head against the wall. Maybe I just didn’t know what to search for, but Google wasn’t very helpful.

So, hopefully somebody else can benefit from my insanity. I’ll write it one more time.

If you’re using stored procedures with MySQL and PHP — use mysqli_next_result($link) in between calls if you are going to make more than one.