Chapter 7: Advanced PHP Techniques: Web-Based Database Administration

The previous chapters were designed to give you a solid foundation for developing applications with PHP. Many dynamic and user-friendly Web sites are simply several basic concepts put together to form one cohesive unit. For example, a successful shopping system may use an underlying product catalog, a method for maintaining the catalog, some sort of order tracking, and a method to order products securely. In this chapter, you'll learn the basics of performing these tasks.

While the term Web-based database administration might seem daunting, don't be afraid-it's just a fancy label for "data goes in, data comes out." The goal of this section is to create a product catalog for an online shopping system, which will be fully functional by the end of this chapter. By breaking down the elements piece by piece, before you know it you'll have created a graphical user interface to a product catalog in a MySQL database. You can repeat the same steps for any type of database-driven system you want to develop: news articles, address books, your mother's recipe collection, whatever you want. If you're not using the MySQL database, just substitute the functions for your particular database for the MySQL database functions.

Planning Your Product Catalog

This sample shopping site will be called XYZ Company, and it will sell books. The first step in creating this site is developing the product catalog. Because we're creating only a simple example for this site, intending only to get the basics down, we'll use one table called MASTER_PRODUCTS. At the end of this chapter, I'll provide pointers for normalizing a set of tables for this type of catalog.

Think about the basic information you'll need to know in order to give the user an accurate description of a product:

  • An ID for the record

  • ISBN (a standard publishing identification number)

  • Book title

  • Author's name

  • Publisher

  • Category

  • Type (hardcover or paperback)

  • A paragraph of information about the book

  • Number of pages in the book

  • Price of the book

You can use the three-step process from Chapter 3, "Working with Databases," to create the MASTER_PRODUCTS table for XYZ Company, or you can manually type a CREATE TABLE command through your MySQL interface of choice (I personally prefer the command line).

This MASTER_PRODUCTS table will have 10 fields, as defined in Table 7.1.

Table 7.1: MASTER_PRODUCTS Table Fields

Field Name

Field Type

Field Length

ID

int

 

ISBN

varchar

25

TITLE

varchar

150

AUTHOR

varchar

75

PUBLISHER

varchar

75

CATEGORY

varchar

50

TYPE

varchar

25

INFO_BLURB

text

 

PAGE_NUM

smallint

 

PRICE

float

 

The ID field will be a primary key, automatically incremented by MySQL when a new record is inserted.

The actual SQL command for the MASTER_PRODUCTS table is as follows:

 CREATE TABLE MASTER_PRODUCTS (        ID int not null primary key auto_increment,        ISBN varchar(25),        TITLE varchar(150),        AUTHOR varchar(75),        PUBLISHER varchar(75),        CATEGORY varchar(50),        TYPE varchar(25),        INFO_BLURB text,        PAGE_NUM smallint,        PRICE float ); 

If you look at the description of the table using the MySQL DESCRIBE command, it looks like this:

 +-------------+---------------+------+-----+---------+-----------------+ | Field       | Type          | Null | Key | Default | Extra           | +-------------+---------------+------+-----+---------+-----------------+ | ID          | int(11)       |      | PRI | NULL    |  auto_increment | | ISBN        | varchar(25)   | YES  |     | NULL    |                 | | TITLE       | varchar(150)  | YES  |     | NULL    |                 | | AUTHOR      | varchar(75)   | YES  |     | NULL    |                 | | PUBLISHER   | varchar(75)   | YES  |     | NULL    |                 | | CATEGORY    | varchar(50)   | YES  |     | NULL    |                 | | TYPE        | varchar(25)   | YES  |     | NULL    |                 | | INFO_BLURB  | text          | YES  |     | NULL    |                 | | PAGE_NUM    | smallint(6)   | YES  |     | NULL    |                 | | PRICE       | float         | YES  |     | NULL    |                 | +-------------+---------------+------+-----+---------+-----------------+ 

In the next sections, you'll create sequences of HTML forms and PHP scripts to add, modify, and delete records in your MASTER_PRODUCTS table.

Developing an Administration Menu

Somewhere on the Web site for XYZ Company, you'll want to have a special series of "admin" pages, which only you (or whomever has the correct password) can access to make changes to the product catalog. The main elements of the administration menu will be as follows:

  • Add a New Product

  • Modify an Existing Product

  • Delete an Existing Product

We'll use a PHP-based authentication scheme, which you learned about in Chapter 5, "User Authentication."

The XYZ Company administration area will be accessible by anyone who knows the username and password pair-in this case, "admin" and "abc123." At the top of every page in the administration sequence, use the following code, which should look very familiar to you if you've read Chapter 5:

 <?php // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||                ($_SERVER[PHP_AUTH_PW] != "abc123")) {                header('WWW-Authenticate: Basic realm="XYZ Company Admin"');                header('HTTP/1.0 401 Unauthorized');                echo 'Authorization Required.';                exit;        } else {                // Display code here.        } } ?> 

