Leveraging the real power of the “mysqli” extension: running multiple queries - MySQL

If you’re anything like me, then I’m sure that very often your PHP applications must run numerous queries. The good news is that the “mysqli” library offers some helpful methods for running multiple queries and dealing with more than one result set.

With reference to this improved functionality, the following example illustrates the process for executing two SELECT statements in one single pass, by using some new methods such as “multi_query()”, “store_result()”, more_result()” and “next_result()” respectively. Take a look at the source code:

$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
// run multiple queries
$sql="SELECT * FROM users;SELECT name FROM USERS";
if($mysqli->multi_query($sql)){
    do{
        // store first result set
        if($result=$mysqli->store_result()){
            while($row=$result->fetch_array(MYSQLI_ASSOC)){
                echo $row['id'].' '.$row['name'].' '.$row
['email'].'<br />';
            }
            $result->close();
        }
        // display header of second result set
        if($mysqli->more_results()){
            echo 'Displaying results of second query...<br />';
        }
   }while($mysqli->next_result());
}
// close connection
$mysqli->close();

In this case, I first concatenated the two SELECT statements with a semicolon (;) character, and then executed them by using the “multi_query()” method. Since this process also returns two result sets, I utilized a “do-while” loop, in order to display the first dataset, and next show the second one.

As you can see, the script’s driving logic is implemented around the “next_result()” method, which prepares the subsequent result set after calling the “multi_query()” method. In short, both data sets are processed in the following sequence: the first one is transferred to the $result object by the “store_result()” method and then displayed properly, while the second one is shown after checking for an additional result set by utilizing the “more_result()” method.

Well, all these tasks probably sound a little confusing in theory, so allow me to show you a practical case. Say you have the following rows stored in a “USERS” table:

1 User 1  user1@domain.com
2 User 2  user2@domain.com
3 User 3  user3@domain.com
4 User 4  user4@domain.com
5 User 5  user5@domain.com

Based on the script listed above, the corresponding output would be the following:

1 User 1  user1@domain.com
2 User 2  user2@domain.com
3 User 3  user3@domain.com
4 User 4  user4@domain.com
5 User 5  user5@domain.com

Displaying results of second query...

User 1
User 2
User 3
User 4
User 5

As you can see, the “mysqli” extension will let you run multiple queries in a fairly accessible way, as well as process multiple result sets, something that was clearly demonstrated by the example you saw before. The set of previous methods implemented together shows a comprehensive approach for running several SQL statements in one single shot, and eventually manipulating all the returned result sets.

So far, you learned how to run multiple queries, in addition to handling more than one MySQL data set at the same time. Quite probably, you’re thinking, "is that all there is to the 'mysqli' extension?" Of course not, since there are many more features that I'd like to show you.

Therefore, over the next few lines, I’ll be explaining how to prepare queries by using the pertinent “prepare()” method, which can be used to insert parameters inside your SQL statements. To see how this will be done, please keep reading.