19. Example—E-Commerce

In This Chapter

Creating the Database 606

The Administrative Side 612

Creating the Public Template 629

The Product Catalog 633

The Shopping Cart 645

Recording the Orders 654

Review and Pursue 659

In this, the final chapter of the book, you’ll develop one last Web application: an e-commerce site that sells prints of art. Unfortunately, to write and explain a complete application would require a book in itself, and some aspects of e-commerce—like how you handle the money—are particular to each individual site. With these limitations in mind, the focus in this chapter is on the core e-commerce functionality: designing the database, populating a catalog as an administrator, displaying products to the public, creating a shopping cart, and storing orders in a database.

This example includes many concepts that have already been covered: using PHP with MySQL (of course), handling file uploads, using PHP to send images to the Web browser, prepared statements, sessions, etc. This chapter also has one new topic: how to perform MySQL transactions from a PHP script. Along the way you’ll find tons of suggestions for extending the project.

Creating the Database

The e-commerce site in this example will use the simply named ecommerce data-base. I’ll explain each table’s role prior to creating the database in MySQL.

With any type of e-commerce application there are three kinds of data to be stored: the product information (what is being sold), the customer information (who is making purchases), and the order information (what was purchased and by whom). Going through the normalization process (see Chapter 6, “Database Design”), I’ve come up with five tables .

This entity-relationship diagram (ERD) shows how the five tables in the ecommerce database relate to one another.

The first two tables store all of the products being sold. As already stated, the site will be selling artistic prints. The artists table (Table 19.1) stores the information for the artists whose work is being sold. This table contains just a minimum of information (the artists’ first, middle, and last names), but you could easily add the artists’ birth and death dates, other biographical data, and so forth. The prints table (Table 19.2) is the main products table for the site. It stores the print names, prices, and other relevant details. It is linked to the artists table using the artist_id. The prints table is arguably the most important, as it provides a unique identifier for each product being sold. That concept is key to any e-commerce site (without unique identifiers, how would you know what a person bought?).

Table 19.1. The artists Table

Table 19.2. The prints Table

The customers table (Table 19.3) does exactly what you’d expect: it records the personal information for each client. At the least, it reflects the customer’s first name, last name, email address, password, and shipping address, as well as the date they registered. Presumably the combination of the email address and password would allow the user to log in, shop, and access their account. Since it’s fairly obvious what information this table would store, I’ll define it with only the three essential columns for now.

Table 19.3. The customers Table

The final two tables store all of the order information. There are any number of ways you could do this, but I’ve chosen to store general order information—the total, the date, and the customer’s ID—in an orders table (Table 19.4). This table could also have separate columns reflecting the shipping cost, the amount of sales tax, any discounts that applied, and so on. The order_contents table (Table 19.5) will store the actual items that were sold, including the quantity and price. The order_contents table is essentially a middleman, used to intercept the many-to-many relationship between prints and orders (each print can be in multiple orders, and each order can have multiple prints).

Table 19.4. The orders Table

Table 19.5. The order_contents Table

In order to be able to use transactions (in the final script), the two order tables will use the InnoDB storage engine. The others will use MyISAM. See Chapter 6 for more information on the available storage engines (i.e., table types).

To create the database

1. Log in to the mysql client and create the ecommerce database, if it doesn’t already exist.

CREATE DATABASE ecommerce;
USE ecommerce;

For these steps, you can use either the mysql client or another tool like phpMyAdmin. Depending upon your situation, you may also need to establish the character set at this point, in terms of the communications with the database application. You can also establish the default character set and encoding when creating the database (again, see Chapter 6).

If you’re using a hosted site, the hosting company will likely provide a database for you already.

2. Create the artists table :

CREATE TABLE artists (
artist_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) DEFAULT NULL,
middle_name VARCHAR(20) DEFAULT NULL,
last_name VARCHAR(40) NOT NULL,
PRIMARY KEY (artist_id), UNIQUE full_name (last_name, first_name, middle_name)
) ENGINE=MyISAM;

Making the first table.

This table stores just four pieces of information for each artist. Of these, only last_name is required (is defined as NOT NULL), as there are artists who go by a single name (e.g., Christo). I’ve added definitions for the indexes as well. The primary key is the artist_id, and an index is placed on the combination of the last name, first name, and middle name, which may be used in an ORDER BY clause. This index is more specifically a unique index, so that the same artist’s name is not entered multiple times.

3. Create the prints table :

CREATE TABLE prints (
print_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
artist_id INT UNSIGNED NOT NULL,
print_name VARCHAR(60) NOT NULL,
price DECIMAL(6,2) UNSIGNED NOT NULL,
size VARCHAR(60) DEFAULT NULL,
description VARCHAR(255) DEFAULT NULL,
image_name VARCHAR(60) NOT NULL,
PRIMARY KEY (print_id),
INDEX (artist_id), INDEX (print_name),
INDEX (price)
) ENGINE=MyISAM;

Making the second table.

All of the columns in the prints table are required except for the size and description. There are indexes on the artist_id, print_name, and price fields, each of which may be used in queries.

Each print will be associated with one image. The image will be stored on the server using the same name as the print_id. When displaying the image in the Web browser, its original name will be used, so that needs to be stored in this table.

You could add to this table an in_stock or qty_on_hand field, to indicate the availability of products.

4. Create the customers table :

CREATE TABLE customers (
customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(60) NOT NULL,
pass CHAR(40) NOT NULL,
PRIMARY KEY (customer_id),
UNIQUE (email), INDEX login (email, pass)
) ENGINE=MyISAM;

Creating a basic version of the customers table. In a real e-commerce site, you’d need to expand this table to store more information.

This is the code used to create the customers table. You could throw in the other appropriate fields (name, address, phone number, the registration date, etc.). As this chapter won’t be using those values—or user management at all—they are being omitted.

5. Create the orders table :

CREATE TABLE orders (
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) UNSIGNED NOT NULL,
order_date TIMESTAMP,
PRIMARY KEY (order_id),
INDEX (customer_id),
INDEX (order_date)
) ENGINE=InnoDB;

Making the orders table.

All of the orders fields are required, and three indexes have been created. Notice that a foreign key column here, like customer_id, is of the same exact type as its corresponding primary key (customer_id in the customers table). The order_date field will store the date and time an order was entered. Being defined as a TIMESTAMP, it will automatically be given the current value when a record is inserted (for this reason it does not formally need to be declared as NOT NULL).

Finally, because transactions will be used with the orders and order_contents tables, both must use the InnoDB storage engine.

6. Create the order_contents table :

CREATE TABLE order_contents (
oc_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id INT UNSIGNED NOT NULL,
print_id INT UNSIGNED NOT NULL,
quantity TINYINT UNSIGNED NOT NULL DEFAULT 1,
price DECIMAL(6,2) UNSIGNED NOT NULL,
ship_date DATETIME DEFAULT NULL,
PRIMARY KEY (oc_id),
INDEX (order_id),
INDEX (print_id),
INDEX (ship_date)
) ENGINE=InnoDB;

Making the final table for the ecommerce database.

In order to have a normalized database structure, each order is separated into its general information—the customer, the order date, and the total amount—and its specific information—the actual items ordered and in what quantity. This table has foreign keys to the orders and prints tables. The quantity has a set default value of 1. The ship_date is defined as a DATETIME, so that it can have a NULL value, indicating that the item has not yet shipped. Again, this table must use the InnoDB storage engine in order to be part of a transaction.

You may be curious why this table stores the price of an item when that information is already present in the prints table. The reason is simply this: the price of a product may change. The prints table indicates the current price of an item; the order_contents table indicates the price at which an item was purchased.

The Administrative Side

The administration side of an e-commerce application is primarily for the management of the three main components:

• Products (the items being sold)

• Customers

• Orders

In this chapter, you’ll create two scripts for adding products to the catalog. As the products being sold are works of art, one script will be for populating the artists table and a second will be for populating the prints table (which has a foreign key to artists).

Due to space constraints, and the fact that without a payment system, this will not be a complete e-commerce example anyway, the customers and orders aspects cannot be detailed in this chapter. However, customer management is just the same as user management, covered in the previous chapter, and you’ll see many recommendations for administration of the orders.

The first two scripts—and pretty much every script in this chapter—will require a connection to the MySQL database. Instead of writing a new one from scratch, just copy mysqli_connect.php (Script 9.2) from Chapter 9, “Using PHP with MySQL,” to the appropriate directory for this site’s files. Then edit the information so that it connects to a database called ecommerce, using a username/password/hostname combination that has the proper privileges.

Also, the two administration scripts will use prepared statements, introduced in Chapter 13, “Security Methods,” in order to provide you with more experience using them. If you’re confused about the prepared statements syntax or functions, review that chapter.

Adding Artists

The first script to be written simply adds artist records to the artists table. The script presents a form with three inputs ; upon submission, the inputs are validated—but two are optional—and the record is added to the database.

The HTML form for adding artists to the catalog.

Script 19.1. This administration page adds new artist names to the database.

---

1   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML
    1.0 Transitional//EN"
2          "http://www.w3.org/TR/xhtml1/DTD/
           xhtml1-transitional.dtd">
3   <html xmlns="http://www.w3.org/1999/
    xhtml" xml:lang="en" lang="en">
4   <head>
5      <meta http-equiv="content-type"
       content="text/html; charset=utf-8" />
6      <title>Add an Artist</title>
7   </head>
8   <body>
9   <?php # Script 19.1 - add_artist.php
10  // This page allows the administrator to
    add an artist.
11
12  if ($_SERVER['REQUEST_METHOD'] == 'POST')
    { // Handle the form.
13
14     // Validate the first and middle
       names (neither required):
15     $fn = (!empty($_POST['first_name'])) ?
       trim($_POST['first_name']) : NULL;
16     $mn = (!empty($_POST['middle_name'])) ?
       trim($_POST['middle_name']) : NULL;
17
18     // Check for a last_name...
19     if (!empty($_POST['last_name'])) {
20
21        $ln = trim($_POST['last_name']);
22
23        // Add the artist to the database:
24        require ('../../mysqli_connect.
          php'),
25        $q = 'INSERT INTO artists (first_
          name, middle_name, last_name)
          VALUES (?, ?, ?)';
26        $stmt = mysqli_prepare($dbc, $q);
27        mysqli_stmt_bind_param($stmt,
          'sss', $fn, $mn, $ln);
28        mysqli_stmt_execute($stmt);
29
30        // Check the results....
31        if (mysqli_stmt_affected_
          rows($stmt) == 1) {
32           echo '<p>The artist has been
             added.</p>';
33           $_POST = array( );
34        } else { // Error!
35           $error = 'The new artist could not be added to the database!';
36        }
37
38        // Close this prepared statement:
39        mysqli_stmt_close($stmt);
40        mysqli_close($dbc); // Close the database connection.
41
42     } else { // No last name value.
43        $error = 'Please enter the artist's name!';
44     }
45
46  } // End of the submission IF.
47
48  // Check for an error and print it:
49  if (isset($error)) {
50     echo '<h1>Error!</h1>
51     <p style="font-weight: bold; color: #C00">' . $error . ' Please try again.</p>';
52  }
53
54  // Display the form...
55  ?>
56  <h1>Add a Print</h1>
57  <form action="add_artist.php" method="post">
58
59     <fieldset><legend>Fill out the form to add an artist:</legend>
60
61     <p><b>First Name:</b> <input type="text" name="first_name" size="10" maxlength="20"
       value="<?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?>" /></p>
62     <p><b>Middle Name:</b> <input type="text" name="middle_name" size="10" maxlength="20"
       value="<?php if (isset($_POST['middle_name'])) echo $_POST['middle_name']; ?>" /></p>
63     <p><b>Last Name:</b> <input type="text" name="last_name" size="10" maxlength="40" value="<?php
       if (isset($_POST['last_name'])) echo $_POST['last_name']; ?>" /></p>
64
65     </fieldset>
66
67     <div align="center"><input type="submit" name="submit" value="Submit" /></div>
68
69  </form>
70
71  </body>
72  </html>

---

To create add_artist.php

1. Begin a new PHP document, starting with the HTML head, to be named add_artist.php (Script 19.1):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="content-type" content="text/html;
charset=utf-8" />
  <title>Add an Artist</title>
</head>
<body>
<?php # Script 19.1 - add_artist.php

Normally, the administrative side of a site would use a template system (perhaps a different template than the public side), but since this chapter only has two administrative scripts, no template will be developed.

2. Check if the form has been submitted:

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

3. Validate the artist’s first and middle names:

$fn = (!empty($_POST['first_name'])) ? trim($_POST['first_name']) : NULL;
$mn = (!empty($_POST['middle_name'])) ? trim($_POST['middle_name']) : NULL;

The artist’s first and middle names are optional fields, whereas the last name is not (since there are artists referred to by only one name). To validate the first two name inputs, I’ve reduced the amount of code by using the ternary operator (introduced in Chapter 10, “Common Programming Techniques”). The code here is the same as

if (!empty($_POST['first_name'])) {
$fn = trim($_POST['first_name']);
} else {
    $fn = NULL;
}

Because this script will rely upon prepared statements, the values to be used in the query don’t need to be run through mysqli_real_escape_string( ). See Chapter 13 for more on this subject.

4. Validate the artist’s last name:

if (!empty($_POST['last_name'])) {
$ln = trim($_POST['last_name']);

The last name value is required. For more stringent validation, you could use regular expressions. For added security, you could apply the strip_tags( ) function.

5. Include the database connection:

require ('../../mysqli_connect.php'),

The administration folder will be located inside of the main (htdocs) folder and is therefore two directories above the connection script. Keep your directory structure in mind when including files.

The site structure for this Web application. The MySQL connection script and the uploads directory (where images will be stored) are not within the Web directory (they aren’t available via http://).

6. Add the artist to the database:

$q = 'INSERT INTO artists (first_name, middle_name, last_name) VALUES (?, ?, ?)';
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'sss', $fn, $mn, $ln);
mysqli_stmt_execute($stmt);

To add the artist to the database, the query will be something like INSERT INTO artists (first_name, middle_name, last_name) VALUES ('John', 'Singer', 'Sargent') or INSERT INTO artists (first_name, middle_name, last_name) VALUES (NULL, NULL, 'Christo'). The query is run using prepared statements, covered in Chapter 13.

The mysqli_stmt_bind_param( ) function indicates that the query needs three inputs (one for each question mark) of the type: string, string, and string. For questions on any of this, see Chapter 13.

7. Report on the results:

if (mysqli_stmt_affected_rows($stmt) == 1) {
  echo '<p>The artist has been added.</p>';
  $_POST = array( );
} else {
  $error = 'The new artist could not be added to the database!';
}

If executing the prepared statement affected one row, which is to say one row was created, a positive message is displayed . In this case, the $_POST array is also reset, so that the sticky form does not redisplay the artist’s information.

An artist has successfully been added to the database.

If the prepared statement failed, a message is added to the $error variable, to be outputted later in the script. For debugging purposes, you could add other details here, such as the MySQL error.

8. Close the prepared statement and the database connection:

mysqli_stmt_close($stmt);
mysqli_close($dbc);

9. Complete the artist’s last name and submission conditionals:

  } else { // No last name value.
    $error = 'Please enter the artist's name!';
  }
} // End of the submission IF.

If no last name value was submitted, then an error message is assigned to the $error variable.

10. Print the error, if one occurred, and close the PHP block:

if (isset($error)) {
  echo '<h1>Error!</h1>
  <p style="font-weight: bold; color: #C00">' . $error . ' Please try again.</p>';
}
?>

Either of the two errors that could have occurred would be represented by the $error variable. If this variable is set, it can be printed out at this point . The error will be written within some CSS to make it bold and red.

If the artist’s last name is not provided, an error message is displayed. The form is sticky, however, remembering values entered into the other two form inputs.

11. Begin creating the HTML form:

<h1>Add a Print</h1>
<form action="add_artist.php" method="post">
  <fieldset><legend>Fill out the form to add an artist:</legend>

12. Create the inputs for adding a new artist:

<p><b>First Name:</b> <input type="text" name="first_name" size="10" maxlength="20" value="<?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?>" /></p>
<p><b>Middle Name:</b> <input type="text" name="middle_name" size="10" maxlength="20" value="<?php if (isset($_POST['middle_name'])) echo $_POST['middle_name']; ?>" /></p>
<p><b>Last Name:</b> <input type="text" name="last_name" size="10" maxlength="40" value="<?php if (isset($_POST['last_name'])) echo $_POST['last_name']; ?>" /></p>

Each form element is made sticky, in case the form is being displayed again.

13. Complete the HTML form:

  </fieldset>
  <div align="center"><input type="submit" name="submit" value="Submit" /></div>
</form>

14. Complete the HTML page:

</body>
</html>

15. Save the file as add_artist.php.

16. Place add_artist.php in your Web directory (in the administration folder) and test it in your Web browser and .

Don’t forget that you’ll also need to place a mysqli_connect.php script, edited to connect to the ecommerce database, in the correct directory as well.

Adding Prints

The second script to be written is for adding a new product (specifically a print) to the database. The page will allow the administrator to select the artist from the database, upload an image, and enter the details for the print . The image will be stored on the server and the print’s record inserted into the database. This will be one of the more complicated scripts in this chapter, but all of the technology involved has already been covered elsewhere in the book.

The HTML form for adding prints to the catalog.

To create add_print.php

1. Begin a new PHP document, starting with the HTML head, to be named add_print.php (Script 19.2):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>Add a Print</title>
</head>
<body>
<?php # Script 19.2 - add_print.php

Script 19.2. This administration page adds products to the database. It handles a file upload and inserts the new print into the prints table.

---

1   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML
    1.0 Transitional//EN"
2          "http://www.w3.org/TR/xhtml1/DTD/
           xhtml1-transitional.dtd">
3   <html xmlns="http://www.w3.org/1999/
    xhtml" xml:lang="en" lang="en">
4   <head>
5      <meta http-equiv="content-type"
       content="text/html; charset=utf-8" />
6      <title>Add a Print</title>
7   </head>
8   <body>
9   <?php # Script 19.2 - add_print.php
10  // This page allows the administrator to
    add a print (product).
11
12  require ('../../mysqli_connect.php'),
13
14  if ($_SERVER['REQUEST_METHOD'] == 'POST')
    { // Handle the form.
15
16     // Validate the incoming data...
17     $errors = array( );
18
19     // Check for a print name:
20     if (!empty($_POST['print_name'])) {
21        $pn = trim($_POST['print_name']);
22     } else {
23        $errors[] = 'Please enter the print's name!';
24     }
25
26     // Check for an image:
27     if (is_uploaded_file ($_FILES['image']['tmp_name'])) {
28
29        // Create a temporary file name:
30        $temp = '../../uploads/' . md5($_FILES['image']['name']);
31
32        // Move the file over:
33        if (move_uploaded_file($_FILES['image']['tmp_name'], $temp)) {
34
35           echo '<p>The file has been uploaded!</p>';
36
37           // Set the $i variable to the image's name:
38           $i = $_FILES['image']['name'];
39
40        } else { // Couldn't move the file over.
41           $errors[] = 'The file could not be moved.';
42           $temp = $_FILES['image']['tmp_name'];
43        }
44
45     } else { // No uploaded file.
46        $errors[] = 'No file was uploaded.';
47        $temp = NULL;
48     }
49
50     // Check for a size (not required):
51     $s = (!empty($_POST['size'])) ? trim($_POST['size']) : NULL;
52
53     // Check for a price:
54     if (is_numeric($_POST['price']) && ($_POST['price'] > 0)) {
55        $p = (float) $_POST['price'];
56     } else {
57        $errors[] = 'Please enter the print's price!';
58     }
59
60     // Check for a description (not required):
61     $d = (!empty($_POST['description'])) ? trim($_POST['description']) : NULL;
62
63     // Validate the artist...
64     if ( isset($_POST['artist']) && filter_var($_POST['artist'], FILTER_VALIDATE_INT, array('min_
       range' => 1))  ) {
65        $a = $_POST['artist'];
66     } else { // No artist selected.
67        $errors[] = 'Please select the print's artist!';
68     }
69
70     if (empty($errors)) { // If everything's OK.
71
72        // Add the print to the database:
73        $q = 'INSERT INTO prints (artist_id, print_name, price, size, description, image_name)
          VALUES (?, ?, ?, ?, ?, ?)';
74        $stmt = mysqli_prepare($dbc, $q);
75        mysqli_stmt_bind_param($stmt, 'isdsss', $a, $pn, $p, $s, $d, $i);
76        mysqli_stmt_execute($stmt);
77
78        // Check the results...
79        if (mysqli_stmt_affected_rows($stmt) == 1) {
80
81           // Print a message:
82           echo '<p>The print has been added.</p>';
83
84           // Rename the image:
85           $id = mysqli_stmt_insert_id($stmt); // Get the print ID.
86           rename ($temp, "../../uploads/$id");
87
88           // Clear $_POST:
89           $_POST = array( );
90
91        } else { // Error!
92           echo '<p style="font-weight: bold; color: #C00">Your submission could not be processed
             due to a system error.</p>';
93        }
94
95        mysqli_stmt_close($stmt);
96
97    } // End of $errors IF.
98
99    // Delete the uploaded file if it still exists:
100   if ( isset($temp) && file_exists ($temp) && is_file($temp) ) {
101      unlink ($temp);
102   }
103
104 } // End of the submission IF.
105
106 // Check for any errors and print them:
107 if ( !empty($errors) && is_array($errors) ) {
108    echo '<h1>Error!</h1>
109    <p style="font-weight: bold; color: #C00">The following error(s) occurred:<br />';
110    foreach ($errors as $msg) {
111       echo " - $msg<br /> ";
112    }
113    echo 'Please reselect the print image and try again.</p>';
114 }
115
116 // Display the form...
117 ?>
118 <h1>Add a Print</h1>
119 <form enctype="multipart/form-data" action="add_print.php" method="post">
120
121    <input type="hidden" name="MAX_FILE_SIZE" value="524288" />
122
123    <fieldset><legend>Fill out the form to add a print to the catalog:</legend>
124
125    <p><b>Print Name:</b> <input type="text" name="print_name" size="30" maxlength="60"
       value="<?php if (isset($_POST['print_name'])) echo htmlspecialchars($_POST['print_name']); ?>"
       /></p>
126
127    <p><b>Image:</b> <input type="file" name="image" /></p>
128
129    <p><b>Artist:</b>
130    <select name="artist"><option>Select One</option>
131    <?php // Retrieve all the artists and add to the pull-down menu.
132    $q = "SELECT artist_id, CONCAT_WS(' ', first_name, middle_name, last_name) FROM artists ORDER
       BY last_name, first_name ASC";
133    $r = mysqli_query ($dbc, $q);
134    if (mysqli_num_rows($r) > 0) {
135       while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
136          echo "<option value="$row[0]"";
137          // Check for stickyness:
138          if (isset($_POST['existing']) && ($_POST['existing'] == $row[0]) ) echo '
             selected="selected"';
139          echo ">$row[1]</option> ";
140       }
141    } else {
142       echo '<option>Please add a new artist first.</option>';
143    }
144    mysqli_close($dbc); // Close the database connection.
145    ?>
146    </select></p>
147
148    <p><b>Price:</b> <input type="text" name="price" size="10" maxlength="10" value="<?php if
       (isset($_POST['price'])) echo $_POST['price']; ?>" /> <small>Do not include the dollar sign or
       commas.</small></p>
149
150    <p><b>Size:</b> <input type="text" name="size" size="30" maxlength="60" value="<?php if
       (isset($_POST['size'])) echo htmlspecialchars($_POST['size']); ?>" /> (optional)</p>
151
152    <p><b>Description:</b> <textarea name="description" cols="40" rows="5"><?php if (isset($_
       POST['description'])) echo $_POST['description']; ?></textarea> (optional)</p>
153
154    </fieldset>
155
156    <div align="center"><input type="submit" name="submit" value="Submit" /></div>
157
158 </form>
159
160 </body>
161 </html>

---

2. Include the database connection script and check if the form has been submitted:

require ('../../mysqli_connect.php'),
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
  $errors = array( );

Any form validation problems will be added to the $errors array, which is initiated here. (Because this script could have multiple errors, an array is being used; in add_artist.php, only a single error at a time could occur.)

3. Validate the print’s name:

if (!empty($_POST['print_name'])) {
  $pn = trim($_POST['print_name']);
} else {
  $errors[ ] = 'Please enter the print's name!';
}

This is one of the required fields in the prints table and should be checked for a value. Again, because this script will use prepared statements, the values to be used in the query don’t need to be run through mysqli_real_escape_string( ).

If you wanted to be extra careful, you could apply strip_tags( ) here (although if a malicious user has gotten into the administrative area, you’ve got bigger problems).

If no value is entered, an error message is added to the $errors array.

4. Create a temporary name for the uploaded image, if one was selected:

if (is_uploaded_file ($_FILES['image']['tmp_name'])) {
  $temp = '../../uploads/' . md5($_FILES['image']['name']);

When the techniques for handling file uploads with PHP were covered (in Chapter 11, “Web Application Development”), the is_uploaded_file( ) function was mentioned. It returns TRUE if a file was uploaded and FALSE if not. If a file was uploaded, the script will attempt to move the file over to the uploads directory (in Step 5).

There is one other thing happening here: the images will not be stored on the server using their given names (which can be a security concern). Instead, the images will be stored using their associated print ID. However, since that value isn’t yet known (because the print’s record hasn’t been added to the database), a temporary name for this file has to be generated. To do so, the md5( ) function, which returns a 32-character hash, is applied to the image’s original name. That temporary name, along with a relative path to the image’s final destination (relative to this script), are assigned to $temp.

There are improvements you could make in this one area. You could also validate that the image is of the right size and type. To keep an already busy script more manageable, I’ve omitted that here, but see Chapter 11 for the exact code to apply.

5. Move the file to its more permanent destination:

if (move_uploaded_file($_FILES['image']['tmp_name'], $temp))
  { echo '<p>The file has been uploaded!</p>';
  $i = $_FILES['image']['name'];

At this point in the script, the print image will have been moved to its permanent location (the uploads directory) but given a temporary name (to be renamed later). A message is printed indicating the success in doing so. Finally, the $i variable will be assigned the original name of the file (for use later on in the script).

The result if a file was selected for the print’s image and it was successfully uploaded.

6. Complete the image-handling section:

  } else {
    $errors[ ] = 'The file could not be moved.';
  $temp = $_FILES['image'] ['tmp_name'];
  }
} else {
  $errors[ ] = 'No file was uploaded.';
  $temp = NULL;
}

The first else clause applies if the file could not be moved to the destination directory. This should only happen if the path to that directory is not correct or if the proper permissions haven’t been set on the directory . In either case, the $temp variable is assigned the value of the original upload, which is still residing in its temporary location. This is necessary, as unused files will be removed later in the script.

If the uploads directory is not writable by PHP, you’ll see errors like these.

The second else clause applies if no file was uploaded. As the purpose of this site is to sell prints, it’s rather important to actually display what’s being sold. If you wanted, you could add to this error message more details or recommendations as to what type and size of file should be uploaded.

7. Validate the size, price, and description inputs:

$s = (!empty($_POST['size'])) ? trim($_POST['size']) : NULL;
if (is_numeric($_POST['price']) && ($_POST['price'] > 0)) {
  $p = (float) $_POST['price'];
} else {
  $errors[ ] = 'Please enter the print's price!';
}
$d = (!empty($_POST['description'])) ? trim($_POST['description']) : NULL;

The size and description values are optional, but the price is not. As a basic validity test, ensure that the submitted price is a number (it should be a decimal) using the is_numeric( ) function, and that the price is greater than 0 (it’d be bad to sell products at negative prices). If the value is appropriate, it’s typecast as a floating-point number just to be safe. An error message will be added to the array if no price or an invalid price is entered.

8. Validate the artist:

if ( isset($_POST['artist']) && filter_var($_POST['artist'], FILTER_VALIDATE_INT, array('min_range' => 1))  ) {
  $a = $_POST['artist'];
} else { // No artist selected.
  $errors[ ] = 'Please select the print's artist!';
}

To enter the print’s artist, the administrator will use a pull-down menu . The result will be a $_POST['artist'] value that’s a positive integer. Remember that if you’re using an older version of PHP, that does not support the Filter extension, you’ll need to use the is_numeric( ) function, typecasting, and a conditional that checks for a value greater than 0 instead. See Chapter 13 for details.

The administrator can select an existing artist using this drop-down menu.

9. Insert the record into the database:

if (empty($errors)) {
  $q = 'INSERT INTO prints (artist_id, print_name, price, size, description, image_name) VALUES (?, ?, ?, ?, ?, ?)';
  $stmt = mysqli_prepare($dbc, $q);
  mysqli_stmt_bind_param($stmt, 'isdsss', $a, $pn, $p, $s, $d, $i);
  mysqli_stmt_execute($stmt);

If the $errors array is still empty, then all of the validation tests were passed and the print can be added. Using prepared statements again, the query is something like INSERT INTO prints (artist_id, print_name, price, size, description, image_name) VALUES (34, 'The Scream', 25.99, NULL, 'This classic...', 'scream.jpg').

The mysqli_stmt_bind_param( ) function indicates that the query needs six inputs (one for each question mark) of the type: integer, string, double (aka float), string, string, and string. For questions on any of this, see Chapter 13.

10. Confirm the results of the query:

if (mysqli_stmt_affected_rows($stmt) == 1) {
  echo '<p>The print has been added.</p>';
  $id = mysqli_stmt_insert_id($stmt);
  rename ($temp, "../../uploads/$id");
  $_POST = array( );
} else {
  echo '<p style="font-weight: bold; color: #C00">Your submission could not be processed due to a system error.</p>';
}

If the query affected one row, then a message of success is printed in the Web browser . Next, the print ID has to be retrieved so that the associated image can be renamed (it currently is in the uploads folder but under a temporary name), using the rename( ) function. That function takes the file’s current name as its first argument and the file’s new name as its second. The value for the first argument was previously assigned to $temp. The value for the second argument is the path to the uploads directory, plus the print’s ID value, just determined. Note that the file’s new name will not contain a file extension. This may look strange when viewing the actual files on the server, but is not a problem when the files—the images—are served by the public side of the site, as you’ll soon see.

Finally, the $_POST array is cleared so that its values are displayed in the sticky form.

If the query did not affect one row, there’s probably some MySQL error happening and you’ll need to apply the standard debugging techniques to figure out why.

11. Complete the conditionals:

    mysqli_stmt_close($stmt);
  } // End of $errors IF.
  if ( isset($temp) && file_exists ($temp) && is_file($temp) ) {
    unlink ($temp);
  }
} // End of the submission IF.

The first closing brace terminates the check for $errors being empty. In this case, the file on the server should be deleted because it hasn’t been permanently moved and renamed.

12. Print any errors:

if ( !empty($errors) && is_array($errors) ) {
  echo '<h1>Error!</h1>
  <p style="font-weight: bold; color: #C00">The following error(s) occurred:<br />';
  foreach ($errors as $msg) {
    echo " - $msg<br /> ";
  }
  echo 'Please reselect the print image and try again.</p>';
}
?>

All of the errors that occurred would be in the $errors array. These can be printed using a foreach loop . The errors are printed within some CSS to make them bold and red. Also, since a sticky form cannot recall a selected file, the user is reminded to reselect the print image. (My book Effortless E-Commerce with PHP and MySQL has an example script that does recall a previously uploaded file, but the code is somewhat tricky.)

An incompletely filled out form will generate several errors.

13. Begin creating the HTML form:

<h1>Add a Print</h1>
<form enctype="multipart/form-data" action="add_print.php" method="post">
  <input type="hidden" name="MAX_FILE_SIZE" value="524288" />
  <fieldset><legend>Fill out the form to add a print to the catalog:</legend>
  <p><b>Print Name:</b> <input type="text" name="print_name" size="30" maxlength="60" value="<?php if (isset($_POST['print_name'])) echo htmlspecialchars($_POST['print_name']); ?>" /></p>
  <p><b>Image:</b> <input type="file" name="image" /></
p>

Because this form will allow a user to upload a file, it must include the enctype in the form tag and the MAX_FILE_SIZE hidden input. The form will be sticky, thanks to the code in the value attribute of its inputs. Note that you cannot make a file input type sticky.