If you add this to the top of every page in the XYZ Company administration area, the PHP script will always look for a valid entry for $_SERVER[PHP_AUTH_USER] and $_SERVER[PHP_AUTH_PW]. You will only need to enter the login information the first time you see the pop-up box. From that point forward, the proper values will exist for $_SERVER[PHP_AUTH_USER] and $_SERVER[PHP_AUTH_PW], and will be carried along wherever you go, until you exit your browser. The code comment // Display code here will be replaced by the content you want to display to the valid user. For the first screen (the administration menu), this content can be three bullet items, linking to admin_addrecord.php, admin_modrecord.php, and admin_delrecord.php.

When you put a section of HTML within an echo statement, remember to escape the quotation marks! The entire administration menu code should look something like this:

 <? // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||               ($_SERVER[PHP_AUTH_PW] != "abc123")) {               header('WWW-Authenticate: Basic realm="XYZ Company Admin"');               header('HTTP/1.0 401 Unauthorized');               echo 'Authorization Required.';               exit;        } else {               echo "<HTML>               <HEAD>               <TITLE>XYZ Company Administration Menu</TITLE>               </HEAD>               <BODY>               <h1>XYZ Company Administration Menu</h1>               <p>Select an option:</p>               <ul>               <li><a href=\"admin_addrecord.php\">Add a New Product</a>               <li><a href=\"admin_modrecord.php\">Modify an Existing Product</a>               <li><a href=\"admin_delrecord.php\">Delete an Existing Product</a>               </ul>               </BODY>               </HTML>";        } } ?> 

Save this file and place it on your Web server. Access it at its URL and enter the correct username and password when prompted. If you are authorized, you should see a menu such as the one shown in Figure 7.1.

click to expand
Figure 7.1: XYZ Company administration menu

Now that you've built the menu, it's time to build the pages behind it, starting with "Add a New Product."

Adding Records to the Product Catalog

Of the three menu options, adding a product is the simplest to execute. Using a two-step system, you'll create the addition form, and then you'll create the PHP script to insert the contents into the MASTER_PRODUCTS table. The link in the administration menu says admin_addrecord.php, so create a file with that name and add your PHP authentication code, leaving room for the "show" portion of the form. The "show" portion will begin with the title and main topic heading, followed by the form. The form will contain fields for each of the fields in the MASTER_PRODUCTS table, with the exception of ID.

Note 

In this example, the values of the drop-down menus are hard-coded into the HTML. You could also put these values in separate PUBLISHER, CATEGORY, and TYPE tables, and use PHP to extract the data and dynamically create your drop-down menus.

While not the most aesthetically pleasing form, an example admin_addrecord.php script could look something like this:

 <? // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else        // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||                ($_SERVER[PHP_AUTH_PW] != "abc123")) {                header('WWW-Authenticate: Basic realm="XYZ Company Admin"');                header('HTTP/1.0 401 Unauthorized');                echo 'Authorization Required.';                exit;        } else {                echo "                <HTML>                <HEAD>                <TITLE>XYZ Company: Add a Product</TITLE>                </HEAD>                <BODY>                <h1>Add a product to the XYZ Company Catalog</h1>                <FORM method=\"POST\" action=\"admin_doaddrecord.php\">                <P><strong>ISBN:</strong>                <INPUT type=\"text\" name=\"isbn\" size=35 maxlength=25>                <P><strong>Title:</strong>                <INPUT type=\"text\" name=\"title\" size=35 maxlength=150>                <P><strong>Author:</strong>                <INPUT type=\"text\" name=\"author\" size=35 maxlength=75>                <P><strong>Publisher:</strong>                <SELECT name=\"publisher\">                <OPTION value=\"\">-- Select One --</OPTION>                <OPTION value=\"Premier Press\">Premier Press</OPTION>                <OPTION value=\"Course Technology\">Course Technology</OPTION>                </SELECT>                <strong>Category:</strong>                <SELECT name=\"category\">                <OPTION value=\"\">-- Select One --</OPTION>                <OPTION value=\"Applications\">Applications</OPTION>                <OPTION value=\"Operating Systems\">Operating Systems</OPTION>                (OPTION value=\"Programming\">Programming</OPTION>                </SELECT>                <strong>Type:</strong>                <SELECT name=\"type\">                <OPTION value=\"\">-- Select One --</OPTION>                <OPTION value=\"hardcover\">hardcover</OPTION>                <OPTION value=\"paperback\">paperback</OPTION>                </SELECT>                <P><strong>Description:</strong><br>                <TEXTAREA name=\"info_blurb\" cols=35 rows=3></TEXTAREA>                <P><strong>Page Count:</strong>                <INPUT type=\"text\" name=\"page_num\" size=5 maxlength=5>                <P><strong>Price:</strong>                <INPUT type=\"text\" name=\"price\" size=5 maxlength=5><br>                <p align=center><INPUT type=\"submit\"                value=\"Add New Product\"></p>                </FORM>                </BODY>                </HTML>";        } } ?> 

