Home arrow MySQL arrow Page 3 - Performing Basic Tasks with MySQL 4.1 and Above, using mysqli with PHP 5

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

For anyone who has spent a few weeks working with PHP 5, the plethora of cool improvements and new features added to this incarnation of the language has brought a new, more powerful level for developing and deploying Web applications. These include the implementation of exceptions and the brand new object model. But what if I tell you that now you can use PHP 5 to work with MySQL, using an object-oriented approach?

TABLE OF CONTENTS:
  1. Performing Basic Tasks with MySQL 4.1 and Above, using mysqli with PHP 5
  2. Performing basic operations: connecting to MySQL, running queries and more
  3. Leveraging the real power of the “mysqli” extension: running multiple queries
  4. Preparing SQL queries: using the “prepare()”, bind_param()” and “execute()” methods
By: Alejandro Gervasio
Rating: starstarstarstarstar / 16
June 27, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: