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';
@ $db = new mysqli($host, $user, $pass, $dbname);
@ $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
$db->select_db($new_dbname);
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
$query = 'SELECT * FROM users';
$result = $db->query($query);
$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
$total_results = $result->num_rows;
$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';
while ($row = $result->fetch_assoc())
{
echo '<p>';
echo $row['username'].' ';
echo $row['firstname'].' ';
echo $row['lastname'].' ';
echo $row['city'].' ';
echo $row['state'].'</p>';
}
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
$row = $result->fecth_object();
$row = mysqli_fetch_object($result);
$row->username;
$row->firstname;
?>
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
$result->free();
$db->close();
mysqli_free_result($result);
mysqli_close($db);
?>
There you have it folks. This is as easy as 1, 2, 3.