Place this file on your Web server, and click on the Add a New Product link on the initial administration menu. If you are authorized, you should see a product addition form like that shown in Figure 7.2.

click to expand
Figure 7.2: Add a new product to the XYZ Company product catalog

Next, you'll create the PHP script that takes your form input, creates a proper SQL statement, creates the record, and displays the record to you as a confirmation. It's not as difficult as it sounds. As the form action in admin_addrecord.php is admin_doaddrecord.php, open your favorite text editor and create a file called admin_doaddrecord.php. Add the PHP authentication code, as you've done in the previous scripts. However, in this script, you'll want to do a bit more than just echo HTML back to the browser. First, you'll want to check for some required fields. Then, if all the necessary fields aren't complete, you'll redirect the user to the form.

In this catalog, suppose the required fields are ISBN, book title, and book price. Check that a value has been entered for their matching variable names: $_POST[isbn], $_POST[title], and $_POST[price]:

 if ((!$_POST[isbn]) || (!$_POST[title]) || (!$_POST[price])) {        header("Location: http://www.yourserver.com/admin_addrecord.php");        exit; } 

If your script makes it past the required-field check, the next step is to build the SQL statement used to insert the data into the MASTER_PRODUCTS table. Hold the SQL statement in a variable called $sql, and build the VALUES list using the variable names from the form:

 $sql = "INSERT INTO MASTER_PRODUCTS VALUES ('', '$_POST[isbn]', '$_POST[title]', '$_POST[author]', '$_POST[publisher]', '$_POST[category]', '$_POST[type]', '$_POST[info_blurb]', '$_POST[page_num]', '$_POST[price]')"; 

Use the basic connection code described in Chapter 3, "Working with Databases," to connect to and query the MySQL database using the SQL statement above. Upon success, echo a copy of the record to the screen, just for validation. If an error occurs, the die() function will let you know where!

The admin_doaddrecord.php script could look something like this (substitute your own database connectivity code):

 <? if ((!$_POST[isbn]) || (!$_POST[title]) || (!$_POST[price])) {        header("Location: http://www.yourserver.com/admin_addrecord.php");        exit; } // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else        // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||                ($_SERVER[PHP_AUTH_PW] != "abc123")) {                header('WWW-Authenticate: Basic realm="XYZ Company Admin"');                 header('HTTP/1.0 401 Unauthorized');                echo 'Authorization Required.';                exit;        } else {                // create connection; substitute your own information!                $conn = mysql_connect("localhost","joeuser","34Nhjp")                        or die(mysql_error());                        // select database; substitute your own database name                $db = mysql_select_db("MyDB", $conn) or die(mysql_error());                // formulate and execute the query                $sql = "INSERT INTO MASTER_PRODUCTS VALUES                ('', '$_POST[isbn]', '$_POST[title]', '$_POST[author]',                '$_POST[publisher]',       '$_POST[category]', '$_POST[type]',                '$_POST[info_blurb]', '$_POST[page_num]', '$_POST[price]')";                $result = mysql_query($sql) or die (mysql_error());                if (isset($result)) {                        echo "                        <HTML>                        <HEAD>                        <TITLE>XYZ Company: Add a Product</TITLE>                        </HEAD>                        <BODY>                        <h1>The following was added to the XYZ                        Company Catalog:</h1>                        <P><strong>ISBN:</strong> ".stripslashes($_POST[isbn])."                        <P><strong>Title:</strong>                        ".stripslashes($_POST[title])."                        <P><strong>Author:</strong>                        ".stripslashes($_POST[author])."                        <P><strong>Publisher:</strong>                        ".stripslashes($_POST[publisher])."                        <strong>Category:</strong>                        ".stripslashes($_POST[category])."                        <strong>Type:</strong> ".stripslashes($_POST[type])."                        <P><strong>Description:</strong><br>                        ".stripslashes($_POST[info_blurb])."                        <P><strong>Page Count:</strong>                        ".stripslashes($_POST[page_num])."                        <P><strong>Price:</strong>                        ".stripslashes($_POST[price])."                        <P align=center><a href=\"admin_menu.php\">Return to                        Menu</a></p>                        </BODY>                        </HTML>";                } else {                        echo "Some sort of error has occurred!</p>";                }        } } ?> 

Note the use of the stripslashes() function, used with the text displayed to the user. This function simply removes the slashes that are automatically added to POSTed data, escaping any special characters.

Place this file on your Web server and go back to the form used to add a record. Complete the form to add a product. If the query is successful, you should see a results page like the one shown in Figure 7.3.

click to expand
Figure 7.3: Successful record addition

Use this sequence of forms to continue adding several more products to the MASTER_PRODUCTS table. In the next sections, you'll modify and delete some of these records. In the next chapter, you'll use the MASTER_PRODUCTS table to create a functioning online bookstore.

Modifying Records in the Product Catalog

