13.1. Using PHP with MySQL | MYSQL in a Nutshell (In a Nutshell (OReilly))

This section presents the basic tasks you need to query a MySQL database from PHP.

13.1.1. Connecting to MySQL

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

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

This section 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, though, the workrequests database is selected for use. Each PHP script example in this chapter begins with a section of code like this one.

13.1.2. 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 printf("%s", mysql_error( ));        while(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 were 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 may be named anything. The array could even be used as it is and the appropriate sequence number referred 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. The value for wrid typically 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 which 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 $_POST associative array, which is describe in PHP's online documentation (http://www.php.net).

The output of this script is a line for each uncompleted work request found in the database. Each line will be linked to another script that presumably would 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 as examples of their use.