I delved into MySQLi last night (only used normal mysql_ before that) to call MySQL stored procedures as part of a calendar script on my site. The SP that loads calendar events and hyperlinks the days works perfectly each time by using $(mysqli)->query("call SP"). This SP takes no parameters and returns a set of rows.
I want to add, in the same script, the option to click on one of the days and show details of the event that day. As this is the same script, I want to use the same MySQLi connection, but each time I try, the return value of the query is a Boolean(false). The second SP takes the date as a parameter, and I've constructed it before the query() call. Printing it to the web page, and running it from the MySQL CLI yields the expected data, so the SP works, and the user is authorised to execute SPs.
Out of interest, I tried replacing the SP call with the SP contents in query(), and again received a 'false' return, so it seems to me that MySQLi is not allowing me to perform two queries on the same connection. I've done some tinkering and tried free() on the first result set, to no avail. As I expected, if I closed and reopened the MySQLi connection, I could call the SP, but this seems to be very inefficient. Is there a specific sequence to perform multiple MySQL calls (not simultaneously) on the same connection? I've searched but only vaguely, as I don't really know what to search for. Thanks.
You need to call next_result after each sproc call as stored procedures return 2 resultsets: the data and a counter resultset.
$db = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
$result = $db->query("call foo()");
$db->next_result(); // using sprocs have to do this before next call !
$result = $db->query("call bar()");
$db->next_result();
...