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.phpExample A-2 contains the code in db.phpExample A-3 contains the disclaimer code; Example A-4 contains the code in edit.phpExample A-5 contains the code in index.phpExample A-6 contains the text in license.txtExample 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));

?>