Opening and Using a Database Connection
Several PHP library functions are used to connect to a MySQL DBMS, run queries, retrieve results, and handle any errors that occur along the way. The presents.php script shown in Example 11-2 illustrates five of these functions in action.
<?php
// Show the user the available presents and the presents in their shopping
// list
// Include the DBMS credentials
include 'db.inc';
// Check if the user is logged in
// (this also starts the session)
logincheck( );
// Show the user the gifts
//
// Parameters:
// (1) An open $connection to the DBMS
// (2) Whether to show the available gifts with the option to add
// them to the shopping list ($delete = false) or to show the current
// user's shopping list with the option to remove the gifts ($delete = true)
// (3) The $user name
function showgifts($connection, $delete, $user)
{
// If we're showing the available gifts, then set up
// a query to show all unreserved gifts (where people IS NULL)
if ($delete == false)
$query = "SELECT *
FROM presents
WHERE people_id IS NULL
ORDER BY present";
else
// Otherwise, set up a query to show all gifts reserved by
// this user
$query = "SELECT *
FROM presents
WHERE people_id = "{$user}"
ORDER BY present";
// Run the query
if (!($result = @ mysql_query ($query, $connection)))
showerror( );
// Did we get back any rows?
if (@ mysql_num_rows($result) != 0)
{
// Yes, so show the gifts as a table
echo "
<table border=1 width=100%>";
// Create some headings for the table
echo "
<tr>" .
"
<th>Quantity</th>" .
"
<th>Gift</th>" .
"
<th>Colour</th>" .
"
<th>Available From</th>" .
"
<th>Price</th>" .
"
<th>Action</th>" .
"
</tr>";
// Fetch each database table row of the results
while($row = @ mysql_fetch_array($result))
{
// Display the gift data as a table row
echo "
<tr>" .
"
<td>{$row["quantity"]}</td>" .
"
<td>{$row["present"]}</td>" .
"
<td>{$row["colour"]}</td>" .
"
<td>{$row["shop"]}</td>" .
"
<td>{$row["price"]}</td>";
// Should we offer the chance to remove the gift?
if ($delete == true)
// Yes. So set up an embedded link that the user can click
// to remove the gift to their shopping list by running
// action.php with action=delete
echo "
<td><a href="action.php?action=delete&" .
"present_id={$row["present_id"]}">Delete from Shopping list</a>";
else
// No. So set up an embedded link that the user can click
// to add the gift from their shopping list by running
// action.php with action=insert
echo "
<td><a href="action.php?action=insert&" .
"present_id={$row["present_id"]}">Add to Shopping List</a>";
}
echo "
</table>";
}
else
{
// No data was returned from the query.
// Show an appropriate message
if ($delete == false)
echo "
<h3><font color="red">No gifts left!</font></h3>";
else
echo "
<h3><font color="red">Your Basket is Empty!</font></h3>";
}
}
?>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Sam and Rowe's Wedding Gift Registry</title>
</head>
<body bgcolor=#ffffff>
<?php
// Secure the user data
$message = clean($message, 128);
// If there's a message to show, output it
if (!empty($message))
echo "
<h3><font color="red"><em>{$message}</em></font></h3>";
// Connect to the MySQL DBMS
if (!($connection = @ mysql_pconnect($hostName, $username, $password)))
showerror( );
// Use the wedding database
if (!mysql_select_db($databaseName, $connection))
showerror( );
echo "
<h3>Here are some gift suggestions</h3>";
// Show the gifts that are still unreserved
showgifts($connection, false, $user);
echo "
<h3>Your Shopping List</h3>";
// Show the gifts that have been reserved by this user
showgifts($connection, true, $user);
// Show a logout link
echo "<a href="logout.php">Logout</a>";
?>
</body>
</html>The script in Example 11-2 shows the current user a list of gifts that are not reserved by any of the guests and a list of gifts reserved by the current user. Using this script (and the script action.php that we discuss later in Section 11.6) the user can add and remove gifts from her shopping list by clicking on the links next to each gift. Figure 11-1 shows the output of the script rendered in a Netscape browser.
The user must be logged in (the logincheck(
)
function is discussed later) and a message
parameter is expected by the script. As discussed earlier, parameters
can be passed with a URL, or a user can enter the data into an HTML
form. At this point, it’s not important how the data
is passed (we discuss this later in Section 11.7) but that a
$message variable is set.
The example has two parts: the main body and a function
showgifts( ). To begin, let’s
focus on the MySQL library functions that are prefixed with the
string mysql_. The main body has two MySQL
function calls:
// Connect to the MySQL DBMS if (!($connection = @ mysql_pconnect($hostName, $username, $password))) showerror( ); // Use the wedding database if (!mysql_select_db($databaseName, $connection)) showerror( );
The function mysql_pconnect(
)
is used to establish a connection to the DBMS. In the
example, three parameters are passed to the function: the values of
variables $hostName, $username,
and $password. These variables are initialized in
an auxiliary include file and are set to
localhost, fred, and
shhh respectively. The function returns a
connection resource
handle
. A handle is a value that can be used
to access information associated with the connection.
Connections opened with mysql_pconnect( ) can be
reused in other scripts. The p stands for
persistent, which means that after the script ends, the connection is
kept in a pool of open connections. The connection can then be reused
by any other script that requires a connection with the same host,
username, and password. Connections in the pool that are unused for
five seconds are closed to save resources. The time restriction is a
MySQL parameter that can be changed with the
--set-variable
connect_timeout
parameter when the MySQL server is
started.
The mysql_select_db(
)
function is then used to access the required database. Two parameters
are passed to the function in this example: the
$databaseName (set to wedding
in the auxiliary include file) and the $connection
handle that was returned from mysql_pconnect( ).
The main script also calls the showgifts(
)
function that runs the queries and
processes the results. It calls three MySQL library functions. The
first runs a query:
// Run the query if (!($result = @ mysql_query ($query, $connection))) showerror( );
The function takes two parameters: the SQL query and the DBMS
connection to use. The query is a string created at the beginning of
showgifts( ). The connection parameter is the
value returned from the earlier call to mysql_pconnect(
). The function mysql_query(
)
returns a result
set handle resource that is used to retrieve the
output of the
query.