In case the print’s name, size, or description uses potentially problematic characters, each is run through htmlspecialchars( ), so as not to mess up the value (e.g., the use of quotation marks in the print’s size and description in ).

14. Begin the artist pull-down menu:

<p><b>Artist:</b>
<select
name="artist"><option>Select One</option>

The artist pull-down menu will be dynamically generated from the records stored in the artists table using this PHP code .

15. Retrieve every artist:

<?php
$q = "SELECT artist_id, CONCAT_WS(' ', first_name, middle_name, last_name) FROM artists ORDER BY last_name, first_name ASC";
$r = mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) > 0) {
  while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
    echo "<option value="$row[0]"";
    // Check for stickyness:
    if (isset($_POST['existing']) && ($_POST['existing'] == $row[0]) ) echo ' selected="selected"';
    echo ">$row[1]</option> ";
  }
} else {
  echo '<option>Please add a new artist first.</option>';
}
mysqli_close($dbc); // Close the database connection.
?>
</select></p>

This query retrieves every artist’s name and ID from the database (it doesn’t use prepared statements, as there’s really no need). The MySQL CONCAT_WS( ) function—short for concatenate with separator—is used to retrieve the artist’s entire name as one value. If you are confused by the query’s syntax, run it in the mysql client or other interface to see the results.

If there are no existing artists in the database, there won’t be any options for this pull-down menu, so an indication to add an artist would be made instead.

This otherwise-basic code is complicated by the desire to make the pull-down menu sticky. To make any select menu sticky, you have to add selected="selected" to the proper option. So the code in the while loop checks if $_POST['existing'] is set and, if so, if its value is the same as the current artist ID being added to the menu.

16. Complete the HTML form:

  <p><b>Price:</b> <input type="text" name="price" size="10" maxlength="10" value="<?php if (isset($_POST['price'])) echo $_POST['price']; ?>" /> <small>Do not include the dollar sign or commas.</small></p>
  <p><b>Size:</b> <input type="text" name="size" size="30" maxlength="60" value="<?php if (isset($_POST['size'])) echo htmlspecialchars($_POST['size']); ?>" /> (optional)</p>
  <p><b>Description:</b> <textarea name="description" cols="40" rows="5"><?php if (isset($_POST['description'])) echo $_POST['description']; ?></textarea> (optional)</p>
  </fieldset>
  <div align="center"><input type="submit" name="submit" value="Submit" /></div>
</form>

Particulars about each form element, such as the description being optional or that the price should not contain a dollar sign , help the administrator complete the form correctly.

17. Complete the HTML page:

</body>
</html>

18. Save the file as add_print.php.

19. Create the necessary directories on your server, if you have not already.

This administrative page will require the creation of two new directories. One, which I’ll call admin (see ), will house the administrative files themselves. On a real site, it’d be better to name your administrative directory something less obvious.

The second, uploads, should be placed below the Web document directory and have its privileges changed so that PHP can move files into it. See Chapter 10 for more information on this.

20. Place add_print.php in your Web directory (in the administration folder) and test it in your Web browser.

Script 19.3. The header file creates the initial HTML and begins the PHP session.

---

1   <?php # Script 19.3 - header.html
2   // This page begins the session, the
    HTML page, and the layout table.
3
4   session_start( ); // Start a session.
5   ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML
    1.0 Transitional//EN"
6          "http://www.w3.org/TR/xhtml1/DTD/
           xhtml1-transitional.dtd">
7   <html xmlns="http://www.w3.org/1999/
    xhtml" xml:lang="en" lang="en">
8   <head>
9      <meta http-equiv="content-type"
       content="text/html; charset=utf-8" />
10     <title><?php echo (isset($page_title))
       ? $page_title : 'Welcome!'; ?></title>
11  </head>
12  <body>
13  <table cellspacing="0" cellpadding="0"
    border="0" align="center" width="600">
14     <tr>
15        <td align="center" colspan="3"><img
          src="images/title.jpg" width="600"
          height="61" border="0" alt="title"
          /></td>
16     </tr>
17     <tr>
18        <td><a href="index.php"><img
          src="images/home.jpg" width="200"
          height="39" border="0" alt="home
          page" /></a></td>
19        <td><a href="browse_prints.
          php"><img src="images/prints.jpg"
          width="200" height="39" border="0"
          alt="view the prints" /></a></td>
20        <td><a href="view_cart.php"><img
          src="images/cart.jpg" width="200"
          height="39" border="0" alt="view
          your cart" /></a></td>
21     </tr>
22     <tr>
23        <td align="left" colspan="3"
          bgcolor="#ffffcc"><br />

---

Creating the Public Template

Before getting to the heart of the public side, the requisite HTML header and footer files should be created. I’ll whip through these quickly, since the techniques involved should be familiar territory by this point in the book.

To make header.html

1. Begin a new PHP document in your text editor or IDE, to be named header.html (Script 19.3):

<?php # Script 19.3 - header.html

2. Start the session:

session_start( );

It’s very important that the user’s session be maintained across every page, so the session will be started in the header file. If the session was lost on a single page, then a new session would begin on subsequent pages, and the user’s history—the contents of the shopping cart—would be gone.

3. Create the HTML head:

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="content-type" content="text/html;
charset=utf-8" />
  <title><?php echo (isset($page_title)) ? $page_title : 'Welcome!'; ?></title>
</head>

As with all the other versions of this script, the page’s title will be set as a PHP variable and printed out within the title tags. In case it’s not set before this page is included, a default title is also provided.

4. Create the top row of the table:

<body>
<table cellspacing="0" cellpadding="0" border="0" align="center" width="600">
  <tr>
    <td align="center" colspan="3"><img src="images/title.jpg" width="600" height="61" border="0" alt="title" /></td>
  </tr>
  <tr>
    <td><a href="index.php"><img src="images/home.jpg"
width="200" height="39" border="0" alt="home page" /></a></td>
    <td><a href="browse_prints.php"><img src="images/
prints.jpg" width="200" height="39" border="0" alt="view the prints" /></a></td>
    <td><a href="view_cart.php"> <img src="images/cart.jpg" width="200" height="39" border="0" alt="view your cart" /></a></td>
  </tr>

This layout will use images to create the links for the public pages .

The banner created by the header file.

5. Start the middle row:

<tr>
  <td align="left" colspan="3" bgcolor="#ffffcc"><br />

All of each individual page’s content will go in the middle row; the header file begins this row and the footer file will close it.

6. Save the file as header.html and place it in your Web directory (create an includes folder in which to store it).

Script 19.5. A minimal script for the site’s home page.

---

1   <?php # Script 19.5 - index.php
2   // This is the main page for the site.
3
4   // Set the page title and include the
    HTML header:
5   $page_title = 'Make an Impression!';
6   include ('includes/header.html'),
7   ?>
8
9   <p>Welcome to our site....please use the
    links above...blah, blah, blah.</p>
10  <p>Welcome to our site....please use the
    links above...blah, blah, blah.</p>
11
12  <?php include ('includes/footer.html'),
?>

---

Script 19.4. The footer file closes the HTML, creating a copyright message in the process.

---

1   <!-- Script 19.4 - footer.html -->
2          <br /></td>
3       </tr>
4       <tr>
5          <td align="center" colspan="3" bgcolor="#669966"><font color="#ffffff">&copy; Copyright...</font></td>
6       </tr>
7   </table>
8   </body>
9   </html>

---

To make footer.html

1. Create a new HTML document in your text editor or IDE, to be named footer.html (Script 19.4):

<!-- Script 19.4 - footer.html -->

2. Complete the middle row:

  <br /></td>
</tr>

This completes the table row begun in the header file.

3. Create the bottom row, complete the table, and complete the HTML :

  <tr>
    <td align="center" colspan="3" bgcolor="#669966"><font color="#ffffff">&copy; Copyright...</font></td>
  </tr>
</table>
</body>
</html>

The copyright row created by the footer file.

4. Save the file as footer.html and place it in your Web directory (also in the includes folder).

To make index.php

1. Begin a new PHP document in your text editor or IDE, to be named index.php (Script 19.5).

<?php # Script 19.5 - index.php
$page_title = 'Make an Impression!';
include ('includes/header.html'),
?>

2. Add the page’s content:

<p>Welcome to our site....please use the links above...blah, blah, blah.</p>
<p>Welcome to our site....please use the links above...blah, blah, blah.</p>

Obviously a real e-commerce site would have some actual content on the main page. For example, you could easily display the most recently added prints here (see the second tip).

3. Complete the HTML page:

<?php include ('includes/footer.html'), ?>

4. Save the file as index.php, place it in your Web directory, and test it in your Web browser .

The public home page for the e-commerce site.

The Product Catalog

For customers to be able to purchase products, they’ll need to view them first. To this end, two scripts will present the product catalog. The first, browse_prints.php, will display a list of the available prints . If a particular artist has been selected, only that artist’s work will be shown ; otherwise, every print will be listed.

The current product listing, created by browse_prints.php.

If a particular artist is selected (by clicking on the artist’s name), the page displays works only by that artist.

The second script, view_print.php, will be used to display the information for a single print, including the image . On this page customers will find an Add to Cart link, so that the print may be added to the shopping cart. Because the print’s image is stored outside of the Web root directory, view_print.php will use a separate script—nearly identical to show_image.php from Chapter 11—for the purpose of displaying the image.

The page that displays an individual product.

To make browse_prints.php

1. Begin a new PHP document in your text editor or IDE, to be named browse_prints.php (Script 19.6):

<?php # Script 19.6 - browse_prints.php
$page_title = 'Browse the Prints';
include ('includes/header.html'),
require ('../mysqli_connect.php'),

2. Define the query for selecting every print:

$q = "SELECT artists.artist_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS artist, print_name, price, description, print_id FROM artists, prints WHERE artists.artist_id = prints.artist_id ORDER BY artists.last_name ASC, prints.print_name ASC";

The query is a standard join across the artists and prints tables (to retrieve the artist name information with each print’s information). The first time the page is viewed, every print by every artist will be returned .

The results after running the main browse_prints.php query in the mysql client.

Script 19.6. The browse_prints.php script displays every print in the catalog or every print for a particular artist, depending upon the presence of $_GET['aid'].

---

1   <?php # Script 19.6 - browse_prints.php
2   // This page displays the available prints (products).
3
4   // Set the page title and include the HTML header:
5   $page_title = 'Browse the Prints';
6   include ('includes/header.html'),
7
8   require ('../mysqli_connect.php'),
9
10  // Default query for this page:
11  $q = "SELECT artists.artist_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS artist,
    print_name, price, description, print_id FROM artists, prints WHERE artists.artist_id = prints.
    artist_id ORDER BY artists.last_name ASC, prints.print_name ASC";