If you really were an online bookseller, at some point you'd probably want to modify the information in your product catalog. You've already got the link in your administration menu, so let's make it work. Before you modify a product's information, you need to select a single record to work with. So, the first step in the modification sequence is to select a record to modify.

As with just about everything related to programming, there are several ways to display the selection screen. You could display the title of each book in a list, along with a radio button, to select the record you want to edit. If you have a long list of products, you could limit the display to groups of 10 or 15 or some other number and use Next and Previous links to display each group. The goal is to pick one record whose modifiable contents will be displayed in the next step. My sample product catalog has only a few items, so I'm going to populate a drop-down list box with each book's ISBN and title.

Open your text editor and create a file called admin_modrecord.php. As the product selection form is also part of the administration area, add the basic PHP authentication code you used in previous scripts in this chapter. The selection form itself is quite basic: it's just one drop-down list box and a submit button. The trick is to populate that list box with elements in the MASTER_PRODUCTS table. As you'll soon see, it's not that tricky!

All you really need in order to populate the <OPTION> elements of the <SELECT> box are the book's ISBN and title. You can order the results any way you'd like, but I've chosen to order the books alphabetically, in ascending order (numbers first, then A, B, C, and so on). The following code snippet shows the basic connectivity function and query that will perform this task:

 // create connection; substitute your own information! $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); //SQL statement to select the ID, ISBN and title $sql = "SELECT ID, ISBN, TITLE FROM MASTER_PRODUCTS ORDER BY TITLE ASC"; // execute SQL query and get result $sql_result = mysql_query($sql,$connection) or die (mysql_error()); if (!$sql_result) {        echo "<P>Couldn't get list!"; } else {        // Display code here } 

The code comment // Display code here will be replaced by the HTML used to create the product addition form. Start by echoing the title and main topic heading:

 echo  " <HTML> <HEAD> <TITLE>XYZ Company: Modify a Product</TITLE> </HEAD> <BODY> <h1>Select a Product from the XYZ Company Catalog</h1> 

Assume that the PHP script for the next step is called admin_showmodrecord.php and add the following file name to the form action:

 <FORM method=\"POST\" action=\"admin_showmodrecord.php\"> 

Next, start the HTML for the drop-down list box. Go so far as to open the <SELECT> tag, which will have a name of sel_record, and add an empty Select an Item <OPTION> tag:

 <P>Select a Product:</strong><br> <select name=\"sel_record\"> <option value=\"\"> -- Select an Item -- </option>"; 

This first echo statement ends here because the next section of code will dynamically create additional <OPTION> elements based on the number of rows in the MASTER_PRODUCTS table. As a result of the SQL query, the following while loop continues to execute for as long as there are rows waiting for retrieval. For each row that is retrieved, the value of the ID column is assigned to the variable $id, the ISBN column is assigned to the variable $isbn, and the value of the TITLE column is assigned to the variable $title. The loop then creates the <OPTION> element for the product retrieved, placing the value of $id in the value attribute of the <OPTION> field.

 while ($row = mysql_fetch_array($sql_result)) {           $id = $row["ID"];           $isbn = $row["ISBN"];           $title = $row["TITLE"];           echo "<option value=\"$id\">$title (ISBN: $isbn)</option>"; } 

After the while loop runs its course, begin another echo statement and print the rest of the page, closing all open tags and sticking in a form submission button where appropriate. That's all that's involved in dynamically populating a <SELECT> list as part of a form. The entire admin_addrecord.php script could look something like this:

 <? // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {           // If empty, send header causing dialog box to appear           header('WWW-Authenticate: Basic realm="XYZ Company Admin"');           header('HTTP/1.0 401 Unauthorized');           echo 'Authorization Required.';           exit; } else {          // If not empty, do something else          // Try to validate against hard-coded values          if (($_SERVER[PHP_AUTH_USER] != "admin") ||                  ($_SERVER[PHP_AUTH_PW] != "abc123")) {                  header('WWW-Authenticate: Basic realm="XYZ Company Admin"');                  header('HTTP/1.0 401 Unauthorized'):                  echo 'Authorization Required.';                  exit;          } else {                  // create connection; substitute your own information!                  $conn = mysql_connect("localhost","joeuser","34Nhjp")                          or die(mysql_error());                  // select database; substitute your own database name                  $db = mysql_select_db("MyDB", $conn) or die(mysql_error());                  //SQL statement to select ID. ISBN and title                  $sql = "SELECT ID, ISBN, TITLE FROM MASTER_PRODUCTS                  ORDER BY TITLE ASC";                  // execute SQL query and get result                  $sql_result = mysql_query($sql) or die (mysql_error());                  if (!$sql_result) {                          echo "Something has gone wrong!";                  } else {                          echo "<HTML>                          <HEAD>                          <TITLE>XYZ Company: Modify a Product</TITLE>                          </HEAD>                          <BODY>                          <h1>Select a Product from the XYZ Company Catalog</h1>                          <FORM method=\"POST\" action=\"admin_showmodrecord.php\">                          <p><strong>Select a Product:</strong><br>                          <select name=\"sel_record\">                          <option value-\"\"> -- Select an Item -- </option>";                          while ($row = mysql_fetch_array($sql_result)) {                                  $id = $row["ID"];                                  $isbn = $row["ISBN"];                                  $title = $row["TITLE"];                                  echo "<option value=\"$id\">$title                                  (ISBN: $isbn)</option>";                          }                          echo " </select>                          <P align=center><INPUT type=\"submit\" value=\"Modify                          this Product\"></p>                          </FORM>                          </BODY>                          </HTML>";                  }          } } ?> 

