Learning MySQL (2007)
Part VI. Appendix
Appendix A. The Wedding Registry Code
Overview
In this appendix, we list the complete code of the wedding registry application we developed in Chapter 15. This code is also available for download from the book’s web site.
Example A-1 contains the code in action.php; Example A-2 contains the code in db.php; Example A-3 contains the disclaimer code; Example A-4 contains the code in edit.php; Example A-5 contains the code in index.php; Example A-6 contains the text in license.txt; Example A-7 contains the code in list.php; and Example A-8 contains the code in logout.php.
Example A-1. action.php
<?php
// action.php: Add or remove a gift from the user's shopping list
// Include database parameters and related functions
require_once("db.php");
// Check if the user is logged in
// (this also starts the session)
logincheck();
// Secure the user data
if(count($_GET))
{
// Connect to the MySQL DBMS and use the wedding database - credentials are
// in the file db.php
if(!($connection= @ mysqli_connect(
$DB_hostname, $DB_username, $DB_password, $DB_databasename)))
showerror($connection);
$gift_id = clean($_GET['gift_id'], 5);
$action = clean($_GET['action'] , 6);
// Is the action something we know about?
if($action != "add" && $action != "remove")
// No, it's not; perhaps someone's trying to manipulate the
// URL query string?
die("Unknown action: ".$action);
// The program should reach this point only if the action is add
// or remove, since otherwise processing stops with the die()
// instruction.
// What did the user want us to do?
if ($action == "add")
{
// The user wants to add a new item to their shopping list.
// Update the gifts table if we find the gift and it is not
// taken by any user.
// This query avoids the need to lock the table.
$query = "UPDATE gifts SET username = '{$_SESSION['username']}' ".
"WHERE gift_id = {$gift_id} AND username IS NULL";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// If we found the row and updated it, create a confirmation
// message to show the user
if (mysqli_affected_rows($connection) == 1)
{
$message = "Reserved the gift for you, {$_SESSION['username']}";
}
else // Couldn't reserve the gift because it wasn't free;
{
// Check whether it's booked by someone else
$query = "SELECT * FROM gifts ".
"WHERE gift_id = {$gift_id} ".
"AND username != '{$_SESSION['username']}'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Create a message to show the user
if (mysqli_num_rows($result))
$message = "Oh dear... someone just beat you to that gift!";
else
$message = "The gift is already reserved for you, ".
"{$_SESSION['username']}";
}
}
else // The action is not add, so it must be remove
{
// The user wants to remove an existing item from their shopping list.
// Create a query to retrieve the gift.
$query = "SELECT * FROM gifts WHERE gift_id = {$gift_id}";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Get the matching gift row;
// (there's only one since the gift_id is the primary key)
// If we don't get exactly one answer, then we have a problem
for($matchedrows=0;($row = @ mysqli_fetch_array($result));$matchedrows++);
if($matchedrows!=1)
die("We've just experienced a technical problem - ".
"please notify the administrator.");
// Double-check they actually have this gift reserved
if (!empty($row["username"]) && $row["username"] != $_SESSION['username'])
// They don't, so record a message to show the user
$message = "That's not your gift, {$_SESSION['username']}!";
else
{
// They do have it reserved. Create a query to unreserve it.
$query = "UPDATE gifts SET username = NULL WHERE gift_id = {$gift_id}";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Create a message to show the user
if (mysqli_affected_rows($connection) == 1)
$message = "Removed the gift from your shopping list, ".
"{$_SESSION['username']}";
else
$message = "There was a problem updating. ".
"Please contact the administrator.";
}
}
}
// Redirect the browser back to list.php
header("Location: list.php?message=" . urlencode($message));
exit;
?>
Example A-2. db.php
<?php
// These are the DBMS credentials and the database name
$DB_hostname = "localhost";
$DB_username = "fred";
$DB_password = "shhh";
$DB_databasename = "wedding";
// Show an error and stop the script
function showerror($connection)
{
// Was there an error during connection?
if(mysqli_connect_errno())
// Yes; display information about the connection error
die("Error " . mysqli_connect_errno($connection) .
" : " .mysqli_connect_error($connection));
else
// No; display the error information for the active connection
die("Error " .mysqli_errno($connection) . " : "
.mysqli_error($connection));
}
// Secure the user data by escaping characters and shortening the
// input string
function clean($input, $maxlength)
{
// Access the MySQL connection from outside this function.
global $connection;
// Limit the length of the string
$input = substr($input, 0, $maxlength);
// Escape semicolons and (if magic quotes are off) single and
// double quotes
if(get_magic_quotes_gpc())
$input = stripslashes($input);
$input = mysqli_real_escape_string($connection, $input);
return $input;
}
// Check if the user is logged in. If not, send them to the login
// page
function logincheck()
{
session_start();
if (empty($_SESSION["username"]))
{
// redirect to the login page
header("Location: index.php");
exit;
}
}
?>
Example A-3. disclaimer
<?php
echo "\n<table width=\"60%\">";
echo "\n<tr><td>\n<hr />";
echo "\n<i>This is not really a wedding registry.
It's a system that demonstrates the concepts of web database systems,
and is downloadable source code that you can use freely under this
<A href=\"license.txt\">license</a>. It pretends to
allows wedding guests to log in, view a list of gifts wanted
by the bride and groom, and reserve gifts that they plan to
purchase by putting them on a shopping list.</i>";
echo "\n</td>\n</tr>\n</table>\n<br />";
?>
Example A-4. edit.php
<?php
// edit.php: Show the user the available gifts and the gifts in
// their shopping list
// Include database parameters and related functions
require_once("db.php");
// Check if the user is logged in
// (this also starts the session)
logincheck();
// Check that the user is Jack or Jill (username is 'jack' or
// 'jill'); other users are not allowed to edit the gifts.
if($_SESSION['username']!="jack" && $_SESSION['username']!="jill")
{
$message = "You are not authorized to edit the gift details. Please ".
"select gift suggestions from the list to add to your shopping list!";
header("Location: list.php?message=".urlencode($message));
exit;
}
// Connect to the MySQL DBMS and use the wedding database - credentials are
// in the file db.php
if(!($connection= @ mysqli_connect(
$DB_hostname, $DB_username, $DB_password, $DB_databasename)))
showerror($connection);
// See if we've arrived here after clicking the delete link
if(count($_GET) && (clean($_GET['action'], 10)=='delete'))
{
// Yes; compose a query to delete the specified gift from the
// gifts table
$query = "DELETE FROM gifts WHERE gift_id=".clean($_GET['gift_id'], 10);
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
}
// See if we've arrived here after clicking the update button; if
// so, update the gift details.
elseif(isset($_POST['update']))
{
// Define an SQL query to list the gift IDs in the database
$query = "SELECT gift_id FROM gifts";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Process the submitted data for each gift ID in the database
while($row = @ mysqli_fetch_array($result))
{
$gift_id=$row["gift_id"];
// Update an existing gift if there is corresponding data
// submitted from the form
if(
isset($_POST["quantity" ][$gift_id]) &&
isset($_POST["description"][$gift_id]) &&
isset($_POST["color" ][$gift_id]) &&
isset($_POST["shop" ][$gift_id]) &&
isset($_POST["price" ][$gift_id])
)
update_or_insert_gift_data($connection, $gift_id);
}
// Process the data submitted in the form fields for the new
// gift; we had assigned this the index 0 in the HTML form.
update_or_insert_gift_data($connection, 0);
}
// Update the data for a gift with the specified gift ID; for a
// gift ID of 0, add a new gift to the database.
function update_or_insert_gift_data($connection, $gift_id)
{
// Extract the data items for the gift attributes from the $_POST array
$quantity =clean($_POST["quantity" ][$gift_id], 5);
$description=clean($_POST["description"][$gift_id], 255);
$color =clean($_POST["color" ][$gift_id], 30);
$shop =clean($_POST["shop" ][$gift_id], 100);
$price =clean($_POST["price" ][$gift_id], 30);
// If the gift_id is 0, this is a new gift, so set the
// gift_id to be empty; MySQL will automatically assign a
// unique gift_id to the new gift.
if($gift_id==0)
$gift_id='';
// If any of the attributes are empty, don't update the database.
if(
!strlen($quantity ) ||
!strlen($description) ||
!strlen($color ) ||
!strlen($shop ) ||
!strlen($price )
)
{
// If this isn't the blank row for optionally adding a new gift,
// or if it is the blank row and the user has actually typed
// something in, display an error message.
if(!empty($gift_id)
||
strlen(
$quantity.
$description.
$color.
$shop.
$price)
)
echo "<font color='red'>".
"There must be no empty fields - not updating:<br />".
"([$quantity], [$description], [$color], [$shop], [$price])".
"<br /></font>";
}
else
{
// Add or update the gifts table
$query = "REPLACE INTO gifts ".
"(gift_id, description,shop,quantity,color,price,username) values (".
"'$gift_id', '$description', '$shop', $quantity,
'$color', '$price', NULL)";
// Run the query through the connection
if (@ mysqli_query($connection, $query)==FALSE)
showerror($connection);
}
}
// Show the user the gifts for editing
//
// Parameters:
// (1) An open $connection to the DBMS
function showgiftsforedit($connection)
{
// Create an HTML form pointing back to this script
echo "\n<form action='{$_SERVER["PHP_SELF"]}' method='POST'>";
// Create an HTML table to neatly arrange the form inputs
echo "\n<table border='1'>";
// Create the table headings
echo "\n<tr>" .
"\n\t<th bgcolor='LIGHTGREEN'>ID</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Description</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Quantity</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Color</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Available from</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Price</th>" .
"\n\t<th bgcolor='LIGHTGREEN'>Delete?</th>" .
"\n</tr>";
// Create an SQL query to list the gifts in the database
$query = "SELECT * FROM gifts ORDER BY description";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Check whether we found any gifts
if(!mysqli_num_rows($result))
// No; display a notice
echo "\n\t<tr><td colspan='7' align='center'>".
"There are no gifts in the database</td></tr>";
else
// Yes; fetch the gift details a row at a time
while($row = @ mysqli_fetch_array($result))
// Compose the data for this gift into a row of form inputs
// in the table.
// Add a delete link in the last column of the row.
echo "\n<tr>" .
"\n\t<td>{$row["gift_id"]}</td>".
"\n\t<td><input name='description[{$row['gift_id']}]' ".
"value='{$row["description"]}' size='60' /></td>".
"\n\t<td><input name='quantity[{$row['gift_id']}]' ".
"value='{$row["quantity"]}' /></td>".
"\n\t<td><input name='color[{$row['gift_id']}]' ".
"value='{$row["color"]}' /></td>".
"\n\t<td><input name='shop[{$row['gift_id']}]' ".
"value='{$row["shop"]}' size='30' /></td>".
"\n\t<td><input name='price[{$row['gift_id']}]' ".
"value='{$row["price"]}' /></td>".
"\n\t<td><a href='{$_SERVER['PHP_SELF']}?".
"action=delete&gift_id={$row["gift_id"]}'>Delete</a></td>".
"\n</tr>";
// Display a row with blank form inputs to allow a gift to be added
echo "\n<tr><td>New item</td>" .
"\n\t<td><input name='description[0]' size='60' /></td>".
"\n\t<td><input name='quantity[0]' /></td>".
"\n\t<td><input name='color[0]' /></td>".
"\n\t<td><input name='shop[0]' size='30' /></td>".
"\n\t<td><input name='price[0]' /></td>".
"\n</tr>";
// End the table
echo "\n</table>";
// Display a submit button and end the form.
echo "\n<input name='update' type='submit' value='Update data' />";
echo "</form>";
}
?>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Jack and Jill's Wedding Gift Registry</title>
</head>
<body bgcolor="LIGHTBLUE">
<?php
// Show a logout link and a link to the main page
echo "<a href='logout.php'>Logout</a> | <a href='list.php'>Gift list</a>";
echo "\n<h3>Gift editing page</h3>";
// Show the existing gifts for editing
showgiftsforedit($connection);
?>
</body>
</html>
Example A-5. index.php
<?php
// index.php: Show the user the login screen for the application, or
// log in a user with correct authentication details.
// Include database parameters and related functions
require_once("db.php");
// Connect to the MySQL DBMS and use the wedding database -
// credentials are in the file db.php
if(!($connection= @ mysqli_connect(
$DB_hostname, $DB_username, $DB_password, $DB_databasename)))
showerror($connection);
// Pre-process the authentication data from the form for security
// and assign the username and password to local variables
if(count($_POST))
{
$username = clean($_POST["username"], 30);
$password = clean($_POST["password"], 30);
}
// Pre-process the message data for security
if(count($_GET))
{
$message = clean($_GET["message"], 128);
}
// If no username or password has been entered, or there's a message
// to display, show the login page
if( empty($username) ||
empty($password) ||
isset($message) )
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Jack and Jill's Wedding Gift Registry</title>
</head>
<body bgcolor='LIGHTBLUE'>
<h2>Jack and Jill's Wedding Gift Registry</h2>
<?php
// If an error message is stored, show it...
if (isset($message))
echo "<h3><font color=\"red\">{$message}</font></h3>";
?>
(if you've not logged in before, make up a username and password)
<form action="<?php echo $_SERVER["PHP_SELF"];?>" method="POST">
<br />Please enter a username:
<input type="text" name="username"
value="<?php if(isset($_POST['username']))
echo $_POST['username'];?>" />
<br />Please enter a password:
<input type="password" name="password" />
<br /><input type="submit" value="Log in">
</form>
<br />
<?php require "disclaimer"; ?>
</body>
</html>
<?php
}
else
{
// Check that the username and password are each at least four
// characters long.
if( (strlen($username)<4) ||
(strlen($password)<4) )
{
// No, they're not; create an error message and redirect
// the browser to the index page to display the message
$message = "Please choose a username and password that are ".
"at least four characters long";
header("Location: index.php?message=" . urlencode($message));
exit;
}
// Create a query to find any rows that match the provided username
$query = "SELECT username, password FROM users WHERE username = '$username'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Were there any matching rows?
if (mysqli_num_rows($result) == 0)
{
// No, so insert the new username and password into the table
$query = "INSERT INTO users SET username = '$username', password='".
crypt($password, substr($username, 0, 2))."'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
}
else
{
// Yes, so check that the supplied password is correct
// Fetch the matching row
// If we don't get exactly one answer, then we have a problem
for($matchedrows=0;($tmprow = @ mysqli_fetch_array($result));$matchedrows++) $row=$tmprow;
if($matchedrows!=1)
die("We've just experienced a technical problem - ".
"please notify the administrator.");
// Does the user-supplied password match the password in the table?
if (crypt($password, substr($username, 0, 2)) != $row["password"])
{
// No, so redirect the browser to the login page with a
// message
$message = "This user exists, but the password is incorrect. ".
"Choose another username, or fix the password.";
header("Location: index.php?message=" . urlencode($message));
exit;
}
}
// Everything went OK. Start a session, store the username in a
// session variable, and redirect the browser to the gift list
// page with a welcome message.
session_start();
$_SESSION['username']=$username;
$message = "Welcome {$_SESSION['username']}! Please select gift suggestions".
" from the list to add to your shopping list!";
header("Location: list.php?message=" . urlencode($message));
exit;
}
?>
Example A-6. license.txt
Source code example for Learning MySQL
Unless otherwise stated, the source code distributed with this book can be
redistributed in source or binary form so long as an acknowledgment appears
in derived source files.
The citation should list that the code comes from
S.M.M. (Saied) Tahaghoghi and Hugh E. Williams,
"Learning MySQL" published by O'Reilly Media.
This code is under copyright and cannot be included in any other book,
publication, or educational product without permission from O'Reilly &
Associates.
No warranty is attached; we cannot take responsibility for errors or fitness
for use.
Example A-7. list.php
<?php
// list.php: Show the user the available gifts and the gifts in
// their shopping list
// Include database parameters and related functions
require_once("db.php");
// 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 or the current user's
// shopping list.
// Define constants for use when calling showgifts
define("SHOW_UNRESERVED_GIFTS", 0);
define("SHOW_GIFTS_RESERVED_BY_THIS_USER", 1);
function showgifts($connection, $show_user_selection)
{
// See whether there are any gifts in the system
$query = "SELECT * FROM gifts";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Check whether any gifts were found
if (@ mysqli_num_rows($result) == 0)
// No; print a notice
echo "\n<h3><font color=\"red\">".
"There are no gifts described in the system!</font></h3>";
else
{
// Yes; display the gifts
// If we're showing the available gifts, then set up
// a query to show all unreserved gifts (where username IS NULL)
if ($show_user_selection == SHOW_UNRESERVED_GIFTS)
$query = "SELECT * FROM gifts WHERE username IS NULL ".
"ORDER BY description";
else
// Otherwise, set up a query to show all gifts reserved by
// this user
$query = "SELECT * FROM gifts WHERE username = '".
$_SESSION['username']."' ORDER BY description";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Did we get back any rows?
if (@ mysqli_num_rows($result) == 0)
{
// No data was returned from the query.
// Show an appropriate message
if ($show_user_selection == SHOW_UNRESERVED_GIFTS)
echo "\n<h3><font color=\"red\">No gifts left!</font></h3>";
else
echo "\n<h3><font color=\"red\">Your Basket is Empty!".
"</font></h3>";
}
else
{
// Yes, so show the gifts as a table
echo "\n<table border=1 width=100%>";
// Create some headings for the table
echo "\n<tr>" .
"\n\t<th>Quantity</th>" .
"\n\t<th>Gift</th>" .
"\n\t<th>Colour</th>" .
"\n\t<th>Available From</th>" .
"\n\t<th>Price</th>" .
"\n\t<th>Action</th>" .
"\n</tr>";
// Fetch each database table row of the results
while($row = @ mysqli_fetch_array($result))
{
// Display the gift data as a table row
echo "\n<tr>" .
"\n\t<td>{$row["quantity"]}</td>" .
"\n\t<td>{$row["description"]}</td>" .
"\n\t<td>{$row["color"]}</td>" .
"\n\t<td>{$row["shop"]}</td>" .
"\n\t<td>{$row["price"]}</td>";
// Are we showing the list of gifts reserved by the
// user?
if ($show_user_selection == SHOW_UNRESERVED_GIFTS)
// No. So set up an embedded link that the user can click
// to add the gift to their shopping list by running
// action.php with action=add
echo "\n\t<td><a href=\"action.php?action=add&" .
"gift_id={$row["gift_id"]}\">".
"Add to Shopping List</a></td>";
else
// 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=remove
echo "\n\t<td><a href=\"action.php?action=remove&" .
"gift_id={$row["gift_id"]}\">".
"Remove from Shopping list</a></td>";
}
echo "\n</table>";
}
}
}
?>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Jack and Jill's Wedding Gift Registry</title>
</head>
<body bgcolor='LIGHTBLUE'>
<?php
// Show a logout link
echo "<a href='logout.php'>Logout</a>";
// Check whether the user is Jack or Jill (username is 'jack' or
// 'jill'); if so, show a link to the gift editing page.
if($_SESSION['username']=="jack" || $_SESSION['username']=="jill")
echo " | <a href='edit.php'>Edit gifts</a>";
// Connect to the MySQL DBMS and use the wedding database -
// credentials are in the file db.php
if(!($connection= @ mysqli_connect(
$DB_hostname, $DB_username, $DB_password, $DB_databasename)))
showerror($connection);
// Pre-process the message data for security
if(count($_GET))
$message = clean($_GET["message"], 128);
// If there's a message to show, output it
if (!empty($message))
echo "\n<h3><font color=\"red\"><em>".
urldecode($message)."</em></font></h3>";
echo "\n<h3>Here are some gift suggestions</h3>";
// Show the gifts that are still unreserved
showgifts($connection, SHOW_UNRESERVED_GIFTS);
echo "\n<h3>Your Shopping List</h3>";
// Show the gifts that have been reserved by this user
showgifts($connection, SHOW_GIFTS_RESERVED_BY_THIS_USER);
?>
</body>
</html>
Example A-8. logout.php
<?php
// Log out of the system by ending the session and load the main
// page
session_start();
session_destroy();
// Redirect to the main page
$message = "Thank you for using this system - you have now logged out.";
header("Location: index.php?message=" . urlencode($message));
?>