12
13  // Are we looking at a particular artist?
14  if (isset($_GET['aid']) && filter_var($_GET['aid'], FILTER_VALIDATE_INT, array('min_range' => 1))  ) {
15     // Overwrite the query:
16     $q = "SELECT artists.artist_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS
       artist, print_name, price, description, print_id FROM artists, prints WHERE artists.artist_
       id=prints.artist_id AND prints.artist_id={$_GET['aid']} ORDER BY prints.print_name";
17  }
18
19  // Create the table head:
20  echo '<table border="0" width="90%" cellspacing="3" cellpadding="3" align="center">
21     <tr>
22        <td align="left" width="20%"><b>Artist</b></td>
23        <td align="left" width="20%"><b>Print Name</b></td>
24        <td align="left" width="40%"><b>Description</b></td>
25        <td align="right" width="20%"><b>Price</b></td>
26     </tr>';
27
28  // Display all the prints, linked to URLs:
29  $r = mysqli_query ($dbc, $q);
30  while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
31
32     // Display each record:
33     echo " <tr>
34        <td align="left"><a href="browse_prints.php?aid={$row['artist_id']}">{$row['artist']}
          </a></td>
35        <td align="left"><a href="view_print.php?pid={$row['print_id']}">{$row['print_name']}</td>
36        <td align="left">{$row['description']}</td>
37        <td align="right">${$row['price']}</td>
38     </tr> ";
39
40  } // End of while loop.
41
42  echo '</table>';
43  mysqli_close($dbc);
44  include ('includes/footer.html'),
45  ?>

---

3. Overwrite the query if an artist ID was passed in the URL:

if (isset($_GET['aid']) && filter_var($_GET['aid'], FILTER_VALIDATE_INT, array('min_range' => 1))  ) {
  $q = "SELECT artists.artist_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS artist, print_name, price, description, print_id FROM artists, prints WHERE artists.artist_id=prints.artist_id AND prints.artist_id={$_GET['aid']} ORDER BY prints.print_name";
}

If a user clicks an artist’s name in the online catalog, the user will be returned back to this page, but now the URL will be, for example, browse_prints.php?aid=6 . In that case, the query is redefined, adding the clause AND prints.artist_id=X, so just that artist’s works are displayed (and the ORDER BY is slightly modified). Hence, the two different roles of this script—showing every print or just those for an individual artist—are handled by variations on the same query, while the rest of the script works the same in either case.

For security purposes, the Filter extension is used to validate the artist ID. If your version of PHP does not support the Filter extension, you’ll need to use typecasting and make sure that the value is a positive integer prior to using it in a query.

4. Create the table head:

echo '<table border="0" width="90%" cellspacing="3" cellpadding="3" align="center">
  <tr>
    <td align="left" width="20%"><b>Artist</b></td>
    <td align="left" width="20%"><b>Print Name</b></td>
    <td align="left" width="40%"><b>Description</b></td>
    <td align="right" width="20%"><b>Price</b></td>
  </tr>';

5. Display every returned record:

$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
  echo " <tr>
    <td align="left"><a href="browse_prints.
php?aid={$row['artist_id']}">{$row['artist']}</a></td>
    <td align="left"><a href="view_print.php?pid={$row['print_id']}">{$row['print_name']}</td>
    <td align="left">{$row['description']}</td>
    <td align="right">${$row['price']}</td>
  </tr> ";
} // End of while loop.

The page should display the artist’s full name, the print name, the description, and the price for each returned record. Further, the artist’s name should be linked back to this page (with the artist’s ID appended to the URL), and the print name should be linked to view_print.php (with the print ID appended to the URL ).

The source code for the page reveals how the artist and print IDs are appended to the links.

This code doesn’t include a call to mysqli_num_rows( ), to confirm that some results were returned prior to fetching them, but you could add that in a live version, just to be safe.

6. Close the table, the database connection, and the HTML page:

echo '</table>';
mysqli_close($dbc);
include ('includes/footer.html'),
?>

7. Save the file as browse_prints.php, place it in your Web directory, and test it in your Web browser ( and ).

To make view_print.php

1. Begin a new PHP document in your text editor or IDE, to be named view_print.php (Script 19.7):

<?php # Script 19.7 - view_print.php

2. Establish a flag variable:

$row = FALSE;

The $row variable will be used to track whether or not a problem occurred on this page. This variable, if everything went right, will store the print information from the database. If a problem occurred, then, at the end of the script, $row will still be FALSE, and the page should indicate an error.

3. Validate that a print ID has been passed to this page:

if (isset($_GET['pid']) && filter_var($_GET['pid'], FILTER_VALIDATE_INT, array('min_range' => 1)) ) {
  $pid = $_GET['pid'];

This script won’t work if it does not receive a valid print ID. This conditional first checks that the page receives a print ID and then checks that the print ID is an integer greater than or equal to 1.

For ease of reference later in the script, the value of $_GET['pid'] is then assigned to $pid.

4. Retrieve the corresponding information from the database:

require ('../mysqli_connect.php'),
$q = "SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS artist, print_name, price, description, size, image_name FROM artists, prints WHERE artists.artist_id=prints.artist_id AND prints.print_id=$pid";
$r = mysqli_query ($dbc, $q);

Script 19.7. The view_print.php script shows the details for a particular print. It also includes a link to add the product to the customer’s shopping cart.

---

1   <?php # Script 19.7 - view_print.php
2   // This page displays the details for a
    particular print.
3
4   $row = FALSE; // Assume nothing!
5
6   if (isset($_GET['pid']) && filter_
    var($_GET['pid'], FILTER_VALIDATE_INT,
    array('min_range' => 1)) ) { // Make
    |sure there's a print ID!
7
8      $pid = $_GET['pid'];
9
10     // Get the print info:
11     require ('../mysqli_connect.php'), //
       Connect to the database.
12     $q = "SELECT CONCAT_WS(' ', first_
       name, middle_name, last_name)
       AS artist, print_name, price,
       description, size, image_name FROM
       artists, prints WHERE artists.artist_
       id=prints.artist_id AND prints.
       print_id=$pid";
13     $r = mysqli_query ($dbc, $q);
14     if (mysqli_num_rows($r) == 1) { //
       Good to go!
15
16        // Fetch the information:
17        $row = mysqli_fetch_array ($r,
          MYSQLI_ASSOC);
18
19        // Start the HTML page:
20        $page_title = $row['print_name'];
21        include ('includes/header.html'),
22
23        // Display a header:
24        echo "<div align="center">
25        <b>{$row['print_name']}</b> by
26        {$row['artist']}<br />";
27
28        // Print the size or a default
          message:
29        echo (is_null($row['size'])) ? '(No
          size information available)' :
          $row['size'];
30
31        echo "<br />${$row['price']}
32        <a href="add_cart.php?pid=$pid">Add to Cart</a>
33        </div><br />";
34
35        // Get the image information and display the image:
36        if ($image = @getimagesize ("../uploads/$pid")) {
37           echo "<div align="center"><img src="show_image.php?image=$pid&name=" . urlencode
             ($row['image_name']) . "" $image[3] alt="{$row['print_name']}" /></div> ";
38        } else {
39           echo "<div align="center">No image available.</div> ";
40        }
41
42        // Add the description or a default message:
43        echo '<p align="center">' . ((is_null($row['description'])) ? '(No description available)' :
          $row['description']) . '</p>';
44
45     } // End of the mysqli_num_rows( ) IF.
46
47     mysqli_close($dbc);
48
49  } // End of $_GET['pid'] IF.
50
51  if (!$row) { // Show an error message.
52     $page_title = 'Error';
53     include ('includes/header.html'),
54     echo '<div align="center">This page has been accessed in error!</div>';
55  }
56
57  // Complete the page:
58  include ('includes/footer.html'),
59  ?>

---

The query is a join like the one in browse_prints.php, but it selects only the information for a particular print .

The result of running the view_print.php query in the mysql client.

5. If a record was returned, retrieve the information, set the page title, and include the HTML header:

if (mysqli_num_rows($r) == 1) {
  $row = mysqli_fetch_array ($r, MYSQLI_ASSOC);
  $page_title = $row['print_name'];
  include ('includes/header.html'),

The browser window’s title will be the name of the print .

6. Begin displaying the print information:

echo "<div align="center">
<b>{$row['print_name']}</b> by
{$row['artist']}<br />";
echo (is_null($row['size'])) ? '(No size information available)' : $row['size'];
echo "<br />${$row['price']}
<a href="add_cart.php?pid=$pid">Add to Cart</a>
</div><br />";

The header for the print will be the print’s name (in bold), followed by the artist’s name, the size of the print, and its price. Finally, a link is displayed giving the customer the option of adding this print to the shopping cart . The shopping cart link is to the add_cart.php script, passing it the print ID.

The print information and a link to buy it are displayed at the top of the page.

Because the print’s size can have a NULL value, the ternary operator is used to print out either the size or a default message.

7. Display the image:

if ($image = @getimagesize ("../uploads/$pid")) {
  echo "<div align="center"><img src="show_image.php?image=$pid&name=" . urlencode($row['image_name']) . "" $image[3] alt="{$row['print_name']}" /></div> ";
} else {
  echo "<div align="center">No image available.</div> ";
}

Because the images are being safely stored outside of the Web root directory, another PHP script is required to provide the image to the browser (the show_image.php script is being used as a proxy script). The name of the image itself is just the print ID, which was already passed to this page in the URL.

This section of the script will first attempt to retrieve the image’s dimensions by using the getimagesize( ) function. If it is successful in doing so, the image itself will be displayed. That process is a little unusual in that the source for the image calls the show_image.php page . The show_image.php script, to be written next, expects the print ID to be passed in the URL, along with the image’s filename (stored in the database when the print is added). This use of a PHP script to display an image is exactly like the use of show_image.php in Chapter 11, only now it’s occurring within another page, not in its own window.

The HTML source of the view_print.php page shows how the src attribute of the img tag calls the show_image.php script, passing it the values it needs.

If the script could not retrieve the image information (because the image is not on the server or no image was uploaded), a message is displayed instead.

8. Display the description:

echo '<p align="center">' . ((is_null($row['description'])) ? '(No description available)' : $row['description']) . '</p>';

Finally, the print’s description is added . A default message will be created if no print description was stored in the database.

The print’s image followed by its description.

9. Complete the two main conditionals:

  } // End of the mysqli_num_rows( ) IF.
  mysqli_close($dbc);
} // End of $_GET['pid'] IF.

10. If a problem occurred, display an error message:

if (!$row) {
  $page_title = 'Error';
  include ('includes/header.html'),
  echo '<div align="center">This page has been accessed in error!</div>';
}

If the print’s information could not be retrieved from the database for whatever reason, then $row is still false and an error should be displayed . Because the HTML header would not have already been included if a problem occurred, it must be included here first.

The view_print.php page, should it not receive a valid print ID in the URL.

11. Complete the page:

include ('includes/footer.html'),
?>

12. Save the file as view_print.php and place it in your Web directory.

