Menu
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).
1000
Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.
Save this answer. Show activity on this post. While both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks - ~2.5% for non-prepared statements, and ~6.5% for prepared ones.
Both PDO and MySQLi have their own advantages: As we have seen earlier that PDO works on 12 different database systems, whereas MySQL can work only with MySQL database. So, if we want to switch our project to another database, PDO makes it easy. In MySQLi, we have to rewrite the entire code.
To execute an SQL statement that returns one or more result sets, call the PDO::query method on the PDO connection object, passing in a string that contains the SQL statement. For example, you might want to call this method to execute a static SELECT statement.
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
65
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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
