Performing Queries from PHP


Almost every Web application has a need to somehow store data. This data can be shopping cart information, quotes, or just about anything else that you can imagine. In previous chapters we have skirted this issue or used plain-text files to store minimal amounts of data. Although functional, it also limits the capabilities of your scripts significantly.

In practical Web applications, almost all data is stored in some fashion within a relational database system such as MySQL. In the previous chapter, I introduced you to the MySQL database system outside of PHP and how the SQL language can be used to store and retrieve data from it. In this chapter we'll be building off that knowledge and introduce how you can take advantage of the capabilities of MySQL from within your PHP scripts using the MySQLi extension. To follow the examples in this chapter, you'll need a working MySQL server, the capability to create tables in a database within that server, and the MySQLi extension installed. For assistance in getting MySQL working in PHP, see Appendix A, "Installing PHP 5 and MySQL."

MySQLi Basics

The MySQLi extension, like MySQL itself, is a very robust extension enabling you to work with almost every facet of the MySQL server. As a result, upon first glance the extension and its use can be somewhat confusing. Like most things, however, when it is broken down into smaller, easily consumed pieces (which is what we'll be doing in this chapter), it's fairly simple to work with.

To begin, let's go over the basic steps of accessing a MySQL database from within a PHP script:

1.

Connect to the MySQL database.

2.

Select the database to use.

3.

Perform the desired SQL queries.

4.

Retrieve any data returned from the SQL queries.

5.

Close the connection to the database.

Notice that these steps don't vary much from what would be expected when working with the MySQL client, as in the previous chapter. In fact, a single function can be associated with each of these steps.

Connecting to the Database

The first step in any database transaction from PHP is connecting to the database. When you're using MySQL directly, this is analogous to executing the MySQL client application; however, in PHP this is done by using the mysqli_connect() function. The syntax for this function is as follows:

 mysqli_connect([$hostname [, $username [, $password                 [, $dbname [, $port [, $socket]]]]]]); 

As you can see, the mysqli_connect() function takes a number of parameters, all of which are optional and most of which should be fairly self-explanatory. Starting from the left, the first parameter is the $hostname parameter, which represents the hostname where the server is located. Because it is often the case that the MySQL server is located on the same machine as your Web server, this often is the "localhost" value. The next two parameters, $username and $password, represent the username and password to use when authenticating to the MySQL server. The third parameter, $dbname, is a string representing the name of the database to use by default. The last two parameters, $port and $socket, are used in conjunction with the $hostname parameter to specify a specific port or socket to be used for the connection.

When executed, the mysqli_connect() function will attempt to connect to the MySQL server using the parameters provided. Upon success, this function will return a resource representing a connection to the database or false on failure.

NOTE

The link resource returned by mysqli_connect() must not be discarded. Unlike previous versions of PHP, the link resource is required as a parameter for every operation.


In general, at a minimum this function requires three parameters ($hostname, $username, and $password); however, it is not uncommon to see the fourth parameter, $dbname, also used to select a default database.

Selecting a Database

While we are on the topic of selecting a database to use, it is appropriate to point out that a database does not necessarily have to be selected through the mysqli_connect() function. After a connection has been created, the mysqli_select_db() function can be used to select the current database in the same way the USE SQL command was used from the client. The syntax for the mysqli_select_db() function is as follows:

 mysqli_select_db($link, $dbname); 

$link is the database connection resource returned from the mysqli_connect() function, and $dbname is a string representing the name of the database to select. This function will return a Boolean TRue if the new database was successfully selected or false on failure.

Performing a Basic Query

Now that we know how to connect to a MySQL server and select a database to use, it's time to start performing SQL queries against it. To perform queries, we will use the mysqli_query() function with the following syntax:

 mysqli_query($link, $query [, $resultmode]); 

$link is the database connection and $query is a string containing a single SQL query without a terminating semicolon (;) or (\g). The third optional parameter, $resultmode, determines how the resultset from the query will be transferred back to PHP. This parameter is either the MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT (the default value) constants. This parameter is used to indicate whether the MySQLi extension should copy the entire resultset to memory after a query (MYSQLI_STORE_RESULT), or only the current row in the resultset. In practical terms, storing the entire resultset in memory allows your scripts to access any row within the resultset arbitrarily when otherwise each row could be read only sequentially. Unless you are working with particularly large data sets, it is generally acceptable to ignore this parameter. Upon success, mysqli_query() will return a resource representing the result of the query or a Boolean false on failure.

Fetching Resultset Rows

When performing queries that modify the tables within a database, such as INSERT and UPDATE, generally there is no data to return from the database. However, queries such as SELECT, which retrieve data from the database, require that data must somehow be accessed from within PHP. When returning the results from a resultset into PHP, various options are available, each providing a unique usefulness depending on the circumstance.

The most general of the result-retrieving functions is the mysqli_fetch_array() function. The syntax for this function is as follows:

 mysqli_fetch_array($result [, $array_type]) 

$result is the resource representing the query result returned from a call to the mysqli_query() function, and the optional parameter $array_type is one of the following constants:

MYSQLI_ASSOC

Return an associative array.

MYSQLI_NUM

Return an enumerated array.

MYSQLI_BOTH

Return both an enumerated and associative array.


If no value is provided, the MYSQLI_BOTH constant is the default value.

Applying this function to your scripts is a fairly simple task. After a query has been performed using the mysqli_query() function, the result of that query is passed to a function such as the mysqli_fetch_array() function, which will return a single row of the result table. For every subsequent call, mysqli_fetch_array() will return another row from the result table until there are no more rows available, in which case mysqli_fetch_array() will return a Boolean false.

As its name implies, the mysqli_fetch_array() function will return each row as an array. The details of how that array is formatted, however, depend on the optional $array_type parameter. If the MYSQLI_ASSOC constant is used, the mysqli_fetch_array() function will return an associative array whose keys represent the column names of the resultset and whose values represent the appropriate value for each column for the current row. On the other hand, if the MYSQLI_NUM constant is used, mysqli_fetch_array() will return an enumerated array representing the current row, where index zero is the first column, 1 is the second, and so on. The final (and default) constant MYSQLI_BOTH, as its name implies, returns an array that contains both associative and enumerated keys and values for the current resultset.

NOTE

Along with the mysqli_fetch_array() function, PHP also provides the mysqli_fetch_row() and mysqli_fetch_assoc() functions. These functions take a single parameterthe result resource returned from mysqli_query()and returns either an enumerated or associative array. Functionally, the mysqli_fetch_row() function is identical to calling the mysqli_fetch_array() function using the MYSQLI_NUM constant for the $array_type parameter. Likewise, the mysqli_fetch_assoc() function is identical to calling mysqi_fetch_array() using the MYSQLI_ASSOC parameter.


To illustrate the use of the mysqli_fetch_array() function, consider the following example, which will retrieve all the results from a hypothetical query into a simple HTML table (see Listing 24.4):

Listing 24.4. Using the mysqli_fetch_array() Function
 <?php      $link = mysqli_connect("localhost", "username", "password");      if(!$link) {           trigger_error("Could not connect to the database", E_USER_ERROR);      }      mysqli_select_db($link, "unleashed");      $result = mysqli_query("SELECT first, last FROM members");      if(!$result) {           trigger_error("Could not perform the specified query", E_USER_ERROR);      }      echo "<TABLE><TR><TD>First Name</TD><TD>Last Name</TD></TR>";      while($row = mysqli_fetch_array($result)) {           echo "<TR>";           echo "<TD>{$row['first']}</TD><TD>{$row['last']}</TD>";           echo "</TR>";      }      echo "</TABLE>;      mysqli_close($link); ?> 

As you can see in Listing 24.4, we start by connecting to our MySQL database using the mysqli_connect() function and then proceed to select the unleashed database using the mysqli_select_db() function. After the database has been selected, a SELECT query can be performed (in this case to retrieve the first and last name from the hypothetical table members) using the mysqli_query() function.

At this point, the $result variable contains a resource representing the resultset of the query SELECT first, last FROM members. To retrieve the data from that resultset, we use the mysqli_fetch_array() function to return the first row of the resultset and store it as an array in the $row variable. The results for that row are then printed and the process continues until the entire resultset has been traversed.

Under certain circumstances, it may be beneficial to be able to randomly access any particular row within a resultset instead of sequentially, as has been introduced so far. To accomplish this, the "current" row that MySQLi will retrieve using a function such as mysqli_fetch_array() must be adjusted using the mysqli_data_seek() function. The syntax for this function is as follows:

 mysqli_data_seek($result, $row_num); 

$result is the MySQLi resultset resource and $row_nu is the zero-indexed row number to move to.

NOTE

As previously indicated, the mysqli_data_seek() function is available only when the resultset was stored in memory by passing the MYSQLI_STORE_RESULT constant to mysqli_query() or the equivalent.


Counting Rows and Columns

Often, it is useful to know how many rows or columns exist for a given resultset. For these purposes, MySQLi provides the mysql_num_rows() and mysqli_num_fields() functions, whose syntax follows:

 mysqli_num_rows($result) mysqli_num_fields($result) 

In each function, $result is the resource representing the resultset. Each of these functions will return a total count of its respective rows or columns for the provided resultset. If no rows were returned or an error occurred, these functions will return a Boolean false.

Freeing Results

After a query is performed, the resultset for that query is stored in memory until the PHP script that performed the query is terminated. Although generally this is acceptable, when you are working with large resultsets, it becomes important to free the resultset in memory. This can be done using the mysqli_free_result() function as follows:

 mysqli_free_result($result) 

$result is the resource representing the resultset from a query. As I stated previously, although it is not always necessary to free the memory from a resultset, it is considered best practice to do so when a resultset is no longer needed.

NOTE

Using the unset() function will not free the result! You must use the mysqli_free_result() function.


Retrieving Error Messages

Now that we have gone over some of the fundamentals of using the MySQLi extension, before we go any further it's important to discuss the tools available when something goes wrong (when an error occurs).

The first step in dealing with errors is to realize that an error has occurred. Although at times this can be obvious (a visible error message is displayed), in most cases the actual function where the error occurred does not produce any visible warning or error. Instead, the MySQLi function in question will return a Boolean false instead of the desired resource or expected value. When such a situation occurs, the MySQLi extension will provide the script with an integer error code as well as a string describing the error. To retrieve these values, we use the mysqli_errno() and mysqli_error() functions. The syntax for these functions is as follows:

 mysqli_errno($link); mysqli_error($link); 

In both situations, $link is the resource representing the database connection. These two functions will return the most recent error code and description associated with the provided database connection.

NOTE

As you may have noticed, the error-reporting functionality in the MySQLi extension requires that a database connection actually exist to work. Hence, failures that occur during a call to the mysqli_connect() cannot use these error-reporting functions to determine the cause of the error. Rather, use the mysqli_connect_errno() and mysqli_connect_error() functions that accept no parameters.


To illustrate the use of the MySQLi extension's error-reporting functions, consider an example of their use in Listing 24.5:

Listing 24.5. Using mysqli_error() and mysqli_errno()
 <?php      $link = mysqli_connect("hostname", "username", "password", "mydatabase");      if(!$link) {         $mysql_error = "Connection error: ".mysqli_connect_error();           die($mysql_error);          }      $result = mysqli_query($link, "SELECT * FROM foo");      if(!$result) {           $errno = mysqli_errno($link);           $error = mysqli_error($link);         die("Query Error: $error (code: $errno)");      } ?> 

In the example found in Listing 24.2, note that there are two potential places where an error can occur. The first is during the call to the mysqli_connect() function. Because an error at that stage would leave the script without a valid database resource, the mysqli_connect_error() and mysqli_connect_errno() must be used. In the second error check for the mysqli_query() function, however, the error-reporting functions can be used to determine why the query failed.

Closing the Database Connections

Although PHP will automatically take care of closing any outstanding database connections (as appropriate), a connection to a database can be explicitly closed using the mysqli_close() function with the following syntax:

 mysqli_close($link) 

$link is the resource representing the database connection to close.

Executing Multiple Queries

One of the features most lacking in the old MySQL extension is the capability to perform multiple queries from a single PHP command. In MySQLi, such an action is now possible through the use of the mysqli_multi_query() function. The syntax of this function is as follows:

 mysqli_multi_query($link, $queries); 

$link is a valid MySQLi database connection and $queries is one or more SQL queries separated by a semicolon character. When executed, the mysqli_multi_query() function returns a Boolean indicating the success of the operation.

Unlike the mysqli_query() function, note that the mysqli_multi_query() function does not return a result directly. To retrieve the first resultset of a multiquery operation, either the mysqli_store_result() or mysqli_use_result() functions must be used. As was the case with the $resultmode parameter of the mysqli_query() function, these functions determine how the MySQL client will access the data contained within the resultset. The syntax of these functions follows:

 mysqli_store_result($link); mysqli_use_result($link); 

$link is the MySQLi database link. When executed, this function will return a result resource for the resultset. This resultset may then be used in the normal fashion using the MySQLi API to retrieve the individual rows.

Because the mysqli_multi_query() function executes multiple different queries sequentially, one or more resultsets may be retrieved. The first of these resultsets will be available immediately after the mysqli_multi_query() function is executed. To advance to the next resultset, two functions are provided to assist you. The first of these functions is the mysqli_more_results() function with the following syntax:

 mysqli_more_results($link); 

$link is the MySQLi database link. This function will return a Boolean value indicating whether more resultsets are awaiting processing. To access the next available resultset, the mysqli_next_result() function is used:

 mysqli_next_result($link); 

$link is again the MySQLi database link. This function will advance the current resultset to the next available, which can then be retrieved using the mysqli_store_result() or mysqli_use_result() function.

To demonstrate the execution of multiple queries, consider Listing 24.6:

Listing 24.6. Multiple Queries Using MySQLi
 <?php     $mysqli = new mysqli("localhost", "username", "password",                          "mydatabase", 3306);     $queries = "SELECT * FROM mytable; SELECT * FROM anothertable";     if(mysqli_multi_query($mysqli, $queries)) {         do {             if($result = mysqli_store_result($mysqli)) {                 while($row = mysqli_fetch_row($result)) {                     foreach($row as $key => $value) {                         echo "$key => $value<BR/>\n";                             }                 }                 mysqli_free_result($result);                     }             if(mysqli_more_results($mysqli)) {                                 echo "<BR/>\nNext result set<BR/>\n";             }         } while(mysqli_next_result($mysqli));     }     mysqli_close($mysqli); ?> 

Creating a Visitor-Tracking System

Now that we have introduced the basic functions used when interacting with a database from the MySQLi extension, a practical example is in order. The purpose behind this example is to produce a transparent visitor-tracking system for your website. This system should be able to keep track of where a particular visitor came in from (that is, the page they first visited), where they went in the site, as well as a number of other different statistics.

The first step to creating any database-driven Web application is to design the underlying database that will be used. For this particular application we'll need two tables in the database: tracker, which will hold the specific tracking information for each visitor, and tracker_ips, which will associate the session IDs for each visitor to their IP address. The structure of these tables is as defined by the following CREATE SQL statements:

 CREATE TABLE tracker(sess_id varchar(32), page varchar(255), time timestamp); CREATE TABLE tracker_ips(sess_id varchar(32) PRIMARY KEY, ip varchar(15)); 

Now that the tables have been created, let's lay out a few PHP scripts that will be used in this program. The first of these scripts is a configuration script I've called connect.inc, which contains the login and connection information for the MySQL database. Following is an example of the connect.inc file in Listing 24.7:

Listing 24.7. The connect.inc File
 <?php     $mysql['username'] = "username";     $mysql['password'] = "password";     $mysql['database'] = "unleashed";     $mysql['host']     = "localhost";     $mysql['port']     = NULL;     $mysql['socket']   = NULL; ?> 

The connect.inc file by itself is not very useful. This file is designed to be included by the next script, tracker.inc, which is as shown in Listing 24.8:

Listing 24.8. The tracker.inc File
 <?php     require_once('connect.inc');     function query_array($query, $link) {         $result = mysqli_query($link, $query);         if(!$result) {             trigger_error("Query failed, could not populate array");         }         $variable = array();         while(($data = mysqli_fetch_array($result))) {             $variable[] = $data;         }         mysqli_free_result($result);         return $variable;     }     function connect_db() {         global $mysql;         $link = mysqli_connect($mysql['host'],                                $mysql['username'],                                $mysql['password'],                                $mysql['port'],                                $mysql['socket']);         if(!$link) {             trigger_error("An error occurred connecting to the MySQL server.");         }         if(!mysqli_select_db($link, $mysql['database'])) {             $error = mysqli_error($link);             $errno = mysqli_errno($link);             trigger_error("An error occurred selecting the database " .                            "(error msg: $error [code: $errno])");         }         return $link;     } ?> 

In this script I have defined two functionsthe query_array() function and the connect_db() function. These functions are mere wrappers for the MySQLi functionality that I have already discussed and are designed to encapsulate functionality that will be used at many points in the entire script.

The first major thing each script does is connect to the database. Hence, I have rolled all the logic associated with connecting to a database into the connect_db() function. This function takes advantage of the $mysql global array as defined by connect.inc, which is included at the top of the file.

Along with connecting to a database, performing a query and retrieving the resultset as an array is another common task for which the query_array() function was designed. This function is given a SQL query (the $query parameter) and a link to the database (the $link parameter) and returns a multidimensional array containing the entire resultset.

The next script that we are interested in provides the functionality of tracking people as they visit the website. The code for this file (called tracker.php) is as follows (see Listing 24.9):

Listing 24.9. The tracker.php File
 <?php     require_once("tracker.inc");     $link = connect_db();     session_start();     $query = "SELECT sess_id FROM tracker_ips WHERE sess_id='".session_id()."'";     $result = mysqli_query($link, $query);     if(!$result) {         $error = mysqli_error($link);         $errno = mysqli_errno($link);         trigger_error("Error performing query (error: $error [code: $errno])");     }     if(mysqli_num_rows($result) == 0) {         $ip = $_SERVER['REMOTE_ADDR'];         $query = "INSERT INTO tracker_ips VALUES('".session_id()."', '$ip')";         mysqli_query($link, $query);         $_SESSION['tracking'] = true;     }     $current_page = $_SERVER['PHP_SELF'];     $query = "INSERT INTO tracker VALUES('" .               session_id() .              "', '$current_page', NULL)";     mysqli_query($link, $query);         mysqli_close($link); ?> 

As you can see, this file makes use of the tracker.inc file discussed previously. This script is designed to be included on every page where you would like tracking enabled and provides all the actual functionality of the tracking system.

Looking back at the two database tables in this system, we have the TRacker and TRacker_ips tables. For this system to work, for every page request the IP address of the requesting client must be searched for in the tracker_ips table. If the IP does not have a record in the table, it must be added. In either case, the actual page that IP visited must also be added to the database into the TRacker table. This process is reflected in Listing 24.9. Assuming the three files I've discussed are in your include path, you can now keep track of each page a user visits simply by including the tracker.php script on those pages where you would like to track users.

NOTE

For security reasons, it is strongly recommended that the connect.inc file be stored somewhere outside of your Web server's document root and then included from within PHP. Otherwise, it is possible that any client could request the file from the Web server and get your login information!


Although we have created the functionality that is needed to populate the database with the data necessary to track users, it does little good without an appropriate representation of that data. To make any reasonable use of this script, you must be able to display this data in an understandable format. This interface is provided by two additional PHP scripts that I will discuss now, starting with Listing 24.10:

Listing 24.10. The stats.php Script
 <?php     require_once('tracker.inc');     $link = connect_db();     $query = "SELECT COUNT(ip) as visitors                 FROM tracker_ips             GROUP BY ip";     $result = mysqli_query($link, $query);     $total_visitors = mysqli_fetch_object($result)->visitors;     mysqli_free_result($result);     $query = "SELECT page, count(page) AS visits                 FROM tracker             GROUP BY page             ORDER BY visits DESC";     $total_per_page = query_array($query, $link);         $query = "SELECT ip, count(page) as views                 FROM tracker, tracker_ips                WHERE tracker.sess_id = tracker_ips.sess_id             GROUP BY ip             ORDER BY views DESC";     $views_per_ip = query_array($query, $link);     mysqli_close($link); ?> <HTML> <HEAD> <TITLE>Tracker Statistics</TITLE> </HEAD> <BODY>     <CENTER>     <H2>Tracker Statistics</H2>     Total number of visitors: <B><?php echo $total_visitors; ?></B>    <BR>     <H3>Total visitors per page</H3>     <TABLE CELLPADDING=0 CELLSPACING=5 BORDER=1>     <TR>     <TD><B>Page</B></TD><TD><B>Visitors</B></TD>     </TR>     <?php foreach($total_per_page as $row) {             echo "<TR>";             echo "<TD>{$row['page']}</TD><TD>{$row['visits']}</TD>";             echo "</TR>";     }     ?>     </TABLE>         <BR>     <H3>Page views per IP address</H3>     <TABLE CELLPADDING=0 CELLSPACING=5 BORDER=1>     <TR>     <TD><B>IP Address</B></TD><TD><B>Pages viewed</B></TD>     </TR>     <?php foreach($views_per_ip as $row) {             $url = "<A HREF='ip_stats.php?ip={$row['ip']}'>{$row['ip']}</A>";             echo "<TR>";             echo "<TD>$url</TD><TD>{$row['views']}</TD>";             echo "</TR>";     }     ?>     </TABLE>     </CENTER> </BODY> </HTML> 

This script, named stats.php, is used to provide the top-level interface to the two tables that store all the tracking information I previously discussed. This script performs three separate queries, each of which provides a different set of data that is then displayed in an HTML document.

The first of these queries is the easiest. It calculates the total number of unique visitors by executing the following query against the tracker_ips table:

 SELECT COUNT(ip) as visitors FROM tracker_ips GROUP BY ip 

Because this script returns a single column and row, the process of retrieving this value can be simplified by returning the resultset as an object. Because objects can be referenced directly from the return value, the following line of code is used to populate the $total_visitors variable with the results from the query:

 $total_visitors = mysqli_fetch_object($result)->visitors; 

The next query executed is used to determine how many unique visitors exist per page and looks like the following:

 SELECT page, count(page) AS visits FROM tracker GROUP BY page ORDER BY visits DESC 

Because the resultset of this query is more than a single row and column, the standard methods of retrieving the resultset must be used. Here we finally take advantage of the query_array() function I introduced earlier in Listing 24.8.

The final query that is executed on this page is used to calculate another useful piece of informationhow many pages each person who visited the website viewed. This is accomplished by performing the following query against the database:

 SELECT ip, count(page) as views     FROM tracker, tracker_ips    WHERE tracker.sess_id = tracker_ips.sess_id GROUP BY ip ORDER BY views DESC 

Now that we have gathered up all the data that is to be displayed, the remainder of the script is fairly self-explanatory. The resulting HTML page that is generated renders something like that shown in Figure 24.1.

Figure 24.1. Sample HTML page.


Although this generation is all simple array traversals and HTML, the one facet that deserves discussion is the last table generated (the Page Views per IP Address table). Note that each IP address is rendered as a link to a second PHP script named ip_stats.php. This link is used to drill-down into the data within the database and provide the interface that enables you to determine exactly which and in what order your website was browsed.

Looking at stats.php, you can see that the HTML link for each IP passes the IP address via the GET method to ip_stats.php, which looks like the following (see Listing 24.11):

Listing 24.11. The ip_stats.php Script
 <?php     require_once('tracker.inc');     if(!isset($_GET['ip'])) {         trigger_error("Sorry, you must provide an IP to display");     }     $ip = $_GET['ip'];     $link = connect_db();     $query = "SELECT page, count(page) as visits                 FROM tracker, tracker_ips                WHERE tracker.sess_id = tracker_ips.sess_id                  AND ip='$ip'             GROUP BY page";     $page_visits = query_array($query, $link);     $query = "SELECT page, time                 FROM tracker, tracker_ips                WHERE tracker.sess_id = tracker_ips.sess_id                  AND ip = '$ip'             ORDER BY time";     $history = query_array($query, $link);     mysqli_close($link); ?> <HTML> <HEAD> <TITLE>Statistics for IP <?php echo $ip; ?></TITLE> </HEAD> <BODY>     <CENTER>     <A HREF="stats.php">Click here to return</A><BR>     <H2>Hit count per page for <?php echo $ip; ?></H2>     <TABLE CELLPADDING=0 CELLSPACING=5 BORDER=1>     <TR>     <TD><B>Page</B></TD><TD><B>Visits</B></TD>     </TR>     <?php foreach($page_visits as $row) {             echo "<TR>";             echo "<TD>{$row['page']}</TD><TD>{$row['visits']}</TD>";             echo "</TR>";     }     ?>     </TABLE>     <BR>     <H2>Time-lapse of page views for <?php echo $ip; ?></H2>     <TABLE CELLPADDING=0 CELLSPACING=5 BORDER=1>     <TR>     <TD><B>Page</B></TD><TD><B>Viewed At</B></TD>     </TR>     <?php foreach($history as $row) {             echo "<TR>";             echo "<TD>{$row['page']}</TD><TD>{$row['time']}</TD>";             echo "</TR>";     }     ?>     </TABLE>     </CENTER> </BODY> </HTML> 

As was the case with the stats.php script, the ip_stats.php script performs a number of queries that are then formatted in HTML and displayed to the user. Specifically, this script performs two queries. The first is as follows, which determines how many times the given IP address visited a particular page:

 SELECT page, count(page) as visits          FROM tracker, tracker_ips         WHERE tracker.sess_id = tracker_ips.sess_id           AND ip='$ip'      GROUP BY page 

The second query provides a different type of detail by showing a time-lapsed view of how each IP address browsed the website.

 SELECT page, time                  FROM tracker, tracker_ips                 WHERE tracker.sess_id = tracker_ips.sess_id                   AND ip = '$ip'              ORDER BY time 

As was the case in stats.php, each of these queries is executed and the results are stored as arrays that are then traversed and displayed. Although this script is not intended to be executed directly, it can be demonstrated by clicking one of the IP address links from the stats.php script. An example of the output can be found in Figure 24.2:

Figure 24.2. Sample output page.


Prepared Statements

The concept of a prepared statement is not new to the world of databases. Many enterprise-class database packages support them, and MySQL 4.1 is no different. The act of executing a query on a remote database server is a relatively expensive process for both the database server itself and your PHP scripts. Every time a query is executed, it must be compiled and sent to the server, executed, and then the results returned back to the client. Because every time your PHP script is executed, this process must be repeated, the costs associated with your database connection can become quite substantial. Prepared statements are useful to reduce the overhead associated with exactly this situation by sending only that data that changes between two otherwise identical SQL queries.

There are two types of prepared statements: those that are bound by their parameters and those that are bound by result. We'll look at each of these next:

Bound Parameter Statements

Consider this SQL statement from Listing 24.11, used in the IP tracker script:

 $query = "SELECT page, count(page) as visits                 FROM tracker, tracker_ips                WHERE tracker.sess_id = tracker_ips.sess_id                  AND ip = '$ip'             GROUP BY page"; 

In this SQL query the only variable is the IP address. However, every time this query is executed, it must be compiled, transmitted, and processed by the database server. Rather than incur all that unnecessary overhead per query, bound parameter statements instead send a template query to the database to be stored. This template is then compiled only once, and future queries need to send only the actual parameters instead. These parameters are then combined with the already ready-to-use SQL query on the server, and the results are returned. Because the query itself is sent and compiled only once by the server, the performance gains can be significant.

Prepared SQL queries are no different from any other query, except instead of specifying the parameters of the query themselves, a question mark ? is used to denote their location, as shown next:

 $query = "SELECT page, count(page) as visits                 FROM tracker, tracker_ips                WHERE tracker.sess_id = tracker_ips.sess_id                  AND ip = ?             GROUP BY page"; 

Note that in our prepared query, the quotes that were needed by the original version have been removed. This highlights another very positive feature of prepared statementsthey do not need to be quoted, nor do they need to be escaped to prevent SQL injection attacks. Rather, such details are handled internally by the MySQLi extension and the corresponding database server, as necessary.

Executing a prepared statement is a three-step process: the transmission of the query template to the server, the binding of a prepared query to variables within PHP, and the execution of the query itself. The first of these tasks, the preparation of a MySQL query template, is handled using the mysqli_prepare() function whose syntax is as shown:

 mysqli_prepare($link, $prepared_query); 

$link is the mysqli database link and $prepared_query is the SQL query template to prepare. Upon execution, the mysqli_prepare() function returns a statement representing the prepared query.

After a prepared statement has been created, the parameters specified in the template can be bound to PHP variables using the mysqli_bind_param() function. The syntax of this function is as follows:

 mysqli_bind_param($stmt, $types, $param [, $param2 [...]]); 

$stmt represents the resource returned from the mysqli_prepare() statement. This function accepts any number of parameters and is used to specify the values to be used in a prepared statement. Starting from left to right in the prepared SQL query, the $types parameter is a string that represents the type of data the corresponding SQL query parameter contains. The accepted types are as shown in Table 24.1.

Table 24.1. Prepared Statements Parameter Types

i

Integer types (all of them)

d

Double or Floating point numbers

b

Blobs

s

All other types


Thus, if your prepared statement contained two VARCHAR and one FLOAT type, the string "ssd" would be used for the $types parameter.

Immediately following the $types parameter are all the values that are to be bound. These parameters must be presented in the same order as in the prepared statement itself, $types parameter.

It is critically important to note that when a variable is bound to a prepared statement, any changes made to that variable in PHP will affect the parameter used in the query! Consider the following query:

 INSERT INTO books VALUES(?, ?, ?); 

and the following prepare/bind statement:

 <?php     /* Code omitted for example */     $bookname = "PHP Developer's Handbook";     $bookisbn = "067232511X";     $bookprice = 49.95;     $stmt = mysqli_prepare($mysqli, "INSERT INTO books VALUES(?, ?, ?)");     mysqli_bind_param($stmt, "ssd", $bookname, $bookisbn, $bookprice); ?> 

When the variables $bookname, $bookisbn, and $bookprice are bound to the statement, the values these variables contain are not actually used until the statement is executed. Thus, changing the $bookname variable after the mysqli_bind_param() statement will change the value used in the query itself. In fact, the variables provided to the mysqli_bind_param() function do not even have to exist when they are bound to the query. Thus the following will also create an identical prepared statement:

 <?php     $stmt = mysqli_prepare($mysqli, "INSERT INTO books VALUES(?, ?, ?)");     mysqli_bind_param($stmt, "ssd", $bookname, $bookisbn, $bookprice);     /* Changing these values modifies the query stored in $stmt */     $bookname = "PHP Unleashed";     $bookisbn = "067232511X";     $bookprice = 49.95; ?> 

The third and final step in working with a parameter-bound prepared statement is the execution of the statement. This is done using the mysqli_stmt_execute() statement, which accepts the statement to execute as its single parameter:

 mysqli_stmt_execute($stmt); 

NOTE

Instead of mysqli_stmt_execute(), an alias mysqli_execute() can also be used.


When executed, the current values for the bound parameters will be used in the query and a Boolean will be returned indicating the success of the query. After a statement has been executed and is no longer needed, it can be destroyed using the mysqli_stmt_close() function:

 mysqli_stmt_close($stmt); 

Listing 24.12 demonstrates a full example of using parameter binding in MySQLi.

Listing 24.12. Parameter Binding in MySQLi
 <?php     $mysqli = mysqli_connect("hostname", "user", "pass", "database");     if(mysqli_connect_errno()) {         die("Could not connect: ".mysqli_connect_error());     }     /* Assume a table corresponding to this CREATE statement exists:         CREATE TABLE books(name VARCHAR(255),                            isbn VARCHAR(10),                            price FLOAT)     */     $bookname = "PHP Unleashed";     $bookisbn = "067232511X";     $bookprice = 49.95;     $stmt = mysqli_prepare($mysqli, "INSERT INTO books VALUES(?, ?, ?)");     mysqli_bind_param($stmt, "ssd", $bookname, $bookisbn, $bookprice);     mysqli_execute($stmt);     mysqli_stmt_close($stmt);     mysqli_close($mysqli); ?> 

Binding Result Values

In the previous section, I discussed the creation of prepared statements using parameter binding. In this section, we'll look at using result binding to access the results of a SQL query. Recall from the previous section that modifying the contents of a bound parameter affected the parameter within the query (even if the variable was modified after the binding of it). When working with results a similar behavior exists, except this time it deals with the results returned. Consider the following query:

 SELECT first, last, phone FROM contacts WHERE first LIKE 'John%' 

As shown, when this query is performed, a three-column result table will be returned for those rows that match the query. As was the case with parameter binding, these three resultset columns can also be bound to PHP variables. This is accomplished using the mysqli_stmt_bind_result() function:

NOTE

The mysqli_stmt_bind_result() function has a shorter alias mysqli_bind_result().


 mysqli_stmt_bind_result($stmt, $res1 [, $res2 [, ...]]); 

$stmt is the statement resource that was executed. Each additional parameter specified in the mysqli_stmt_bind_result() function will be bound to a corresponding resultset column. Thus, the number of variables specified is directly connected to the number of columns in the resultset.

Now that you understand how to bind PHP variables to resultset columns, how is the individual data from each row extracted? For these bound variables to be populated with resultset data, that data must be fetched into them using the mysqli_stmt_fetch() function. This function accepts a single parameter representing the statement to fetch from:

 mysqli_stmt_fetch($stmt); 

When executed, the mysqli_stmt_fetch() function retrieves a row from the resultset and populates the corresponding bound variables with the values from it. The mysqli_stmt_fetch() function returns true if the fetch was executed, false if an error occurred, or NULL if no more rows exist.

NOTE

Don't forget! Although false and NULL are two different types, they will both evaluate to Boolean false unless one of the type-checking comparison operators (such as ===) is used.


Listing 24.13 demonstrates the use of result binding:

Listing 24.13. Result Binding in Prepared Statements
 <?php     $mysqli = mysqli_connect("hostname", "user", "pass", "database");     if(mysqli_connect_errno()) {         die("Could not connect: ".mysqli_connect_error());     }     $query = "SELECT first, last, phone FROM contacts WHERE first LIKE 'John%'";     $stmt = mysqli_prepare($mysqli, $query);     mysqli_execute($stmt);     mysqli_stmt_bind_result($stmt, $first, $last, $phone);     while(($res = mysqli_stmt_fetch($stmt))) {         echo "First: $first<BR/>\n";         echo "Last: $last<BR/>\n";         echo "Phone: $phone<BR/>\n";     }     if($res === false) {         die("An error occurred fetching: ".mysqli_error($mysqli));     }     mysqli_stmt_close($stmt);     mysqli_close($mysqli); ?> 

Transactions

Transactions are a key feature available in the new MySQLi extension, allowing your PHP scripts to ensure that a series of SQL operations either execute completely or not at all. Although transactions themselves have been available from MySQL 4.0 and later, the MySQLi extension is the first to provide a series of function calls for working with them from within PHP.

To use transactions in MySQL, you must create a transaction-compatible table within your database. To do so, it must be either an InnoDB or a BDB table type. This is done by specifying a type parameter to your SQL CREATE statement, as shown:

 CREATE TABLE mytable(mycolumn VARCHAR(255)) TYPE=innodb; 

To use transactions in MySQLi, three functions are provided. The first of these functions is the mysqli_autocommit() function. This function determines MySQL's behavior when dealing with a transaction-compatible database by determining whether, by default, all SQL queries will immediately take effect. The syntax of this function is as shown:

 mysqli_autocommit($link, $mode); 

$link is the MySQLi database link resource and $mode is a Boolean indicating whether autocommits are enabled. When executed, this function returns a Boolean indicating whether the operation executed successfully. To determine the current state of autocommits on the server, the following SQL statement can be executed:

 SELECT @@autocommit 

It returns a single row and column, which will be 1 if it is enabled or 0 if it is not.

To actually use transactions, it is important that the autocommit feature be disabled by calling mysqli_autocommit() with a value of false. After it is disabled, any changes made against transaction-compatible tables must be committed before the changes will take effect. To commit a change to a table, the mysqli_commit() function is used, passing only the database link resource $link, as shown:

 mysqli_commit($link); 

As I stated at the beginning of this section, transactions allow you to ensure that a series of SQL operations are all executed, or not at all. Prior to a call to the mysqli_commit() function, any and all operations that took place prior to the last call to mysqli_commit() may be reverted (or "rolled back") with a call to the mysqli_rollback() function. The syntax of this function is as follows:

 mysqli_rollback($link); 

Listing 24.14 uses these functions to demonstrate the use of transactions:

Listing 24.14. Using Transactions in MySQLi
 <?php     $mysqli = new mysqli("localhost", "username", "password",                          "mydatabase", 3306);     $query = "CREATE TEMPORARY               TABLE friends (name VARCHAR(50), age INT) TYPE=InnoDb";     mysqli_query($mysqli, $query);     mysqli_autocommit($mysqli, false);     $friends = array(                     array("name" => "Max",                           "age" => 22),                     array("name" => "Cliff",                           "age" => 45),                     array("name" => "Hollie",                           "age" => 18));     foreach($friends as $friend) {         $query = "INSERT INTO friends VALUES('{$friend['name']}',                                               {$friend['age']})";         mysqli_query($mysqli, $query);     }     mysqli_commit($mysqli);     $result = mysqli_query($mysqli, "SELECT COUNT(*) FROM friends");     $rows = mysqli_num_rows($result);     echo "There are $rows row(s) in the table.<BR/>\n";     mysqli_query($mysqli, "DELETE FROM friends");     $result = mysqli_query($mysqli, "SELECT COUNT(*) FROM friends");     $rows = mysqli_num_rows($result);     echo "There are $rows rows(s) in the table (after delete)<BR/>\n";     mysqli_rollback($mysqli);     $result = mysqli_query($mysqli, "SELECT COUNT(*) FROM friends");     $rows = mysqli_num_rows($result);     echo "There are $rows rows(s) in the table (after rollback)<BR/>\n";     mysqli_close($mysqli); ?> 



PHP 5 Unleashed
PHP 5 Unleashed
ISBN: 067232511X
EAN: 2147483647
Year: 2004
Pages: 257

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net