Databases Reference
In-Depth Information
The first 16 lines of the file are HTML. Of these, the first eight lines of the script are
the preamble, which starts an HTML document, defines the content type, and sets the
page title. The following eight lines start a table, displaying column headings for the
data that follows. The final three lines of the file close the HTML document. The re-
mainder of the file—producing the content inside the HTML table—is the PHP script.
The PHP script is encapsulated in the
<?php
and
?>
tags. The script itself carries out
four main steps:
1. Connect to MySQL, using the
mysql_connect( )
MySQL library function
2. Select the database, using the
mysql_select_db( )
MySQL library function
3. Run the SQL query, using the
mysql_query( )
MySQL library function
4. Retrieve and display the data, using a
while
loop, the
mysql_fetch_array( )
MySQL
library function, the
print
statement, and a
foreach
loop
With the exception of the last step, you already know how to carry out these steps using
the MySQL monitor.
The first step in the PHP script is to connect to MySQL. This is performed with the
following fragment:
if (!($connection = @ mysql_connect("localhost", "root", "
the_mysql_root_password
")))
die("Cannot connect");
The function
mysql_connect( )
opens a connection, and the three parameters to the
function—
"localhost"
,
"root"
, and
"
the_mysql_root_password
"
—are the hostname of
the server, the MySQL user, and the user's password, respectively. The function does
the same thing as running the MySQL monitor: it authenticates you, giving you access
to the MySQL server so that you can run SQL statements. The other important feature
is that a connection
resource handle
is returned and saved in a PHP variable
$connection
; this is used in the following two steps. The at symbol (
@
) tells PHP not to
display its own error messages. If we discover a critical error, we call the
die( )
function
to display an error message and stop processing the script. We look at error handling
in detail in “Handling MySQL Errors” in Chapter 4.
The second step is to use a database. This occurs through this fragment:
if (!(mysql_select_db("music", $connection)))
die("Couldn't select music database");
The function
mysql_select_db( )
is the PHP library equivalent of the
USE
command. In
this fragment, the
music
database is selected as the current database. The second
parameter to the function,
$connection
, is the database connection resource handle
returned from the first step; it's used to identify the connection to the database server.
The third step runs the query. It performs the same function as typing it into the MySQL
monitor does, but it doesn't retrieve or display the results. Here's the code fragment:
if (!($result = @ mysql_query("SELECT * FROM artist", $connection)))
die("Couldn't run query");
