开发者

PHP Call a Second MySQL Stored Procedure or Query on the same connection?

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 hyperlink开发者_如何学Pythons 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(); 

...
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