If you were to run this page in the browser at this point, no print image would be displayed as show_image.php has not yet been written.

To write show_image.php

1. Begin a new PHP document in your text editor or IDE, to be named show_image.php (Script 19.8):

<?php # Script 19.8 - show_image.php
$image = FALSE;
$name = (!empty($_GET['name'])) ? $_GET['name'] : 'print image';

This script will do the same thing as show_image.php from Chapter 11, except that there are two values being passed to this page. The actual image’s filename on the server will be a number, corresponding to the print ID. The original image’s filename was stored in the database and will be used when sending the image to the Web browser. This page will not contain any HTML, and nothing can be sent to the Web browser prior to this opening PHP tag.

Script 19.8. This script is called by view_print.php (Script 19.7) and displays the image stored in the uploads directory.

---

1   <?php # Script 19.8 - show_image.php
2   // This pages retrieves and shows an
     image.
3
4   // Flag variables:
5   $image = FALSE;
6   $name = (!empty($_GET['name'])) ? $_
    GET['name'] : 'print image';
7
8   // Check for an image value in the URL:
9   if (isset($_GET['image']) && filter_
    var($_GET['image'], FILTER_VALIDATE_INT,
    array('min_range' => 1))  ) {
10
11     // Full image path:
12     $image = '../uploads/' . $_GET['image'];
13
14     // Check that the image exists and is
       a file:
15     if (!file_exists ($image) || (!is_
       file($image))) {
16        $image = FALSE;
17     }
18
19  } // End of $_GET['image'] IF.
20
21  // If there was a problem, use the
    default image:
22  if (!$image) {
23     $image = 'images/unavailable.png';
24     $name = 'unavailable.png';
25  }
26
27  // Get the image information:
28  $info = getimagesize($image);
29  $fs = filesize($image);
30
31  // Send the content information:
32  header ("Content-Type: {$info['mime']} ");
33  header ("Content-Disposition: inline;
    filename="$name" ");
34  header ("Content-Length: $fs ");
35
36  // Send the file:
37  readfile ($image);

---

Two flag variables are initialized here. The first, $image, will refer to the physical image on the server. It’s assumed to be false and needs to be proven otherwise. The $name variable, which will be the name of the file provided to the Web browser, should come from the URL. If not, a default value is assigned.

2. Check for an image value in the URL:

if (isset($_GET['image']) && filter_var($_GET['image'], FILTER_VALIDATE_INT, array('min_range' => 1))  ) {

Before continuing, ensure that the script received an image value, which should be part of the HTML src attribute for each print in view_print.php .

3. Check that the image exists as a file on the server:

$image = '../uploads/' . $_GET['image'];
if (!file_exists ($image) || (!is_file($image))) {
  $image = FALSE;
}

As a security measure, the image’s full path is hard-coded as a combination of ../uploads and the received image name. You could also validate the MIME type (image/jpg, image/gif) of the file here, for extra security.

Next, the script checks that the image exists on the server and that it is a file (as opposed to a directory). If either condition is FALSE, then $image is set to FALSE, indicating a problem.

4. Complete the validation conditional and check for a problem:

} // End of $_GET['image'] IF.
if (!$image) {
  $image = 'images/unavailable.png';
  $name = 'unavailable.png';
}

If the image doesn’t exist, isn’t a file, or if no image name was passed to this script, this condition will be TRUE. In such cases, a default image will be used. For that to happen, however, a fair amount of hacking on the user’s part will have had to take place: the view_print.php script does some preliminary verification of the image, only calling show_image.php if it can access the image.

5. Retrieve the image information:

$info = getimagesize($image);
$fs = filesize($image);

To send the file to the Web browser, the script needs to know the file’s type and size. This code is the same as in Chapter 11.

6. Send the file:

header ("Content-Type: {$info['mime']} ");
header ("Content-Disposition: inline; filename="$name" ");
header ("Content-Length: $fs ");
readfile ($image);

These header( ) calls will send to the Web browser information about the file to follow, exactly as in Chapter 11. To revisit the overall syntax, the first line prepares the browser to receive the file, based upon the MIME type. The second line sets the name of the file being sent.

The last header( ) function indicates how much data is to be expected. The file data itself is sent using the readfile( ) function, which reads in a file and immediately sends the content to the Web browser.

7. Save the file as show_image.php, place it in your Web directory, and test it in your Web browser by viewing any print.

Notice that this page contains no HTML. It only sends an image file to the Web browser. As in Chapter 11, the closing PHP tag is omitted, to avoid potential problems.

Script 19.9. This script adds products to the shopping cart by referencing the product (or print) ID and manipulating the session data.

---

1   <?php # Script 19.9 - add_cart.php
2   // This page adds prints to the shopping
    cart.
3
4   // Set the page title and include the
    HTML header:
5   $page_title = 'Add to Cart';
6   include ('includes/header.html'),
7
8   if (isset ($_GET['pid']) && filter_
    var($_GET['pid'], FILTER_VALIDATE_INT,
    array('min_range' => 1))  ) { // Check
    for a print ID.
9      $pid = $_GET['pid'];
10
11     // Check if the cart already contains
       one of these prints;
12     // If so, increment the quantity:
13     if (isset($_SESSION['cart'][$pid])) {
14
15        $_SESSION['cart'][$pid]
          ['quantity']++; // Add another.
16
17        // Display a message:
18        echo '<p>Another copy of the print
          has been added to your shopping
          cart.</p>';
19
20     } else { // New product to the cart.
21
22        // Get the print's price from the
          database:
23        require ('../mysqli_connect.php'),
          // Connect to the database.
24        $q = "SELECT price FROM prints
          WHERE print_id=$pid";
25        $r = mysqli_query ($dbc, $q);
26        if (mysqli_num_rows($r) == 1) { //
          Valid print ID.
27
28           // Fetch the information.
29           list($price) = mysqli_fetch_
             array ($r, MYSQLI_NUM);
30
31           // Add to the cart:
32           $_SESSION['cart'][$pid] = array
             ('quantity' => 1, 'price' =>
             $price);
33
34           // Display a message:
35           echo '<p>The print has been
             added to your shopping cart.</
             p>';
36
37        } else { // Not a valid print ID.
38           echo '<div align="center">This
             page has been accessed in
             error!</div>';
39        }
40
41        mysqli_close($dbc);
42
43     } // End of isset($_SESSION['cart']
       [$pid] conditional.
44
45  } else { // No print ID.
46     echo '<div align="center">This page
       has been accessed in error!</div>';
47  }
48
49  include ('includes/footer.html'),
50  ?>

---

The Shopping Cart

Once you have created a product catalog, as the preceding pages do, the actual shopping cart itself can be surprisingly simple. The method I’ve chosen to use in this site is to record the product IDs, prices, and quantities in a session. Knowing these three things will allow scripts to calculate totals and do everything else required.

These next two examples will provide all the necessary functionality for the shopping cart. The first script, add_cart.php, is used to add items to the shopping cart. The second, view_cart.php, both displays the contents of the cart and allows the customer to update the cart.

Adding items

The add_cart.php script will take one argument—the ID of the print being purchased—and will use this information to update the cart. The cart itself is stored in a session, accessed through the $_SESSION['cart'] variable. The cart will be a multidimensional array whose keys will be product IDs. The values of the array elements will themselves be arrays: one element for the quantity and another for the price (Table 19.6).

Table 19.6. Sample $_SESSION[‘cart’] Values

To create add_cart.php

1. Begin a new PHP document in your text editor or IDE, to be named add_cart.php (Script 19.9):

<?php # Script 19.9 - add_cart.php
$page_title = 'Add to Cart';
include ('includes/header.html'),

2. Check that a print ID was passed to this script:

if (isset ($_GET['pid']) && filter_var($_GET['pid'], FILTER_VALIDATE_INT, array('min_range' => 1))  ) {
  $pid = $_GET['pid'];

As with the view_print.php script, the script should not proceed if no, or a non-numeric, print ID has been received. If one has been received, then its value is assigned to $pid, to be used later in the script.

3. Determine if a copy of this print had already been added to the cart:

if (isset($_SESSION['cart'][$pid])) {
  $_SESSION['cart'][$pid]['quantity']++;
  echo '<p>Another copy of the print has been added to your shopping cart.</p>';

Before adding the current print to the shopping cart (by setting its quantity to 1), check if a copy is already in the cart. For example, if the customer selected print #519 and then decided to add another, the cart should now contain two copies of the print. The script therefore first checks if the cart has a value for the current print ID. If so, the quantity is incremented. The code $_SESSION['cart'][$pid] ['quantity']++ is the same as

$_SESSION['cart'][$pid]['quantity'] = $_SESSION['cart'][$pid]['quantity'] + 1.

Finally, a message is displayed .

The result after clicking an Add to Cart link for an item that was already present in the shopping cart.

4. If the product is not already in the cart, retrieve its price from the database:

} else { // New product to the cart.
  require ('../mysqli_connect.php'),
  $q = "SELECT price FROM prints WHERE print_id=$pid";
  $r = mysqli_query ($dbc, $q);
  if (mysqli_num_rows($r) == 1) {
    list($price) = mysqli_fetch_array ($r, MYSQLI_NUM);

If the product is not currently in the cart, this else clause comes into play. Here, the print’s price is retrieved from the database using the print ID.

5. Add the new product to the cart:

$_SESSION['cart'][$pid] = array ('quantity' => 1, 'price' => $price);
echo '<p>The print has been added to your shopping cart.</p>';

After retrieving the item’s price, a new element is added to the $_SESSION['cart'] multidimensional array. Since each element in $_SESSION['cart'] is itself an array, use the array( ) function to set the quantity and price. A simple message is then displayed .

The result after adding a new item to the shopping cart.

6. Complete the conditionals:

    } else { // Not a valid print ID.
      echo '<div align="center"> This page has been accessed in error!</div>';
    }
    mysqli_close($dbc);
  } // End of isset($_SESSION ['cart'][$pid] conditional.
} else { // No print ID.
  echo '<div align="center">This page has been accessed in error!</div>';
}

The first else applies if no price could be retrieved from the database, meaning that the submitted print ID is invalid. The second else applies if no print ID, or a non-numeric one, is received by this page. In both cases, an error message will be displayed .

The add_cart.php page will only add an item to the shopping cart if the page received a valid print ID in the URL.

7. Include the HTML footer and complete the PHP page:

include ('includes/footer.html'),
?>

8. Save the file as add_cart.php, place it in your Web directory, and test it in your Web browser (by clicking an Add to Cart link).

Viewing the shopping cart

The view_cart.php script will be more complicated than add_cart.php because it serves two purposes. First, it will display the contents of the cart in detail . Second, it will give the customer the option of updating the cart by changing the quantities of the items therein (or deleting an item by making its quantity 0). To fulfill both roles, the cart’s contents will be displayed in a form that gets submitted back to this same page.

The shopping cart displayed as a form where the specific quantities can be changed.

Finally, this page will link to a checkout.php script, intended as the first step in the checkout process.

To create view_cart.php

1. Begin a new PHP document in your text editor or IDE, to be named view_cart.php (Script 19.10):

<?php # Script 19.10 - view_cart.php
$page_title = 'View Your Shopping Cart';
include ('includes/header.html'),

Script 19.10. The view_cart.php script both displays the contents of the shopping cart and allows the user to update the cart’s contents.

---

1   <?php # Script 19.10 - view_cart.php
2   // This page displays the contents of
    the shopping cart.
3   // This page also lets the user update
    the contents of the cart.
4
5   // Set the page title and include the
    HTML header:
6   $page_title = 'View Your Shopping Cart';
7   include ('includes/header.html'),
8
9   // Check if the form has been submitted
    (to update the cart):
10  if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
11
12     // Change any quantities:
13     foreach ($_POST['qty'] as $k => $v) {
14
15        // Must be integers!
16        $pid = (int) $k;
17        $qty = (int) $v;
18
19        if ( $qty == 0 ) { // Delete.
20           unset ($_SESSION['cart'][$pid]);
21        } elseif ( $qty > 0 ) { // Change
          quantity.
22           $_SESSION['cart'][$pid]
             ['quantity'] = $qty;
23        }
24
25     } // End of FOREACH.
26
27  } // End of SUBMITTED IF.
28
29  // Display the cart if it's not empty...
30  if (!empty($_SESSION['cart'])) {
31
32     // Retrieve all of the information for the prints in the cart:
33     require ('../mysqli_connect.php'), // Connect to the database.
34     $q = "SELECT print_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS artist, print_name
       FROM artists, prints WHERE artists.artist_id = prints.artist_id AND prints.print_id IN (";
35     foreach ($_SESSION['cart'] as $pid => $value) {
36        $q .= $pid . ',';
37     }
38     $q = substr($q, 0, -1) . ') ORDER BY artists.last_name ASC';
39     $r = mysqli_query ($dbc, $q);
40
41     // Create a form and a table:
42     echo '<form action="view_cart.php" method="post">
43     <table border="0" width="90%" cellspacing="3" cellpadding="3" align="center">
44     <tr>
45        <td align="left" width="30%"><b>Artist</b></td>
46        <td align="left" width="30%"><b>Print Name</b></td>
47        <td align="right" width="10%"><b>Price</b></td>
48        <td align="center" width="10%"><b>Qty</b></td>
49        <td align="right" width="10%"><b>Total Price</b></td>
50     </tr>
51     ';
52
53     // Print each item...
54     $total = 0; // Total cost of the order.
55     while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
56
57        // Calculate the total and sub-totals.
58        $subtotal = $_SESSION['cart'][$row['print_id']]['quantity'] * $_SESSION['cart'][$row['print_
          id']]['price'];
59        $total += $subtotal;
60
61        // Print the row:
62        echo " <tr>
63        <td align="left">{$row['artist']}</td>
64        <td align="left">{$row['print_name']}</td>
65        <td align="right">${$_SESSION['cart'][$row['print_id']]['price']}</td>
66        <td align="center"><input type="text" size="3" name="qty[{$row['print_id']}]"
          value="{$_SESSION['cart'][$row['print_id']]['quantity']}" /></td>
67        <td align="right">$" . number_format ($subtotal, 2) . "</td>
68        </tr> ";
69
70     } // End of the WHILE loop.
71
72     mysqli_close($dbc); // Close the
       database connection.
73
74     // Print the total, close the table,
       and the form:
75     echo '<tr>
76        <td colspan="4" align="right">
          <b>Total:</b></td>
77        <td align="right">$' . number_
          format ($total, 2) . '</td>
78     </tr>
79     </table>
80     <div align="center"><input
       type="submit" name="submit"
       value="Update My Cart" /></div>
81     </form><p align="center">Enter a
       quantity of 0 to remove an item.
82     <br /><br /><a href="checkout.
       php">Checkout</a></p>';
83
84  } else {
85     echo '<p>Your cart is currently
       empty.</p>';
86  }
87
88  include ('includes/footer.html'),
89  ?>

---

2. Update the cart if the form has been submitted:

if ($_SERVER['REQUEST_METHOD'] =='POST') {
  foreach ($_POST['qty'] as $k => $v) {
     $pid = (int) $k;
     $qty = (int) $v;
     if ( $qty == 0 ) {
       unset ($_SESSION['cart'][$pid]);
     } elseif ( $qty > 0 ) {
  $_SESSION['cart'][$pid]['quantity'] = $qty;
     }
  } // End of FOREACH.
} // End of SUBMITTED IF.

If the form has been submitted, then the script needs to update the shopping cart to reflect the entered quantities. These quantities will come in the $_POST['qty'] array, whose index is the print ID and whose value is the new quantity (see for the HTML source code of the form). If the new quantity is 0, then that item should be removed from the cart by unsetting it. If the new quantity is not 0 but is a positive number, then the cart is updated to reflect this.

The HTML source code of the view shopping cart form shows how the quantity fields reflect both the product ID and the quantity of that print in the cart.

If the quantity is not a number greater than or equal to 0, then no change will be made to the cart. This will prevent a user from entering a negative number, creating a negative balance due, and getting a refund!

Alternatively, you could use the Filter extension to validate the print ID and the quantity.

3. If the cart is not empty, create the query to display its contents:

if (!empty($_SESSION['cart'])) {
  require ('../mysqli_connect.php'),
  $q = "SELECT print_id, CONCAT_WS(' ', first_name, middle_name, last_name) AS artist, print_name FROM artists, prints WHERE artists.artist_id = prints.artist_id AND prints.print_id IN (";
  foreach ($_SESSION['cart'] as $pid => $value) {
    $q .= $pid . ',';
  }
  $q = substr($q, 0, -1) . ') ORDER BY artists.last_name ASC';
  $r = mysqli_query ($dbc, $q);

The query is a JOIN similar to one used already in this chapter. It retrieves all the artist and print information for each print in the cart. One addition is the use of the IN SQL clause. Instead of just retrieving the information for one print (as in the view_print.php example), retrieve all the information for every print in the shopping cart. To do so, use a list of print IDs in a query like SELECT... print_id IN (519,42,427).... You can also use SELECT... WHERE print_id=519 OR print_id=42 or print_id=427..., but that’s unnecessarily long-winded.

To generate the IN (519,42,427) part of the query, a for loop adds each print ID plus a comma to $q. To remove the last comma, the substr( ) function is applied, chopping off the last character.

4. Begin the HTML form and create a table:

echo '<form action="view_cart.php" method="post">
<table border="0" width="90%" cellspacing="3" cellpadding="3" align="center">
<tr>
  <td align="left" width="30%"><b>Artist</b></td>
  <td align="left" width="30%"><b>Print Name</b></td>
  <td align="right" width="10%"><b>Price</b></td>
  <td align="center" width="10%"><b>Qty</b></td>
  <td align="right" width="10%"><b>Total Price</b></td>
</tr>
';

5. Retrieve the returned records:

$total = 0;
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
  $subtotal = $_SESSION['cart'][$row['print_id']]['quantity'] * $_SESSION['cart'][$row['print_id']]['price'];
  $total += $subtotal;

When displaying the cart, the page should also calculate the order total, so a $total variable is initialized at 0 first. Then for each returned row (which represents one print), the price of that item is multiplied by the quantity to determine the subtotal (the syntax of this is a bit complex because of the multidimensional $_SESSION['cart'] array). This subtotal is added to the $total variable.

6. Print the returned records:

echo " <tr>
<td align="left">{$row['artist']}</td>
<td align="left">{$row['print_name']}</td>
<td align="right">${$_SESSION['cart'][$row['print_id']]['price']}</td>
<td align="center"><input type="text" size="3" name="qty[{$row['print_id']}]" value="{$_SESSION['cart'][$row['print_id']]['quantity']}" /></td>
<td align="right">$" . number_format ($subtotal, 2) . "</td>
</tr> ";

Each record is printed out as a row in the table, with the quantity displayed as a text input type whose value is preset (based upon the quantity value in the session). The subtotal amount (the quantity times the price) for each item is also formatted and printed.

7. Complete the while loop and close the database connection:

} // End of the WHILE loop.
mysqli_close($dbc);

8. Complete the table and the form:

echo '<tr>
  <td colspan="4" align="right"><b>Total:</b></td>
  <td align="right">$' . number_format ($total, 2) . '</td>
</tr>
</table>
<div align="center"><input type="submit" name="submit" value="Update My Cart" /></div>
</form><p align="center">Enter a quantity of 0 to remove an item.
<br /><br /><a href="checkout.php">Checkout</a></p>';

The order total is displayed in the final row of the table, using the number_format( ) function for formatting. The form also provides instructions to the user on how to remove an item, and a link to the checkout page is included.

9. Finish the main conditional and the PHP page:

} else {
  echo '<p>Your cart is currently empty.</p>';
}
include ('includes/footer.html'),
?>

This else clause completes the if (!empty($_SESSION['cart'])) { conditional.

10. Save the file as view_cart.php, place it in your Web directory, and test it in your Web browser ( and ).

Changes made to any quantities update the shopping cart and order total after clicking Update My Cart (compare with ).

Remove everything in the shopping cart by setting the quantities to 0.

Recording the Orders

After displaying all the products as a catalog, and after the user has filled the shopping cart, there are three final steps:

• Checking the user out

• Recording the order in the database

• Fulfilling the order

Ironically, the most important part—checking out (i.e., taking the customer’s money)—could not be adequately demonstrated in a book, as it’s so particular to each individual site. So what I’ve done instead is given an overview of that process in the sidebar. My book Effortless E-Commerce with PHP and MySQL, by contrast, specifically shows how to use two different payment gateways, but doing so requires two full chapters on their own.

Similarly, the act of fulfilling the order is beyond the scope of the book. For physical products, this means that the order will need to be packaged and shipped. Then the order in the database would be marked as shipped by indicating the shipping date. This concept shouldn’t be too hard for you to implement, but you do also have to pay attention to managing the store’s inventory. For virtual products, order fulfillment is a matter of providing the user with the digital content.

So those are some of the issues that cannot be well covered in this chapter; what I can properly demonstrate in this chapter is how the order information would be stored in the database. To ensure that the order is completely and correctly entered into both the orders and order_contents tables, the script will use transactions. This subject was introduced in Chapter 7, “Advanced MySQL,” using the mysql client. Here the transactions will be performed through a PHP script. For added security and performance, this script will also make use of prepared statements, discussed in Chapter 13.

Script 19.11. The final script in the e-commerce application records the order information in the database. It uses transactions to ensure that the whole order gets submitted properly.

---

1   <?php # Script 19.11 - checkout.php
2   // This page inserts the order
    information into the table.
3   // This page would come after the
    billing process.
4   // This page assumes that the billing
    process worked (the money has been
    taken).
5
6   // Set the page title and include the
    HTML header:
7   $page_title = 'Order Confirmation';
8   include ('includes/header.html'),
9
10  // Assume that the customer is logged
    in and that this page has access to the
    customer's ID:
11  $cid = 1; // Temporary.
12
13  // Assume that this page receives the
    order total:
14  $total = 178.93; // Temporary.
15
16  require ('../mysqli_connect.php'), //
    Connect to the database.
17
18  // Turn autocommit off:
19  mysqli_autocommit($dbc, FALSE);
20
21  // Add the order to the orders table...
22  $q = "INSERT INTO orders (customer_id,
    total) VALUES ($cid, $total)";
23  $r = mysqli_query($dbc, $q);
24  if (mysqli_affected_rows($dbc) = = 1) {
25
26     // Need the order ID:
27     $oid = mysqli_insert_id($dbc);
28
29     // Insert the specific order contents
       into the database...
30
31     // Prepare the query:
32     $q = "INSERT INTO order_contents
       (order_id, print_id, quantity, price)
       VALUES (?, ?, ?, ?)";
33     $stmt = mysqli_prepare($dbc, $q);
34     mysqli_stmt_bind_param($stmt, 'iiid',
       $oid, $pid, $qty, $price);
35
36     // Execute each query; count the
       total affected:
37     $affected = 0;
38     foreach ($_SESSION['cart'] as $pid =>
       $item) {
39        $qty = $item['quantity'];
40        $price = $item['price'];
41        mysqli_stmt_execute($stmt);
42        $affected += mysqli_stmt_
          affected_rows($stmt);
43     }
44
45     // Close this prepared statement:
46     mysqli_stmt_close($stmt);
47
48     // Report on the success....
49     if ($affected = = count($_
       SESSION['cart'])) { // Whohoo!
50
51         // Commit the transaction:
52         mysqli_commit($dbc);
53
54        // Clear the cart:
55        unset($_SESSION['cart']);
56
57        // Message to the customer:
58        echo '<p>Thank you for your order.
          You will be notified when the
          items ship.</p>';
59
60        // Send emails and do whatever
else.
61
62     } else { // Rollback and report the
       problem.
63
64        mysqli_rollback($dbc);
65
66        echo '<p>Your order could not be
          processed due to a system error.
          You will be contacted in order
          to have the problem fixed. We
          apologize for the inconvenience.</
          p>';
67        // Send the order information to
the administrator.
68
69     }
70
71  } else { // Rollback and report the
    problem.
72
73     mysqli_rollback($dbc);
74
75     echo '<p>Your order could not be
       processed due to a system error. You
       will be contacted in order to have
       the problem fixed. We apologize for
       the inconvenience.</p>';
76
77     // Send the order information to the
       administrator.
78
79  }
80
81  mysqli_close($dbc);
82
83  include ('includes/footer.html'),
84  ?>

---

This script, checkout.php, represents the final step the customer would see in the e-commerce process. Because the steps that would precede this script have been skipped in this book, a little doctoring of the process is required.

To create checkout.php

1. Begin a new PHP document in your text editor or IDE, to be named checkout.php (Script 19.11):

<?php # Script 19.11 - checkout.php
$page_title = 'Order Confirmation';
include ('includes/header.html'),

2. Create two temporary variables:

$cid = 1;
$total = 178.93;

To enter the orders into the database, this page needs two additional pieces of information: the customer’s identification number (which is the customer_id from the customers table) and the total of the order. The first would presumably be determined when the customer logged in (it would be stored in the session). The second value may also be stored in a session (after tax and shipping are factored in) or may be received by this page from the billing process. But as the script as written in this chapter does not have immediate access to either value (having skipped those steps), let’s create these two variables to fake it.

3. Include the database connection and turn off MySQL’s autocommit mode:

require ('../mysqli_connect.php'),
mysqli_autocommit($dbc, FALSE);

The mysqli_autocommit( ) function can turn MySQL’s autocommit feature on or off. Since transactions will be used to ensure that the entire order is entered properly, the autocommit behavior should be disabled first. If you have any questions about transactions, see Chapter 7 or the MySQL manual.

4. Add the order to the orders table:

$q = "INSERT INTO orders (customer_id, total) VALUES
($cid, $total)";
$r = mysqli_query($dbc, $q);
if (mysqli_affected_rows($dbc) == 1) {

This query is very simple, entering only the customer’s ID number and the total amount of the order into the orders table. The order_date field in the table will automatically be set to the current date and time, as it’s a TIMESTAMP column.

5. Retrieve the order ID:

$oid = mysqli_insert_id($dbc);

The order_id value from the orders table is needed in the order_contents table to relate the two.

6. Prepare the query that inserts the order contents into the database:

$q = "INSERT INTO order_contents (order_id, print_id, quantity, price) VALUES (?, ?, ?, ?)";
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'iiid', $oid, $pid, $qty, $price);

The query itself inserts four values into the order_contents table, where there will be one record for each print purchased in this order. The query is defined, using placeholders for the values, and prepared. The mysqli_stmt_bind_param( ) function associates four variables to the placeholders. Their types, in order, are: integer, integer, integer, double (aka float).

7. Run through the cart, inserting each print into the database:

$affected = 0;
foreach ($_SESSION['cart'] as $pid => $item) {
  $qty = $item['quantity'];
  $price = $item['price'];
  mysqli_stmt_execute($stmt);
  $affected += mysqli_stmt_affected_rows($stmt);
}
mysqli_stmt_close($stmt);

By looping through the shopping cart, as done in view_cart.php, the script can access each item, one at a time. To be clear about what’s happening in this step: the query has already been prepared—sent to MySQL and parsed—and variables have been assigned to the placeholders. Within the loop, two new variables are assigned values that come from the session. Then, the mysqli_stmt_execute( ) function is called, which executes the prepared statement, using the variable values at that moment. The $oid value will not change from iteration to iteration, but $pid, $qty, and $price will.

To confirm the success of all the queries, the number of affected rows must be tracked. A variable, $affected, is initialized to 0 outside of the loop. Within the loop, the number of affected rows is added to this variable after each execution of the prepared statement.

8. Report on the success of the transaction:

if ($affected == count($_SESSION['cart'])) {
  mysqli_commit($dbc);
  unset($_SESSION['cart']);
  echo '<p>Thank you for your order. You will be notified when the items ship.</p>';

The conditional checks to see if as many records were entered into the database as exist in the shopping cart. In short: did each product get inserted into the order_contents table? If so, then the transaction is complete and can be committed. Next, the shopping cart is emptied and the user is thanked. Logically you’d want to send a confirmation email to the customer here as well.

9. Handle any MySQL problems:

  } else {
    mysqli_rollback($dbc);
    echo '<p>Your order could not be processed due to a system error. You will be contacted in order to have the problem fixed. We apologize for the inconvenience.</p>';
  }
} else {
  mysqli_rollback($dbc);
  echo '<p>Your order could not be processed due to a system error. You will be contacted in order to have the problem fixed. We apologize for the inconvenience.</p>';
}

