[header image]
When the old mysql extension for PHP was deprecated in 2011 due to a number of concerns such as security, lack of prepared statements, no way to do asynchronous queries, and so on, two other extensions became popular for connecting to a MySQL database with PHP: mysqli and PDO.
Both offer improvements to the old mysql extension. For developers familiar with the old extension, mysqli will likely be the easiest to convert to, as it will have more similarities to mysql than PDO. With that in mind, seeing how to use the mysqli extension can be helpful, so that you can either update old apps or create new ones using a better extension.
To get started with the mysqli extension, you simply need to have PHP 5.3 or higher installed in most cases. If so, the extension should already be installed and ready to use, you will simply need to access by creating a connection and making queries.
The old mysql extension should no longer be used. Source: PHPGang.com
You can connect to your database using the old procedural style from the mysql extension, but it is recommended to use the new object-oriented style, which offers more programming advantages while still offering much of the familiarity that one would have with the mysql extension.
The procedural style looks like this:
$connection = mysqli_connect(‘host’, ‘username’, ‘password’, ‘db_name’);
As you will see, this is just like using the old mysql style, but simply adding an ‘i’ to the command. While this is convenient and can help you quickly update old PHP code, the object-oriented method is preferred in more modern PHP scripts and will be used for the remainder of the examples.
The object-oriented style of connecting looks like this:
$mysqli = new mysqli(‘host’, ‘username’, ‘password’, ‘db_name’);
You will also want to make a check for connection errors, so that you can handle any error if necessary. Here is a simple example of this in action:
$mysqli = new mysqli(‘host’, ‘username’, ‘password’, ‘db_name’);
if ($mysqli->connect_error) {
die(‘Error : ‘. $mysqli->connect_error);
}
As you can see, this simply exits with the error message if something goes wrong when trying to connect to the MySQL database. Depending on your application, you may wish to do other things when catching this error.
Now that you can connect, you’ll want to be able to query the database for information. There are several methods you can use, depending on how you want the results organized.
To select records as an object, you can use the fetch_object() function. The object properties will be the names of the fields and will be associated with the value of that field in each returned row.
For example, the following selects records as an object and uses the results to build a short table.
$results = $mysqli->query(“SELECT id, name FROM users”);
print ‘<table>’;
while ($row = $results->fetch_object()) {
print ‘<tr>’;
print ‘<td>’ . $row->id . ‘</td>’;
print ‘<td>’ . $row->name . ‘</td>’;
print ‘</tr>’;
}
print ‘</table>’;
As you can see, this makes a nice tidy object you can use to access your data and keeps the object-oriented style. However, you may prefer your results in the form of an array or associative array.
If you wish results to be returned as an associative array, you can use the mysqli_fetch_assoc() function. Similar to how an object returns keys for the field names, an associative array of results will allow you to access its members by the field names, as shown below:
$results = $mysqli->query(“SELECT id, name FROM users”);
print ‘<table>’;
while ($row = $results->fetch_assoc()) {
print ‘<tr>’;
print ‘<td> ‘ . $row[“id”] . ‘</td>’;
print ‘<td>’ . $row[“name”] . ‘</td>’;
print ‘</tr>’;
}
print ‘</table>’;
If you prefer, you can expand this to allow not only for referencing items by key, but also by a numeric array index. This is done using the fetch_array() function. For example, the code below refers to items in the array by numeric index rather than key.
$results = $mysqli->query(“SELECT id, name FROM users”);
print ‘<table>’;
while ($row = $results->fetch_array()) {
print ‘<tr>’;
print ‘<td>’ . $row[0] . ‘</td>’;
print ‘<td>’ . $row[1] . ‘</td>’;
print ‘</tr>’;
}
print ‘</table>’;
When you are through with a result set, you can free the memory it is using the free() function. When you are through with the connection entirely, you can close it. For example:
$mysqli = new mysqli(‘host’, ‘username’, ‘password’, ‘db_name’);
if ($mysqli->connect_error) {
die(‘Error : ‘. $mysqli->connect_error);
}
$results = $mysqli->query(“SELECT id, name FROM users”);
print ‘<table>’;
while ($row = $results->fetch_object()) {
print ‘<tr>’;
print ‘<td>’ . $row->id . ‘</td>’;
print ‘<td>’ . $row->name . ‘</td>’;
print ‘</tr>’;
}
print ‘</table>’;
$results->free();
$mysqli->close();
As you can see, the connection is made, the query is sent, and the results are used. When done, the memory used by the result set is freed and the connection is closed. If you are transitioning from the old mysql extension, most of this should be fairly similar, thus making the transition a bit easier. If you are just starting, this gives you a quick introduction on how to get things going with PHP and MySQL!
