Using the mysqli Interface: Basic Techniques

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.