When and How to use Multiple MySQL Queries with PHP (PDO)

Question

What are the benefits of using multiple MySQL queries in a statement, other than to minimize code.

How do you execute, retrieve, and display the results of multiple MySQL queries sent in one statement using PHP (and preferably PDO).

Answer 1

I'm not sure there is any benefit at all.

Answer 2

Regardless of which database you are using, it can be more efficient to put multiple queries into one statement. If you perform the queries separately, you have to make a call to the database across the network (or at least, between processes if on the same machine), get a connection to it (including autheticating), pass in the query, return the resultset, and release the connection for each query.

Even if you use connection pooling, you are still passing more requests back and forth than is necessary.

So, for example, if you combine two queries into one, you have saved all of that extra calling back and forth for the second query. The more queries you combine, then, the more efficient your app can become.

For another example, many apps populate lists (such as for dropdownlists) when they start up. That can be a number of queries. Performing them all in one call can accelerate startup time.

Answer 3

mysql_query() doesn't support multiple queries. However, there are some workarounds:

http://www.dev-explorer.com/articles/multiple-mysql-queries
http://php.net/function.mysql-query