One-Page E-Commerce Via PHP, MySQL

 
Richard H. Nilsson/February 27, 2003

Abstract

Describes a simple structure of re-entrant PHP code with HTTP parameter querying to take advantage of server-caching features of Apache web server to deliver a coherant and rapid e-commerce experience.

Why PHP and MySQL?

It fell upon us that there was a service that drop-ships consumer products directly to buyers, given an address and consumated credit card purchase. I chose to use PHP and MySQL to put up a storefront for the following reasons:

  1. Both PHP and MySQL are free.
  2. PHP is a rapid development environment.
  3. Together, they form a powerful and transparent-to-the-user interactive experience.
  4. Both are ubiquitous on webhosting service sites, whether Linux, UNIX or Windows-based.

Free Software

It is easy to acquire and install MySQL and PHP,  both released and distributed under the GPL, on a number of different operating systems including Linux, my choice for back-end Web development. I downloaded, installed and configured MySQL from its home Website [ http://www.mysql.com/ ] in a matter of minutes. PHP was already installed and integrated with my RedHat 7.3 Apache server, but if you don't have it, you can get it at [http://www.php.net/downloads.php ].

Rapid Development

Setting up a MySQL database consists of deciding what your data structure is and then using simple commands to create the database and its tables. It is beyond the scope of this article to go into details more than to illustrate the truth of the previous sentence. You may issue the following at the command line:
(code 1)

mysql -u root create database ecom_db
mysql -u root ecom_db < Productsdb.dump

Where the plaintext file "Productsdb.dump" contains at least a string of commands to create the database table "Products", its fields and their characteristics and can also contain some data "insert" statements to input some data to work with later. For example, the Productsdb.dump file might be similar to:
(code 2)

CREATE TABLE Products (
Item_No varchar(30) NOT NULL default '',
Brand varchar(30),
Whl_Price decimal(6,2),
Srp decimal(6,2),
Category varchar(30),
Sub_1 varchar(30),
Headline text,
Bullets text,
Image_File varchar(30),
Weight decimal(5,2),
Dimensions varchar(30),
Upc char(12),
PRIMARY KEY  (Item_No),
UNIQUE KEY id (Item_No)
) TYPE=MyISAM;

INSERT INTO Products VALUES ('AW-791','ACOUSTIC RESEARCH',126.99,359.95,
'HEADPHONES','WIRELESS','','Surround Sound Wireless Headphones with 
Dolby(tm) Digital Processor','<li>Creates a surround sound environment 
virtually identical to a movie theater or live music performance
<li>Transmits through walls/floors, up to 300\'<li>40mm titanium speaker 
elements with neodymium magnets<li>Universal 900MHz platform allows use of 
multiple headphones with 1 transmitter<li>Angled, swiveling earcups with 
oversized earpads<li>20Hz-20kHz<li>Uses 3 AAA batteries<li>Includes transmitter, 
optical cable, and rechargeable NiMH batteries<li>Retail: $359.95','AW-791.jpg',
'AcousticResearch.gif','www.recoton.com',45,3.50,'16 x 10.75 x 6.5',
'044102157910');

The exact structure will be dictated by the data provided by your merchandise supplier and how you wish to describe the products on your site. We also created an "Inventory" table containing Item_No and Qty fields, to keep a separate account of each item's inventory status. Our supplier sends a daily email containing this information, and it is a simple matter to automate the update of the Inventory table. You can do this with a shell script containing MySQL commands that executes automatically on receipt of the email or by a crontab entry, or a PHP script you invoke manually.

Scripting the Database

PHP has built-in application programing interfaces (APIs) for several database applications. These APIs consist of reserved-word function calls. First, you have to "connect" to the database, using a login and (usually) password:
(code 3)

<?php
if (!($db = @ mysql_connect($hostName, $userName, $passWord)))
   showError();

if (!( @ mysql_select_db($databaseName,$db)))
   showError();
?>

"showError()" is a function on your page somewhere that displays an appropriate message when an error is detected. In the showError() function, you can decide whether to halt or continue execution of the remainder of the page depending on the severity of the error. The value of "hostName" is usually "localhost", and the userName/passWord are usually your normal username and password that you give to log in. The value of "databaseName"  is the name you gave the database when you created it; in our example the name is "ecom_db". These variables can be set in the PHP code above (code3) on the page, or in a separate file that is "included" using this statement above (code 3):
(code 4)

<?php
include 'ecomdb.inc';
?>

Where the contents of the file 'ecomdb.inc' is:
(code 5)

<?
  $hostName     = "localhost";
  $databaseName = "ecom_db";
  $userName     = "you";
  $passWord     = "yourpass";
?> 

Now that you have a database and tables, you can create forms to enter your data, or use mysql's built-in functions to import data from tab-delimited text files.

Re-Entrant Code Structure

Re-entrant code means using the same page and scripts with an "if -else" tree to perform various operations. If none of the pre-defined operations are indicated, perform a default function. It's un-sophisticated, but it sure works!

For example, let's say you had a function to display a list of products from your database, and the first chunk of data on each line was the product's "Item_No" given as a link that would re-enter the page and display all the database info on that product. You could make the link using the following code to fetch a "row" of information on that item from the database and construct the link from it (read the comments to see what we're doing on each line:
(code 6)

<?php
/////////////////////////////////////////
// Default- list items 'function'
// Put this at the bottom of the page.
// It excecutes if no other function is selected.
// set up the MySQL query to get some data
$query = "SELECT * FROM Products ORDER BY Item_No ASC";
// now issue the query
if (!($result = @ mysql_query ($query, $db)))
    showError();
// begin a table to display the items
echo "<table>\n";
// for as meny items as you have in the db, print them...
for ($rowCounter = 0; 
    ($myrow = @ mysql_fetch_array($result)); 
    $rowCounter++)
{
// print a table row for each item
echo "<tr>";  // start the row
// link the item number to re-enter the page with parameters
printf("<td><a href=\"%s?show=yes&item=%s\">%s</a></td>", 
$PHP_SELF, $myrow["Item_No"]);
// print some information about the item
printf("<td>%s</td><td>%s</td>", $myrow["Headline"], $myrow["Root_Cat"]);
// end the row
echo "</tr>\n";
}  // end of all rows
?>

(Note that this is a compacted version. If you have a LOT of items in the database, this will make a LONG page. You would probably want to list just a few at a time, say, 20 or so, and permit browsing the list by pages. There are several PHP algorithms on the Web for this, so I won't go into them here.)

Notice the "$PHP_SELF" in the code listing. This built-in function/variable is the URL of the current page. Used as a URL in a link href property, it causes the browser to reload the page when the link is clicked. Each "%s" is given the value of the respective variable in the comma-delimited list following at the end of the line.

The result is a link containing an HTTP query:
(code 7)

<a href="index.php?show=yes&item=XB3357">XB3357</a>

This link will reload our page and pass the parameters "show" and "item" to any PHP scripts there. All we have to do is check to see if the parameter variable "item" has a value and "show" is equal to "yes", and execute the code required to display the database information for the item whose Item_No is equal to the value of "item".
(code 8)

<?php
////////////////////////////
// Display an item's info
// if 'item' is present and 'show' is "yes"
if ($item && $show="yes")
{
  // build the query statement
  $result = mysql_query("SELECT * FROM Products WHERE Item_No='$item'",$db);

  // if this item is in the database, display info in a table
  if ($myrow = mysql_fetch_array($result)) 
  {
    // start a table
    echo "<table border=\"0\">\n";

    // display an image of the item above its item_number
    printf("<tr><td rowspan=\"3\"><img src=\"images/%s\" alt=\"%s\">
    <br clear=\"all\">%s</td>\n", 
    $myrow["Image_File"], $myrow["Image_File"], $myrow["Item_No"]);

    // in the next cell, show the manufacturers brand name and other info
    printf("<td><em>Maker:</em> %s</td></tr>\n", $myrow["Brand"]);
    printf("<tr><td><ul>%s<li>Retail: $%s<br /><br /><em>Dimensions:</em>
    <br />%s<br /><em>Weight:</em> %s lb.</ul></td></tr>\n", 
    $myrow["Bullets"], $myrow["Srp"], $myrow["Dimensions"], $myrow["Weight"]);

    /////////////////////////////////////////////
    // Check inventory before allowing purchase
    $check = $myrow["Item_No"];
    $result = mysql_query("SELECT * FROM Inventory WHERE Item_No='$check'",$db);

    if (($myrow2 = mysql_fetch_array($result)) && ($myrow2["Qty"] > 0))
    {
      // Build "Buy" button
      printf("<a class=\"buy\" href=\"http://your.service/add.cfm?userid=8262
      &item=%s&price=%s&wt=%s">Buy</a></ul></td></tr>", $myrow["Item_No"], $price, 
      $myrow["Weight"], $PHP_SELF, $myrow["Item_No"]);
    } else {
      // Build "No stock" button
      echo "<a class=\"buy\">Out of Stock</a></ul></td></tr>";
    }
    // end the table
    echo "</table>\n";
  } else {
    echo "<p>So sorry, item " . $item . " was not found.</p>\n";
    echo "<p>Please <a href=\"javascript:history.go(-1);\">go back</a></p>\n";
  }
exit;
}
?>

The "exit;" at the end stops execution of the code after the item's information is displayed. Without it, the browse list would appear below the item display. Notice the construction of a "Buy" button if the item number is found and has a non-zero quantity in another table of the same database called "Inventory". If the quantity in inventory associated with this item_no is 0, the button is labled "Out of Stock" and does not link to your cart processor. The appearance of the button can be defined using a CSS style specification in the head of the document for the class ".buy" in which you tailor the background and border colors to your site's scheme:
(code 9)

<style type="text/css"><!--
.buy {
    font:12px/1.2 helvetica,arial,sans-serif;
    font-weight:700;
    padding:2px 2px;
    background:#933;
    border-top:2px solid #ECC;
    border-left:2px solid #FDD;
    border-right:2px solid #633;
    border-bottom:2px solid #633;
    color:#FFF;
    text-decoration:none;
    }
-->
</style>

With appropriate colors and borders, and with a subclass ".buy:hover",  the button can be quite attractive, highlight when moused-over, and loads faster than graphical buttons would.
(code 10)

.buy:hover { 
    background:#A33;
    color:#FFF;
    }

Add Another Function to the "Tree"

To illustrate another "function catcher", let's show the results of a search. First, we need to input a search term. This is usually done with a small HTML form somewhere on the page, again usually at the top:
(code 11)

<form action="index.php" name="form_one">
<input type="hidden" name="search" value="yes" />
<input type="text" name="term" size="15" />
<a class="nav3" name="submit" 
href="javascript:document.form_one.submit();">Search</a>
</form>

When a form is submitted, a variable called "submit" is automatically created by the presence of the "submit" button, along with variables for each of the named form input elements. In our case we get the variables $submit, $search, and $term.

We can detect the presence of these variables and act accordingly:
(code 12)

<?php
//////////////////////////
// Searching for "$term"
if ( $submit && $term && ($search == yes))
{
  // came looking for somethin'...
  // here's a simple query...
  $query = "SELECT * FROM Products WHERE Headline LIKE '%$term%'";
         :
  [ put your search and display algorithm code here ]
         :
  exit;
}
?>

You can get as elaborate as you like in the search query and display function. Again, the code passage ends in "exit;" to stop processing after the search. To perform another function on the page, you can either generate a link in your item display as we did in the other examples above, or have the user use the "reload" function. Alternatively, you can put a javascript "back" button somewhere in the item display:
(code 13)

echo "<a class=\"button\" href=\"javascript:history.go(-1);\">Go back</a><br />\n";

Conclusion

Forcing a page to re-load using a link HREF and the built-in $PHP_SELF variable with parameters attached, and detecting the presence of these parameters in an "if... else" tree, one HTML/PHP page can serve the functions of an entire e-commerce site. You can be as elaborate as you like, adding functions simply by providing new and unique variables in your HREF URLs.

Copyright 2004 Richard Nilsson. Verbatim copying and redistribution of this entire article are permitted without royalty in any medium provided this notice is preserved.