To check whether your version of PHP includes mysqli support, run the phpinfo() command and look for a section headed MySQLi Support.
The MySQLi API can be used both procedurally and in an object-oriented manner. The procedural interface is very similar to the classic mysql 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 MySQL
The constructor function for a mysqli class object is used to initialize a MySQL 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:
If the connection fails, call the mysqli_connect_errno() and mysqli_connect_error() functions to find the MySQL 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
Executing a Query
When you have a mysqli object that is connected to a MySQL 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 MySQL error number and message text, respectively.
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 and $data["code"] would both contain the product code.
Listing 24.4 puts everything together into a script that connects to MySQL, executes a query, and fetches and displays the resulting data.
Listing 24.4. Performing a Query Using the MySQLi API
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.
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.