This section presents the basic tasks you need to query a MySQL database from PHP.
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.
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.
18.118.193.7