Place this file on your Web server, and click on the Modify an Existing Product link on the initial administration menu. If you are authorized, you should see the product selection form, shown in Figure 7.4.

click to expand
Figure 7.4: Select the product to modify

The next step will create the PHP script used to display the selected record. The information currently in the database will be used to pre-populate the modification form. The modification form is exactly the same as the addition form, so its structure should be familiar to you.

The action of the form in step one is admin_showmodrecord.php, so open your text editor and create a file with that name. As the product modification form is also part of the administration area, add the basic PHP authentication code you used in previous scripts in this chapter. But before any of that, add an if statement that checks for the one required field: $_POST[sel_record]. If a record hasn't been selected, you won't have anything to do with the modification and, therefore, authenticating yourself won't really make a difference.

 if (!$_POST[sel_record]) {        header("Location: http://www.yourcompany.com/admin_modrecord.php");        exit; } 

If your script makes it past the required-field check and you authenticate yourself properly, the next step is to connect to the database and issue the SQL statement used to retrieve the selected record. The goal is to pull all of the information for a record with a matching ISBN, so issue the following SQL statement within your basic connection code:

 $sql = "SELECT * FROM MASTER_PRODUCTS WHERE ID = '$_POST[sel_record]'"; 

After issuing the query and retrieving a result, you'll use the mysql_fetch_array() function to grab all of the data for the selected record and assign meaningful variable names to the columns in the table. These values will go into the HTML form for modifying the product.

The form for modifying a product looks suspiciously like the form for adding a product, only with a different form action and a string holding the current value of the input field in the value attribute of each text field.

Within the form for modifying a product you have some drop-down list boxes. Instead of using multiple if...else statements to determine selected elements in a drop-down list box, we'll put the possible <OPTION> elements in an array and loop through it.

Note 

If the elements existed in a separate table, such as in a normalized database, then the same looping concept would hold true. The difference would be that the array would be obtained from the database and not from a hard-coded entry, as is the case here.

An example admin_showmodrecord.php script could look something like this:

 <? //check for required field if (!$_POST[sel_record]) {        header("Location: http://www.yourcompany.com/admin_modrecord.php");        exit; } // Made it trhough, so authenticate. // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else        // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||               ($_SERVER[PHP_AUTH_PW] != "abc123")) {               header('WWW-Authenticate: Basic realm="XYZ Company Admin"');               header('HTTP/1.0 401 Unauthorized');               echo 'Authorization Required.';               exit;        } else {               // create connection; substitute your own information!               $conn = mysql_connect("localhost","joeuser","34Nhjp")               or die(mysql_error());               // select database; substitute your own database name               $db = mysql_select_db("MyDB", $conn) or die(mysql_error());               //SQL statement to select information               $sql = "SELECT * FROM MASTER_PRODUCTS WHERE               ID = '$_POST[sel_record]'";               // execute SQL query and get result               $sql_result = mysql_query($sql) or die (mysql_error());               if (!$sql_result) {                        echo "Something has gone wrong!";               } else {                        //loop through record and get values                        while ($record = mysql_fetch_array($sql_result)) {                                $id = $record['ID'];                                $isbn = stripslashes($record['ISBN']);                                $title = stripslashes($record['TITLE']);                                $author = stripslashes($record['AUTHOR']);                                $publisher = $record['PUBLISHER'];                                $category = $record['CATEGORY'];                                $type = $record['TYPE'];                                $info_blurb = stripslashes($record['INFO_BLURB']);                                $page_num = stripslashes($record['PAGE_NUM']);                                $price = stripslashes($record['PRICE']);                        }                        echo "<HTML>                        <HEAD>                        <TITLE>XYZ Company: Modify a Product</TITLE>                        </HEAD>                        <BODY>                        <h1>Modify this product from the XYZ Company Catalog</h1>                        <FORM method=\"POST\" action=\"admin_domodrecord.php\">                        <INPUT TYPE=\"hidden\" name=\"id\" value=\"$id\">                        <P><strong>ISBN:</strong>                       <INPUT type=\"text\" name=\"isbn\" value=\"$isbn\"                        size=35 maxlength=25>                       <P><strong>Title:</strong>                       <INPUT type=\"text\" name=\"title\" value=\"$title\"                        size=35 maxlength=150>                       <P><strong>Author:</strong> <INPUT type=\"text\"                       name=\"author\" value=\"$author\" size=35 maxlength=75>                       <P><strong>Publisher:</strong>                       <SELECT name=\"publisher\">                       <OPTION value=\"\">-- Select One --</OPTION>";                       $pub_array = array("Premier Press", "Course Technology");                       foreach ($pub_array as $pub) {                               if ($pub == "$publisher") {                                       echo "<OPTION value=\"$pub\"                                       selected>$pub</OPTION>";                               } else {                                       echo "<OPTION value=\"$pub\">$pub</OPTION>";                               }                       }                       echo "</SELECT>                       <strong>Category:</strong>                       <SELECT name=\"category\">                       <OPTION value=\"\">-- Select One --</OPTION>";                       $cat_array = array("Applications", "Operating Systems",                       "Programming");                       foreach ($cat_array as $cat) {                                if ($cat == "$category") {                                        echo "<OPTION value=\"$cat\"                                        selected>$cat</OPTION>";                                } else {                                        echo "<OPTION                                        value=\"$cat\">$cat</OPTION>";                                }                       }                       echo "</SELECT>                       <strong>Type:</strong>                       <SELECT name=\"type\">                       <OPTION value=\"\">-- Select One --</OPTION>";                       $type_array = array("hardcover", "paperback");                       foreach ($type_array as $book_type) {                               if ($type_array == "$type") {                                       echo "<OPTION value=\"$book_type\"                                       selected>$book_type</OPTION>";                               } else {                                       echo "<OPTION                                       value=\"$book_type\">$book_type</OPTION>";                               }                       }                       echo "</SELECT>                       <P><strong>Description:</strong><br>                       <TEXTAREA name=\"info_blurb\"                       cols=35 rows=3>$info_blurb</TEXTAREA>                       <P><strong>Page Count:</strong> <INPUT type=\"text\"                       name=\"page_num\" value=\"$page_num\" size=5 maxlength=5>                       <P><strong>Price:</strong> <INPUT type=\"text\"                       name=\"price\" value=\"$price\" size=5 maxlength=5><br>                       <p align=center><INPUT type=\"submit\" value=\"Modify                       Product\"></p>                       </FORM>                       </BODY>                       </HTML>";               }        } } ?> 

