This article will illustrate some basic techniques for using the mysqli interface. This is a great tool to use in php5 because it doesn’t need any include libraries like PEAR, etc; it is compiled right into the distribution. In fact, to start using mysqli methods right away all you have to do is call the object with the right initialization parameters:
$dblink = new mysqli($dbLoc, $dbUser, $dbPw, $db) 
or die(mysqli_error($dblink));
I put the extra error handler in there so you could see any errors that may result in your connection. In this case I am connecting to a root user, no pw, on localhost, using the mysql world database, so the credentials are quite simple:
$db = 'world';
$dbLoc = 'localhost';
$dbUser = "root";
$dbPw = '';
Note: Obviously using these credentials on a web server could prove quite dangerous, but you knew that, right? Let’s hope so...
Once you have casted $dblink as a new mysqli object, as long as you have a successful database connection, (and data in your db to query), you can start working with it right away. I have, however, encapsulated some of the methods available to mysqli into a class I simply called DB, (some of you may not wish to use this class name, especially if you are using and/or are familiar with PEAR’s db class…). Let's go over an example of looping through a basic recordset, where you may find it much easier than the older mysql interface’s way of doing things (unless, of course, you have your own classes to automate such).
To start with, I’ve created a method in this class to store queries in an array by query name, something you may find useful. Here I store a query for the loop we are going to run:
$db->store_sql('select id, name, district from city limit 10', 'city_info');
Now, here's our callback using the inherited mysqli methods in the DB class I’ve created:
$result = $db->query($queries['city_info']) or die($db->error());

while ($row = $result->fetch_assoc()) {
	
	foreach ($row as $key=>$value) {
		echo ''.$key.': '.$value.'
'; } echo '
'; }
Take special note of the first iterator. I'm using the mysqli_fetch_assoc() method on the result object (which is an instance of the mysqli result object). This very cleanly allows you to iterate through each row as an array. Then, I just do a key value iterator for each column name retrieved in the query to create the output. This technique could easily be packaged up in another method in the db class, with perhaps some extra html handling capability, to output tables, forms, or even process forms, etc. with a little finagling of course.
All of this can of course be done with regular mysql functions, but the mysqli interface class is a bit cleaner than the older mysql functions. In addition, there are some newly available pieces of functionality, such as prepare_query and multi_query. First, let’s take a look at the prepare_query method (I have, by the way, borrowed almost verbatim these code snippets from the php5 manual, the main difference being in the way I packaged up the prepare_query method):

First, let’s store another query for ourselves using that method above:

$db->store_sql('SELECT District FROM City WHERE Name=?', 'prep_district');
Now, although the code to get the output is quite simple, it’s because the method prepared_query_result does all the work for you:
echo $db->prepared_query_result($queries['prep_district'], 'Kabul');
here is the code for that method:
function prepared_query_result($sql, $bind_param) {
    	$this->stmnt = $this->mysqli->prepare($sql); 
    	$this->stmnt->bind_param("s", $bind_param); 
		$this->stmnt->execute();
		$this->stmnt->bind_result($result);
		$this->stmnt->fetch();
		$this->stmnt->close();
		return $result;
    	}
Compare this to the code example in the php5 manual here. Instead of the printf() output in the php5 manual example, I’ve put that same code into a method and made it return your bound result so you can then do whatever you like with it, and also reuse this functionality.
This looks like a lot of work to just get a single string, but by preparing a query like this, you can optimize the system for speed and access the same information many times. The codes notes from the php5 manual link above show how you would do this:
  //put this function as a method in your 
 //existing DB class
   public function prepare ( $query = '' ) {
       return new PS( $this, $query );
   }
put this class definition after your DB class in the same document
class PS extends mysqli_stmt {

 public function __construct ( $mysqli, $query ) {
       parent::__construct( $mysqli, $query );
   }

}
See? I’m not the only one naming my classes DB, heh... the technique above would be excellent for storing large amounts of single parameter driven queries that need to be iterated through. By the way, I spent some time trying to store queries using just the prepare_query() method, but I couldn’t get the stored objects to work. The reason is because all methods associated with this prepared statement need to be their own instance of the mysql_stmt object.

Lastly, let’s take a look at the multi-query:

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5;";
I personally found this a bit hard to follow, but see if you can understand what’s going on here. First, we execute the query syntax as a multi query:
$db->multi_query($query) or die ($db->error());
Then, start a do block:
do {
Next, we store the result of the query using the mysqli’s store_result method and put a check around it to make sure it executes properly:
if ($result = $db->store_result()) {
if yes, start another iterator here for each row (note: we’re in the first query)
	while ($row = $result->fetch_row()) {
now, we print the data out (we know it’s only one piece of info, the username, so just the first element of the row array is needed):
		printf("%s
", $row[0]); } // end while loop Next we close the result set: $result->close(); } // end if block check for another queries data here, if yes, print a break tag: if ($db->more_results()) { printf("
"); } //end if
I understand if this code block looks confusing… but think about what this line is doing here:
} while ($db->next_result());
What’s going on here is for each query that is run, the first inner loop will iterate over all it’s values:
while ($row = $result->fetch_row()) { //print out data here }
then, when the loop is done, whatever result set you are in is closed, and then you run a check to see if there are more results (i.e. the results of another query ) available. If yes, an html break tag is printed out to separate the results, and the process is run all over again.
In Conclusion
In summary, the mysqli class can be very powerful for doing repetitive data tasks and makes it much easier to encapsulate robust methods into your own custom classes. If you haven’t yet taken advantage of it in php5, or done much Object Oriented coding, it may comprise the best of both worlds for you, as it is a quick and easy way to both begin working with OO easily inside php5, as well as make your database calls much simpler.
About the Author
Ben Robinson is an open source coder who enjoys working on dynamic web applications, especially in PHP and mysql, in the L.A.M.P. environment. His website is TierraLogic Systems (http://www.tierralogic.com/) if you’d like to pay him a visit.