How to call stored procedures with mysqli

1. The main problem with stored procedures
2. Comments (1)

Calling a stored procedure in mysqli is helluva tricky business. There are bugs and limitations to avoid as well as many tricks to employ.

Well, to be honest, in the simplest case possible (when a stored procedure is the only sql query your code has to run, and the procedure itself is relatively simple, not using cursors or returning multiple result sets) you can call it just as any other query.

But the more complex your stored procedure gets, the more complex the calling code would be.

The main problem with stored procedures

There is one thing about stored procedures any programmer stumbles upon at first: every stored procedure always returns one extra result set: one (or many) results with actual data, and one just empty. Which means if you try to call a procedure and then proceed to another query, then "Cannot execute queries while other unbuffered queries are active" error will occur, because you have to clear out that extra empty result first.

Therefore, in order to proceed we've got to winkle out that extra result set from mysql first. And mysqli::next_result does exactly that. So basically we would only need to call this function in order to let our next SQL queries work.

Also, we will need this function

Calling a stored procedure without parameters that returns a single result set

As it was said above, in case your procedure is the only database interaction on the page, you can call it right away, without any additional stuff. But usually it is not the case and you have to execute other queries after the procedure. Hence, we've got to take out that extra result set from our way.

When our query doesn't take any parameters and returns just one result set, we could add just a single call to mysqli::next_result

$result = $mysqli->query("call mysqli_call()");
echo json_encode($result->fetch_all(MYSQLI_ASSOC)), "\n";
$mysqli->next_result();
// here we go - the next query works!
$next_query = $mysqli->query("SELECT 'next' as `from` from dual");
echo json_encode($next_query->fetch_assoc()), "\n";

Calling a stored procedure without parameters that returns multiple result sets