Place this file on your Web server and choose a product by selecting an option via the admin_modrecord.php form. The modification form should now appear, pre-populating with the proper data from your MASTER_PRODUCTS table. Figure 7.5 shows an example.

click to expand
Figure 7.5: Product information pre-populates the modification form

The final step in the modification sequence is to update the fields in the table with their new values and return a confirmation to the user. This script is nearly identical to the admin_doaddrecord.php script created earlier in this chapter. In fact, just copy admin_doaddrecord.php to admin_domodrecord.php and open it in your text editor. I'll explain the minor changes, and you'll be on your way to the next section.

The first modification is the if statement that checks required fields. The redirection that occurs if a required value is not present should look something like this (adding a check for the value of $_POST[id]):

 if  ((!$_POST[id]) || (!$_POST[isbn]) || (!$_POST[title]) || (!$_POST[price])) {          header("Location: http://www.yourserver.com/admin_addrecord.php");          exit; } 

The next difference is in the SQL statement. The Add Product script uses the INSERT command. If you use the INSERT command in the modification sequence, a second record would be created with the new information. This isn't what you want. You want the original record to be updated with the new information. In this case, you can use the UPDATE command and SET the fields to the new values:

 $sql = "UPDATE MASTER_PRODUCTS SET ISBN = '$_POST[isbn]', TITLE = '$_POST[title]', AUTHOR = '$_POST[author]', PUBLISHER = '$_POST[publisher]', CATEGORY = '$_POST[category]', TYPE = '$_POST[type]', INFO_BLURB = '$_POST[info_blurb]', PAGE_NUM = '$_POST[page_num]', PRICE = '$_POST[price]' where ID = '$_POST[id]'"; 

Note 

Since you have a primary key in the ID field, you could also use the REPLACE command, which will INSERT a record in place of a record with a matching unique field.

The final differences are purely cosmetic. You'll want the page's title and heading to reflect your actions:

 <TITLE>XYZ Company: Modify a Product</TITLE> 

and

 <h1>The new record looks like this:</h1> 