The first else clause applies if the correct number of records were not inserted into the order_contents table. The second else clause applies if the original orders table query fails. In either case, the entire transaction should be undone, so the mysqli_rollback( ) function is called.

If a problem occurs at this point of the process, it’s rather serious because the customer has been charged but no record of the order has made it into the database. This shouldn’t happen, but just in case, you should write all the data to a text file and/or email all of it to the site’s administrator or do something that will create a record of this order. If you don’t, you’ll have some very irate customers on your hands.

10. Complete the page:

mysqli_close($dbc);
include ('includes/footer.html'),
?>

11. Save the file as checkout.php, place it in your Web directory, and test it in your Web browser .

The customer’s order is now complete, after entering all of the data into the database.

You can access this page by clicking the link in view_cart.php.

12. Confirm that the order was properly stored by looking at the database using another interface .

The order in the MySQL database, as viewed using the mysql client.

Review and Pursue

If you have any problems with the review questions or the pursue prompts, turn to the book’s supporting forum (www.LarryUllman.com/forums/).

Note: Some of these questions and prompts rehash information covered earlier in the book, in order to reinforce some of the most important points.

Review

• What kind of storage engine in MySQL supports transactions?

• What types of indexes does MySQL support? What criteria go into deciding what columns should be indexed and what type of index is most appropriate?

• What is a Secure Sockets Layer (SSL)? Why is it important for e-commerce sites? Do you have SSL enabled for your Web site?

• Why isn’t it problematic that the images for the prints are stored without file extensions?

• What steps should you take to debug problems in PHP scripts that interact with a MySQL database?

• What is a multidimensional array?

• What is meant by MySQL’s autocommit feature?

• What does the mysqli_insert_id( ) function do?

• What does it mean to commit or rollback a transaction?

Pursue

• Expand the definition of the artists table to record other information about each artist. Also display this other information on the public side of the site.

• Add a quantity_on_hand column to the prints table. Add a form element to add_print.php so that the administrator can indicate how many copies of the product are initially in stock. On the public side, only offer an “Add to Cart” option for products that are available. When an item is sold (i.e., in checkout.php), reduce each product’s quantity on hand by the number of copies sold.

• Expand the definition of the customers table to include other pertinent information.

• Create registration, login, and logout functionality for customers, using the information from Chapter 18, “Example—User Registration,” as your basis.

• Create the ability for customers to view their accounts, change their passwords, view previous orders, etc.

• Create and use a template for the administrative side of the site.

• Using sessions, restrict access to the administrative side of the site to verified users.

• Apply the code from Chapter 11 to improve the security of add_print.php, so that it properly validates the uploaded file’s type and size.

• Take the dynamically generated artists pull-down menu from add_print.php and use it as a navigational tool on the public side. To do so, set the form’s action attribute to browse_print.php, change the name of the pull-down menu to aid, use the GET method, and when users select an artist and click Submit, he or she will be taken to, for example, browse_print.php?aid=5.

• Apply pagination to browse_prints.php. See Chapter 10 for more on pagination.

• On browse_prints.php, add the option to affect how the prints are displayed by adding links to the column headings (e.g., to browse_prints.php?order=price). Then change the ORDER BY in the queries based upon the presence, and value, of $_GET['order']. Again, this idea was demonstrated in Chapter 9.

• Combine some of the functionality of view_print.php and add_cart.php so that the details of the print just added to the cart are also shown on add_cart.php.

• To show the contents of the cart on add_cart.php, add the applicable code from view_cart.php to the end of add_cart.php.

• Create the ability for the administrator to view the list of orders. Hint: the first page would function like browse_prints.php, except that it would perform a join across orders and customers. This page could display the order total, the order ID, the order date, and the customer’s name. You could link the order ID to a view_order.php script, passing the order ID in the URL. That script would use the order ID to retrieve the details of the order from the order_contents table (joining prints and artists in the process).

• Add the ability to calculate tax and shipping for orders. Store these values in the orders table, too.