MySQL Forums
Forum List  »  PHP

prepared statements for multiple queries
Posted by: Alexander Zorach
Date: March 22, 2009 01:43PM

After reading lots of webpages for promoting mysqli and the use of prepared statements, I was finally convinced to start using them, primarily because I want the performance gains of executing a query multiple times. I have already implemented them in my php scripts and they work beautifully--with a modest performance gain too. I have not yet, however, been able to get multiple execution to work the way I need it to.

I have one particular query that I have encapsulated in a single function, call it foo(bar). This query is a bottleneck on my site and I have already optimized it using indices. On some pages, this query is run as many as 10 times, and it is often run not all at once but interspersed with other queries. What I want to do is to have foo(bar) benefit from multiple execution of the prepared statement regardless of where it is called in the script and whether or not other database activity happens in between calls.

I can't seem to get a clear answer anywhere online--is it possible for me to prepare a query, execute it, keep the statement prepared, execute some other query, and then execute the prepared query more times? Is this possible at all? Is it possible with a single database connection? Or with a single php script using multiple connections? If it requires a separate connection, is it worth the overhead of the extra connection?

Every time I do this I have run into the problem that if I close the statement via mysqli_stmt_close or mysqli_stmt->close, the query is no longer prepared for execution, so I have to re-prepare it in order to run it again. However...if I don't close it...then I'm unable to run other queries in the same database connection.

I feel like there something I fundamentally don't understand about how prepared statements work. I'm a bit frustrated because I've been reading the php/mysqli documentation at length and I can't find any straight answers about exactly is possible or not possible here. All the examples I've found on the web simply show an example of a single prepared query. I would be very grateful for any feedback/advice or even a pointer to somewhere where I could learn about this.

Thanks in advance!

Edited 1 time(s). Last edit at 03/22/2009 05:30PM by Alexander Zorach.

Options: ReplyQuote

Written By
prepared statements for multiple queries
March 22, 2009 01:43PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.