New to MySQL Stored Procedures "Commands out of sync; you can't run this command now"

PHP
1

I’ve been using PHP for quite some time but I’m pretty new to using MySQL stored procedures. I have always written my SQL as inline SQL.

I’m currently getting this error “Commands out of sync; you can’t run this command now”. I’ve read the MySQL article on this error (http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html) but don’t really understand what it’s telling me.

My Code:

I’m calling a stored procedure called sp_LoginUser:

$result = mysqli_query($dbcnx, "CALL sp_LoginUser('".$_SERVER["REMOTE_ADDR"]."', '".$_SERVER["HTTP_USER_AGENT"]."', '".$userName."', '".$userPassword."')") or die ("an error occurred: " . mysqli_error($dbcnx));

if (!$result) { 
    error('Error'); 
}

At this point everything runs fine. When I call this second stored procedure:

$result = mysqli_query($dbcnx, "CALL sp_FetchUserDefaultContactInfo('".$userID."')") or die ("an error occurred: " . mysqli_error($dbcnx));

if (!$result) { 
    error('Error'); 
}

I’ve read the article regarding mysql_store_result() as well but don’t really understand when I would need to use that. If someone could explain to me why this is important or point me in the direction of a good tutorial that would be great.

2

if your first SP does not return data, then you need to close your result set:

$result->close();

if it does return data, then you need to process the rows first and close the result before you can run another SP or query.

3

What do you mean by process the rows? I attempted to close the result (regardless) with $result->close(); but I received the same error.

4

I found that if I close the MySQL connection and then reconnect before calling the 2nd stored procedure I don’t receive that error. I would imagine though that this isn’t a very efficient way to process commands.

5