How to Run Multiple MySQL Queries with PHP

Using PHP to Process More than One SQL Statement at the Same Time

Mar 29, 2009
Mark Alexander Bain

Running Multiple SQL Queries with PHP - Mark Alexander Bain

It is very easy to work with MySQL queries and PHP and it's nearly as easy to process more than one SQL statement at the same time. Just use the mysqli multi_query method

It’s very easy to send SQL queries to a MYSQL database and process the results (see How to Run MySQL Queries with PHP). However, running a number of queries one after another can be time consuming and memory intensive.

Fortunately PHP enables the programmer to run a number of queries at the same time and then process the returned results one record set at a time. The PHP programmer does this by using PHP’s mysqli object and its multi_query method.

Sending Multiple Queries to MySQL from PHP

Rather than repeatedly sending queries to the database it is possible to send a number of them at the same time. The first step, as always, is to instantiate the mysqli object:

$user = "db_user";

$user_password = "db_password";

$db_name = "db_demo";

$db = new mysqli ("localhost", $user, $user_password, $db_name);

With the connection in place the next stage is to create a string consisting of several SQL statements (remembering to separate each of them with a semi-colon):

$sql = "insert into articles

(title, url)

values

('How to Use the PHP Text Database API ',

'http://www.suite101.com/functions/article/edit.cfm/103536');";

$sql .= "insert into articles

(title, url)

values

('Using a PHP Session',

'http://www.suite101.com/functions/article/edit.cfm/102893');";

$sql .= "select * from articles";

Then the multi_query method sends the query to the database:

if ($db->multi_query($sql)) {

The PHP programmer will then need to process the results returned from the multiple queries. This is, of course, slightly more involved than handling a single query because more than one record set may be returned.

Processing the Results from Multiple Queries

When the PHP programmer processes the results from their queries they must:

Step though the record sets by using the next_result method
Load the current record set by using the store_result method

The end result is something like:

echo "<table width=100%>";

while ($db->next_result()) {

if ($resultset = $db->store_result()) {

while ($record = $resultset->fetch_array(MYSQLI_BOTH)) {

echo "<tr>

<td>" . $record['title'] . "</td>

<td>" . $record[2] . "</td>

</tr>";

}

$resultset->free();

}

echo "</table>";

} else {

echo $db->error . "<br>";

}

Here only one recordset will be returned and processed (since only a single select statement is being used).

Summary

The PHP programmer sends queries to a MySQL programmer by creating a mysqli object. This object connects to the database and then accepts a single SQL statement via the query method. Multiple SQL statements can be sent to the database but this time programmer must use the multi_query method. The result of each query depends on the type of query sent.

Only select statements return a set of records. All queries return true or false depending on the query’s success or failure. Once the PHP application has received the record set then the results can be processed and presented to the web page user or used elsewhere in the application itself.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...