MySQL in a Nutshell, 2nd Edition

Using PHP with MySQL

This section presents the basic tasks you need to query a MySQL database from PHP. Prior to PHP 5, MySQL was enabled by default. As of PHP 5, it’s not enabled and the MySQL library is not packaged with PHP. To enable MySQL with PHP, you need to configure PHP with the --with-mysql[=/path_to_mysql] option.

Connecting to MySQL

For a PHP script to interface with MySQL, the script must first make a connection to MySQL, thus establishing a MySQL session. To connect to the fictitious database workrequests, a PHP script might begin like this:

<?php
   
$host = 'localhost';
$user = 'russell';
$pw = 'dyer';
$db = 'workrequests';
   
mysql_connect($host, $user, $pw)
   or die(mysql_error);
mysql_select_db($db);
   
?>

This excerpt of PHP code starts by establishing the variables with information necessary for connecting to MySQL and the database. After that, PHP connects to MySQL by giving the host and user variables. If it’s unsuccessful, the script dies with an error message. If the connection is successful, the workrequests database is selected for use. Each PHP script example in this chapter begins with an excerpt of code like this one.

Querying MySQL

In the fictitious database is a table called workreq that contains information on client work requests. To retrieve a list of work requests and some basic information on clients, a PHP script begins by connecting to MySQL, as shown in the previous script excerpt. That is followed by the start of a web page and then the invocation of an SQL statement to retrieve and display the data. You can achieve this with code such as the following:

   ...  // Connect to MySQL
   
   <html>
   <body>
   <h2>Work Requests</h2>
   
   <?php
   $sql_stmnt = "SELECT wrid, client_name,
                 wr_date, description
                 FROM workreq, clients
                 WHERE status = 'done'
                 AND workreq.clientid = clients.clientid";
   
   $results = mysql_query($sql_stmnt)
              or die('Invalid query: ' . mysql_error());
   
   while($row = mysql_fetch_row($results)) {
      list($wrid, $client_name, $wr_date, $description) = $row;
      print "<a href='detail.php?wrid=$wrid'>$client_name - 
             $desription ($wr_date)</a><br/>";
   }
   
   mysql_close( );
   ?>
   </body>
   </html>

After connecting to MySQL (substituted with ellipses here) and starting the web page, a variable ($sql_stmnt) containing the SQL statement is created. Then the database is queried with the SQL statement, and a reference to the results set is stored in a variable ($results). The query is followed by an or statement, a common PHP syntax for error checking. The print statement executes only if no results are found.

Assuming PHP was successful in querying the database, a while statement is used to loop through each row of data retrieved from MySQL. With each pass, using the mysql_fetch_row() function, PHP will temporarily store the fields of data for each row in an array ($row). Within the code block of the while statement, the PHP list() function parses the elements of the $row array into their respective variables. The variables here are named to match their column counterparts. This is not necessary, though—they can be named anything. The array could even be used as it is and the appropriate sequence number referenced to retrieve data. For instance, for the date of the work request, $row[2] could be used because it’s the third in the sequence (0 is first). Naming the variables as they are here, though, makes it easier to read the code and easier for others to follow later.

The second line of code within the while statement displays the data in the format required for the web page. The data is wrapped in a hyperlink with a reference to another PHP script (details.php), which will retrieve all of the details for the particular work request selected by a user. That work request will be identified by the work request number (i.e., wrid), which is a key column for the details.php PHP script. Typically, the value for wrid will automatically be placed in a variable by the same name ($wrid) regardless of what the variable is named in this script. It’s based on the name given in the hyperlink or anchor tag. This will happen if the php.ini configuration file has register_globals set to on, something that is not the case in recent versions of PHP. On Unix and Linux systems, this file is located in the /etc directory. On a Windows system, it’s usually found in the c:\windows directory. If not, the value can be referenced using the $_GET associative array, which is describe in PHP’s online documentation (http://www.php.net).

The output of this script is a line for each incomplete work request found in the database. Each line will be linked to another script that presumably can provide details on the work request selected. In this simple example, only a few of the many PHP MySQL functions are used to display data. In the next section of this chapter, each function is described with script excerpts that show how they are used.

Previous Chapter

19. PHP API

Next Chapter

PHP MySQL Functions in Alphabetical Order

Table of Contents for MySQL in a Nutshell, 2nd Edition

Using PHP with MySQL

Connecting to MySQL

Querying MySQL

Table of Contents for
MySQL in a Nutshell, 2nd Edition