The entire admin_domodrecord.php script could look something like this:

 <? if ((!$_POST[isbn]) || (!$_POST[title]) || (!$_POST[price])) {        header("Location: http://www.yourserver.com/admin_addrecord.php");        exit; } // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else        // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||               ($_SERVER[PHP_AUTH_PW] != "abc123")) {               header('WWW-Authenticate: Basic realm="XYZ Company Admin"');               header('HTTP/1.0 401 Unauthorized');               echo 'Authorization Required.';               exit;        } else {               // create connection; substitute your own information!               $conn = mysql_connect("localhost","joeuser","34Nhjp")                      or die(mysql_error());               // select database; substitute your own database name               $db = mysql_select_db("MyDB", $conn) or die(mysql_error());               // formulate and execute the query               $sql = "UPDATE MASTER_PRODUCTS SET ISBN = '$_POST[isbn]',               TITLE = '$_POST[title]', AUTHOR = '$_POST[author]',               PUBLISHER = '$_POST[publisher]', CATEGORY = '$_POST[category]',               TYPE = '$_POST[type]', INFO_BLURB = '$_POST[info_blurb]',               PAGE_NUM = '$_POST[page_num]', PRICE = '$_POST[price]'               WHERE ID = '$_POST[id]'";               $result = mysql_query($sql) or die (mysql_error());               if (isset($result)) {                      echo "<HTML>                      <HEAD>                      <TITLE>XYZ Company: Modify a Product</TITLE>                      </HEAD>                      <BODY>                      <h1>The new record looks like this:</h1>                      <P><strong>ISBN:</strong> ".stripslashes($_POST[isbn])."                      <P><strong>Title:</strong> ".stripslashes($_POST[title])."                      <P><strong>Author:</strong>                      ".stripslashes($_POST[author])."               <P><strong>Publisher:</strong>                      ".stripslashes($_POST[publisher])."                      <strong>Category:</strong>                      ".stripslashes($_POST[category])."                      <strong>Type:</strong> ".stripslashes($_POST[type])."                      <P><strong>Description:</strong><br>                      ".stripslashes($_POST[info_blurb])."                      <P><strong>Page Count:</strong>                      ".stripslashes($_POST[page_num])."                      <P><strong>Price:</strong> ".stripslashes($_POST[price])."                      <P align=center><a href=\"admin_menu.php\">Return to M                      enu</a></p>                      </BODY>                      </HTML>";                } else {                      echo "Some sort of error has occurred!</p>";                }        } } ?> 

Place this file on your Web server and go back to the form at http://www.yourserver.com/admin_modrecord.php. Select a product to modify, and make some changes to its record. If successful, you should see a verification screen such as the one shown in Figure 7.6.

click to expand
Figure 7.6: Verification of product modifications

Use this sequence of forms to modify any records you need to change in the MASTER_PRODUCTS table. The next section shows you the final step in a basic product administration menu: deleting a record.

Deleting Records from the Product Catalog

During the course of business, products are sometimes discontinued for one reason or another. A good administration menu will account for this by offering a Delete Product option. The following three-step sequence gives you that option in your administration menu.

The first step is to select a product for deletion, using virtually the same code you used to select a product for modification. Copy admin_modrecord.php to admin_delrecord.php, and open it in your text editor. The very basic changes are as follows:

Change the title to

 <TITLE>XYZ Company: Delete a Product</TITLE> 

and change the form action to

 <FORM method=\"POST\" action=\"admin_showdelrecord.php\"> 

and change the button to

 <P align=center><INPUT type=\"submit\" value=\"Select this Product\"></p> 

Those are the only changes you'll need to make, so I won't repeat all that code here. Place this file on your Web server, and click on the Delete an Existing Product link on the initial administration menu. If you are authorized, you should see the product selection form in Figure 7.7, which you've seen before.

click to expand
Figure 7.7: Select an item to delete

The next step in the form sequence will display the selected record as a confirmation before acting on a delete command. The script, admin_showdelrecord.php, is very similar to admin_showmodrecord.php in that it selects the contents of the record and displays them to the user. The difference lies in the fact that the information does not prepopulate a form. Instead, the script just displays the information on the screen.

Copy admin_showmodrecord.php to admin_showdelrecord.php, and open it in your text editor. The first modification is part of the if statement that checks required fields. The redirection that occurs if a required value is not present should be something like this:

 //check for required field if (!$_POST[sel_record]) {        header("Location: http://www.yourcompany.com/admin_delrecord.php");        exit; } 

The next changes are in the HTML. Change the title to

 <TITLE>XYZ Company: Delete a Product</TITLE> 

and the heading to

 <h1>Do you wish to delete this product?</h1> 

and the form action to

 <FORM method=\"POST\" action=\"admin_dodelrecord.php\"> 

Add a hidden field to identify the record to be deleted:

 <input type=\"hidden\" name=\"id\" value=\"$id\"> 

Next, instead of printing a form with input fields, simply print the item headings followed by the values from the database for that particular item. Finish up by changing the form submission button to

 <p align=center><INPUT type=\"submit\" value=\"Delete Product\"></p> 

