Using MySQLi with PHP with an OO Approach

Introduction

Since I have been talking about a database connection while using PHP and MySQL, I decided to write about the new library that has been incorporated within PHP. This library happens to be named MySQLi. The (i) stands for improved. This improved version of the library can be used from versions MySQL 4 and up. I will explain how to use this new library using an object oriented approach, but at the same time, will provide how to go at it, with a procedural process.

Connect to DB

To connect to a MySQL server, the following syntax is to be used:

<?php 

$host = 'localhost';
$user = 'yourusername';
$pass = 'yourpassword';
$dbname = 'databasename';

// OOP way
@ $db = new mysqli($host, $user, $pass, $dbname);
// Procedural way would be:
@ $db = mysqli_connect($host, $user, $pass, $dbname);

?>

Notice that we have used the error suppresor. In my recommendation, this error suppresor should only be used when you are able to catch an error on your own, and display an error message to the user. This suppressor has a warning in PHP.NET.

PHP.NET Warning

Reference: http://www.php.net/manual/en/language.operators.errorcontrol.php

Currently the “@” error-control operator prefix will even disable error reporting for critical errors that will terminate script execution. Among other things, this means that if you use “@” to suppress errors from a certain function and either it isn’t available or has been mistyped, the script will die right there with no indication as to why.

Carrying on with our topic, as you can see, we have instantiated the mysqli class. Since this instantiation returns an object, we can now invoke the methods of this class. In turn, utilizing the procedural way, this returns a resource, thus representing the database connection. This means that each time you will call a mysqli function, this same resource is needed, which will indicate what connection you are refering to.

Displaying an Error

Since we have suppresed the error in this case, we have a function that tells us if the connection was successful or not. This function can be invoked the same way for both procedural and object oriented way. In my opinion, it ‘looks’ like a standalone function. To display an error to the user, we do it like this:

<?php 
if(mysqli_connect_errno())
{
    die('The connection to the database could not be established.');
}
?>

And that’s how simple it is to display an error to the user, and exit the process, since our logic will not run correctly due to the missing database connection.

Changing Databases

If at a certain point, you need to change the database, you can do so with the following:

<?php 
// OOP way
$db->select_db($new_dbname);
// Or the procedural way
// We send the parameter $db as the resource
mysqli_select_db($db, $new_dbname);
?>

Running a Query

Let’s create a query to retrieve all the users and their information to display it on the screen. To do so, we would setup a string with our query, and send it to the function like this:

<?php
// Set up query
$query = 'SELECT * FROM users';
// OOP way
$result = $db->query($query);
// Procedural way
$result = mysqli_query($db, $query);
?>

Remember that when using the OOP way, your returned result will be an object, as to the procedural way, you will get another resource. If the above query is not successful for any reason, our result will be a simple ‘FALSE’.

Get the Results

Obviously, if we have just queried a database, it means that we need this data. If you would like to know how many records this query has returned, then we can go ahead and use the attribute num_rows. We achieve this with a simple line of code:

<?php 
// OOP way
$total_results = $result->num_rows;
// Procedural way
$total_results = mysqli_num_rows($result);
?>

Now that we know how many results are, we can go ahead and loop through the results, and display them on the screen. Let’s go ahead and use a while loop. The fetch_assoc function will return an array for each record that was found, and each will have each key as their attributes, and each value in the array.

<?php 

echo 'There are $total_results record(s) found';
// OOP way
while ($row = $result->fetch_assoc()) 
{
    echo '<p>';
    echo $row['username'].' ';
    echo $row['firstname'].' ';
    echo $row['lastname'].' ';
    echo $row['city'].' ';
    echo $row['state'].'</p>';
}

// Procedural way 
while ($row = mysqli_fetch_assoc($result))
{
    echo '<p>';
    echo $row['username'].' ';
    echo $row['firstname'].' ';
    echo $row['lastname'].' ';
    echo $row['city'].' ';
    echo $row['state'].'</p>';
}
?>

If you wanted to grab the results as an object, you could just use the fetch_object function.

<?php 
// OOP way
$row = $result->fecth_object();
// Procedural way
$row = mysqli_fetch_object($result);
// Attributes are accessed in the following:
$row->username;
$row->firstname; // Etc...
?>

Close Connection

Although PHP automatically closes your connection upon script termination, if you want to close the connection before your script is done, you can do so by just invoking the close function. This is done by doing the following. First though, you should ‘free’ up the result identifier, which will free up the memory. Then use the close function, to close the connection.

<?php 
// OOP way
$result->free();
$db->close();
// Procedural way
mysqli_free_result($result);
mysqli_close($db);
?>

There you have it folks. This is as easy as 1, 2, 3.