Using the mysqli API

To check whether your version of PHP includes mysqli support, run the phpinfo() command and look for a section headed MySQLi Support.

MySQLi Support

At compile time, MySQLi support is enabled by using the --with-mysqli configure switch. You might need to supply the path to the mysql_config utility, as in --with-mysqi=/usr/bin/mysql_config.

The MySQLi API can be used both procedurally and in an object-oriented manner. The procedural interface is very similar to the classic MariaDB APIfor instance, it uses functions such as mysqli_query() and mysqli_fetch_row(). Therefore, in this section, you learn how to use MySQLi with objects.

Connecting to MariaDB

The constructor function for a mysqli class object is used to initialize a MariaDB connection. The following statement creates a new object with a connection to a database on the local host:

$conn = new mysqli("localhost", "user", "password", "mysql10");

The constructor function actually has six optional arguments:

  1. hostThe hostname of the MariaDB server. This can be an IP address, domain name or hostname, or localhost where the MariaDB server and web server are running on the same host.

  2. usernameThe username for database authentication.

  3. passwdThe password that corresponds to the username passed in argument 2.

  4. dbnameThe database name to connect to. Unlike in the MariaDB API, you do not need to make a separate function call to select the database.

  5. portThe port number for TCP/IP connections. For localhost connections or to use the default port, omit this argument or pass NULL.

  6. socketThe name of the socket or named pipe for local connections. For TCP/IP connections or to use the default socket, omit this argument or pass NULL.

If the connection fails, call the mysqli_connect_errno() and mysqli_connect_error() functions to find the MariaDB error number and message text, respectively. Because the constructor function failed, you cannot call these functions as methods on a mysqli object.

Listing 24.3 creates a new mysqli database connection with error trapping.

Listing 24.3. Establishing a New Database Connection Using the MySQLi API
<?php
$conn = new mysqli("localhost", "user", "pass", "mysql10");
if (mysqli_connect_errno()) {
 echo "Unable to connect to MariaDB server <>";
 echo "Error " . mysqli_connect_errno() . " - " . mysqli_connect_error();
 exit;
}
?>

Executing a Query

When you have a mysqli object that is connected to a MariaDB server, you can execute a query using the query() method. Its argument is a string that contains a single SQL query; the terminating semicolon is not required. The following statement is an example:

$result = $conn->query("SELECT * FROM customers");

If the query is successful, a result object is returned that then is used to find information about the query and to retrieve data rows if the query was a SELECT statement.

Otherwise, the return value is FALSE. You can check the errno and error properties on the connection object to find the MariaDB error number and message text, respectively.

Object Properties

Some pieces of information that are retrieved using functions in the classic MariaDB API or when using MySQLi procedurally become object properties instead of methods when you use MySQLi with objects.

For example, the function mysqli_error() becomes a property of a database connection object,$conn->errornote that there are no parentheses here.

Finding Information About a Query

If the query you executed was a SELECT statement, you can find how many data rows it returned by checking the num_rows property on the result object. It takes a single argumenta query result handle returned by mysql_query()and returns the number of rows in the resulting data set. The following statement is an example:

echo $result->num_rows . " row(s) were returned";

The field_count property similarly contains the number of columns in the data set. The affected_rows property contains the number of rows affected by an UPDATE, INSERT, or DELETE statement.

Fetching Queried Data

The most powerful way to fetch data from a result handle is with the fetch_object() or fetch_row() methods. The first time you call $result->fetch_object(), the first row of data is returned. Each subsequent call returns the next row until no data remains to be fetched.

The data is returned into an object that contains a property for each column in the data set. The property's name is the same as the selected column name or alias in the query.

For example, suppose that your script executes the query SELECT code, price FROM products ORDER BY price and then uses fetch_object() to return the first data row into $data. The value of $data->code would be the code for the first product returnedwith this query, the least expensive product in the tableand $data->price would hold the corresponding price value.

The fetch_row() method works in the same way, except that it creates an array structure rather than an object, with each element indexed both numerically and associatively. Using the same example query, $data[0] and $data["code"] would both contain the product code.

Listing 24.4 puts everything together into a script that connects to MariaDB, executes a query, and fetches and displays the resulting data.

Listing 24.4. Performing a Query Using the MySQLi API
<?php
$conn = new mysqli("localhost", "user", "pass", "mysql10");
if (mysqli_connect_errno()) {
 echo "Unable to connect to MariaDB server <>";
 echo "Error " . mysqli_connect_errno() . " - " . mysqli_connect_error();
 exit;
}
$sql = "SELECT * FROM customers ORDER BY name";
$result = $conn->query($sql);
if (!$result) {
 echo "Unable to execute query <>";
 echo "Error " . $result->errno . " - " . $result->error;
 exit;
}
echo "<table border=1>\n";
while ($data = $result->fetch_row()) {
 echo "<tr>";
 for ($i=0; $i<$result->field_count; $i++) {
 echo "<td>" . $data[$i] . "</td>\n";
 }
 echo "</tr>\n";
}
echo "</table>\n";
?>

This script uses the fetch_row() method to retrieve data because the output is generated in a loop. In many cases, you will know the column names for the data you are working with, and the fetch_object() method might be more convenient.

MariaDB vs. MySQLi

If you are familiar with another PHP database API, you will be familiar with the way the classic MariaDB interface works. To see how the object-oriented MySQLi API compares, run through Listing 24.2 and Listing 24.4 step by stepthey give exactly the same result.

Tidying Up

To deallocate a resource object, call the $result->free() method. Doing so destroys the object, so you no longer can call its methods or view its properties.

To disconnect from the database, call $conn->close(). The database connection is closed immediately and the connection object is destroyed.

Previous Page Next Page