The entire code for admin_dodelrecord.php should look something like this:

 <? //check for required field if (!$_POST[sel_record]) {        header("Location: http://www.yourcompany.com/admin_delrecord.php");        exit; } // Made it trhough, so authenticate. // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else        // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] !- "admin") ||        ($_SERVER[PHP_AUTH_PW] != "abc123")) {        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // create connection; substitute your own information!        $conn = mysql_connect("localhost","joeuser","34Nhjp")                or die(mysql_error());        // select database; substitute your own database name        $db = mysql_select_db("MyDB", $conn) or die(mysql_error());        //SQL statement to select information        $sql = "SELECT * FROM MASTER_PRODUCTS WHERE                ID = 'S_POST[sel_record]'";        // execute SQL query and get result        $sql_result = mysql_query($sql) or die (mysql_error());        if (!$sql_result) {                echo "Something has gone wrong!";        } else {                //loop through record and get values                while ($record = mysql_fetch_array($sql_result)) {                        $id = $record['ID'];                        $isbn = stripslashes($record['ISBN']);                        $title = stripslashes($record['TITLE']);                        $author = stripslashes($record['AUTHOR']);                        $publisher = $record['PUBLISHER'];                        $category = $record['CATEGORY'];                        $type - $record['TYPE'];                        $info_blurb = stripslashes( $record['INFO_BLURB']);                        $page_num = stripslashes($record['PAGE_NUM']);                        $price = stripslashes($record['PRICE']);                }                echo "<HTML>                <HEAD>                <TITLE>XYZ Company: Delete a Product</TITLE>                </HEAD>                        <BODY>                        <h1>Do you wish to delete this product?</h1>                        <FORM method=\"POST\"                        action=\"admin_dodelrecord. php\">                        <input type=\"hidden\" name=\"id\" value=\"$id\">                        <P><strong>ISBN:</strong> $isbn                        <P><strong>Title:</strong> $title                        <P><strong>Author:</strong> $author                        <P><strong>Publisher:</strong> $publisher                        <strong>Category:</strong> $category                        <strong>Type:</strong> $type                        <P><strong>Description:</strong><br>                        $info_blurb                        <P><strong>Page Count:</strong> $page_num                        <P><strong>Price:</strong> $price                        <p align=center><INPUT type=\"submit\"                        value=\"Delete Product\"></p>                        </FORM>                        </BODY>                        </HTML>";                }        } } ?> 

Place this file on your Web server and choose a product by selecting an option in admin_delrecord.php and clicking on the Select Product button. The confirmation should appear, as shown in Figure 7.8.

click to expand
Figure 7.8: Display product information to verify deletion

The final step in the deletion sequence is to actually issue the DELETE command and return a confirmation to the user. This script, called admin_dodelrecord.php, has all the trappings of other scripts in this series, the only difference being in the SQL statement and the HTML displayed on the screen.

Copy admin_delrecord.php to admin_dodelrecord.php and open it in your text editor. The first modification is to the SQL statement. Instead of using the SELECT command, you'll use DELETE:

 $sql = "DELETE FROM MASTER_PRODUCTS WHERE ID = \"$_POST[id]\""; 

The final difference is the confirmation that's returned to the user. Change the heading to something like this:

 <h1>Product deleted.</h1> 

and remove the form-related code, so that the entire code looks something like this:

 <? // Check to see if $_SERVER[PHP_AUTH_USER] already contains info if (!isset($_SERVER[PHP_AUTH_USER])) {        // If empty, send header causing dialog box to appear        header('WWW-Authenticate: Basic realm="XYZ Company Admin"');        header('HTTP/1.0 401 Unauthorized');        echo 'Authorization Required.';        exit; } else {        // If not empty, do something else        // Try to validate against hard-coded values        if (($_SERVER[PHP_AUTH_USER] != "admin") ||                ($_SERVER[PHP_AUTH_PW] != "abc123")) {                header('WWW-Authenticate: Basic realm="XYZ Company Admin"');                header('HTTP/1.0 401 Unauthorized');                echo 'Authorization Required.';                exit;        } else {                // create connection; substitute your own information!                $conn = mysql_connect("localhost","joeuser","34Nhjp")                or die(mysql_error());                // select database; substitute your own database name                $db = mysql_select_db("MyDB", $conn) or die(mysql_error());                //SQL statement to delete item                $sql = "DELETE FROM MASTER_PRODUCTS WHERE ID = \"$_POST[id]\"";                // execute SQL query and get result                $sql_result = mysql_query($sql) or die (mysql_error());                if (!$sql_result) {                        echo "Something has gone wrong!";                } else {                        echo "<HTML>                        <HEAD>                        <TITLE>XYZ Company: Delete a Product</TITLE>                        </HEAD>                        <BODY>                        <h1>Product deleted.</h1>                        </BODY>                        </HTML>";                }        } } ?> 

Place this file on your Web server and go back to the selection form. Select a product to delete, confirm your action, then press the button to delete the record once and for all. You should see a result screen such as the one shown in Figure 7.9.

click to expand
Figure 7.9: Deletion confirmed!

You now have the ability to perform basic administration tasks within a product catalog. Now you can move on to creating a functioning e-commerce Web site with the catalog.



PHP Essentials
PHP Essentials, 2nd Edition
ISBN: 1931841349
EAN: 2147483647
Year: 2002
Pages: 74

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net