trovster.com

The personal website of Trevor Morris

E-Commerce Website Report

Introduction

This “Internet 2” module was designed to teach about internet commerce, buzz-word ‘E-Commerce’. The aim was to develop & maintain an e-commerce website using server-side scripting languages such as Perl or PHP: Hypertext Preprocessor (PHP) coupled with databases & the database query lanquage; Structured Query Language (SQL).

The project coursework was to implement a fully working e-commerce website. It was suggested to use a premade piece of commercial ‘shopping-cart’ software. The suggested software is the demonstration web shop from WebWright UK Limited.

I decided this was a good project to improve on my PHP scripting & MySQL database manipulation. I have used both PHP & MySQL before, but only on basic presentational manipulation of data from MySQL databases.

Method

MySQL Basics

The code below is used to connected to the appropriate database. Within each database there can be numerous tables, these are references later using a SQL query. mysql_connect is used to open a connection to a MySQL server. There are three parameters need to establish a connection to the database. These are;

  1. Computer name of the MySQL server,
  2. Username for the database &
  3. Password for the database.

Connecting To The Database

<php
$connect = mysql_connect ("computername", "username", "password");
if(!$connect) {
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($connect);
?>

If the connection is a failure, due to any incorrect information within the parameters, FALSE will be returned. With the code below, an error code will be reported to pinpoint the problem. If the connection is successful then “Connected successfully” is printed out.

The majority of the time the MySQL database will be on the same computer as the webserver your running off. With this scenario the computer name is localhost or the Internet Protocol (IP) address 127.0.0.1, which is a loopback network connection. However for this project I had to connect to the MySQL database on the Linux2 web server. The code to connect to this database is linux2.inf.brad.ac.uk:3306. The appropriate port number was also needed for the connection to be successful.

Below is a very basic SQL query to retrieve data from a MySQL database.

Building Basic SQL Queries

SELECT * FROM tablename
SELECT * FROM tablename ORDER BY rowid ASC
SELECT * FROM tablename ORDER BY rowid ASC LIMIT 10

The first selects all the data from the table specified. The second manipulates the information gathered, by ordering it by a specified field in ascending order. Finally, the query limits how many entries are displayed.

  • id — An identification number for each item. This number is unique and is generated automatically.
  • section — CD, Sticker or Badge. A string of a maximum 64 characters.
  • genre — A string of a maximum 64 characters.
  • artist — Name of artist. A string of a maximum 128 characters.
  • album — Title of album or item. A string of a maximum 128 characters.
  • price — An integer number only.. Default value of “0”.
  • description — A description, used only for CDs. Longtext holds up to a maximum of 4GB characters.
  • label — Record label, only used for CDs.
  • tracks — CD tracks seperated by a comma (,), used only for CDs.
  • image — Image name only, with no extension or directory information.
  • rec — Is the product recommended? Default value of “no”. Other value is “yes”.

Creating the Shopping Cart Database

First of all I designed the database using pen & paper. I then created the database and table using phpMyAdmin2; a database called “internet2” and a table called “catalogue”. I set up 11 rows for the “catalogue” table, these are;

Creating The Database “internet2”

CREATE DATABASE internet2;
USE internet2;

Creating The Table “catalogue”

CREATE TABLE catalogue (
id int(64) NOT NULL auto_increment,
section varchar(64) NOT NULL default '',
genre varchar(64) NOT NULL default '',
artist varchar(128) NOT NULL default '',
album varchar(128) NOT NULL default '',
price float NOT NULL default '0',
description longtext NOT NULL,
label varchar(128) NOT NULL default '',
tracks longtext NOT NULL,
image varchar(128) NOT NULL default '',
rec varchar(8) NOT NULL default 'no',
KEY `id` (`id`)
) TYPE=MyISAM;

Inserting Values Into The Table “catalogue”

INSERT INTO catalogue VALUES (id, 'section', 'genre', 'artist', 'album', 'price',
'description', 'label', 'tracks', 'image', 'rec');

INSERT INTO `catalogue` VALUES (35, 'Badge', 'Power-Punk', 'The Ataris', 'Rainbow',
'1', '', '', '', 'ataris_rainbow', 'yes');

Retrieving Data from the Shopping Cart Database

On the homepage the user is presented with a recommended item from each of the three categories. This is achieved by retriving the data from the row “rec” which has the value of “yes” and row “section” for each section, CD, Sticker & Badge. Secondly, you order the item by random and finally limited the output to one item. The SQL queries for the home page are below.

SQL Queries For The Homepage

SELECT * FROM catalogue WHERE rec='yes' AND section='CD' ORDER BY rand() LIMIT 1
SELECT * FROM catalogue WHERE rec='yes' AND section='Sticker' ORDER BY rand() LIMIT 1
SELECT * FROM catalogue WHERE rec='yes' AND section='Badge' ORDER BY rand() LIMIT 1

The code above simply connects to the database. This information needs to taken from the database, this is done my fetching the data using the SQL query.

Manipulating The Data

// retrieves the data using the sql select.
$row = mysql_fetch_assoc($sql_cd); 

// changes variables from, for example, $row[section] to $section.
extract($row);

// parses the $description through a function & limits characters to 300.
$description = truncate($description,300);

// adds a trailing zero to the price.
$price =  sprintf("%.2f",$price);

// retrieves the height & width for the image.
list($width2, $height2) = getimagesize("images/$section/$image.png");

// scales the image so it will be the right size in "em's".
$height = $height2/20;
$width = $width2/20;

The example above is for manipulating the CD SQL query. Replacing $sqlcd with $sqlbagde or $sql_sticker will format the row variables specific to those queries. $description = truncate($description,300); is not need for these two sections so can be just left out. The code doesn’t output any markup, it just manipulates the data in specific ways. The row variables need to be echoed out to be shown by the browser.

Basic Echoing

// set $variable to equal the string example
$variable = "example"; 

echo "This is an $variable"; // prints out "This is an example".

// using single quotes will print the variable name, not the value.
echo 'This is an $variable'; // prints out "This is an $variable".

// escaping characters is done by using \
echo "A \"quote\" of something"; // prints out "A "quote" of something".
echo 'That\'s a good example'; // prints out "That's a good example".

It is important to be aware of escaping characters especially when dealing with HTML tags, as classes and ids need to be escaped to parse the document.

Using mysql_fetch_assoc($sql_connect) only the first item will be retrieved from the database. To select all the items appropriate to the SQL query, a while loop is needed. Below is the code to retrieve the data & to check whether the SQL query result is empty.

A Basic While Loop

$row = mysql_fetch_assoc($sql_connect);

// loop while fetching items from the sql query.
while($row) {
    extract($row)
    echo "";
}

// checks whether the number of rows is greater the zero.
if (mysql_num_rows($result) > 0) {
    echo 'There are some items in the database;
}
else {
    // accessed if the if statement is false.
    echo 'There was no items found.';
}

The else statement is only executed if the if expression evaluated to FALSE, and if there were any elseif expressions - only if they evaluated to FALSE as well.

PHP.net, 2004

Combining the two pieces of code, nesting one inside the other a simple cart system can be generated. If there isn’t any information relating to that persons cart, then a paragraph can tell the user this, and suggest to them to add items to the cart. If the cart has one or more items inside, then these items can be shown within the if statement. Here is a simple piece of code to illistrating a basic shopping cart combining the code examples above.

Sample Basic Cart Code

// connect to the appropriate database.
$connect = mysql_connect ("computername", "username", "password");

// define the query.
$sql_query = mysql_query("SELECT * FROM catalogue WHERE id LIKE '$yourid' LIMIT 10");

// check the query for results.
if (mysql_num_rows($sql_query) > 0) {
    $row = mysql_fetch_assoc($sql_query); // retrieve the information.

    // loop while there are entries in the database.
    while($row) {
        extract($row); // extract the $row information
        echo "$cartitem,";
    }
}
else {
    // there are no results.
    echo 'There was no items found.';
}

Manipulating Data in the Shopping Cart Database

At the moment the data can only be accessed and manipulated, it can not be changed by the user. First of all a table needs to be created for the users to change the stored data. I named this table “cart” and it only has three rows, these are;

  • punkisID — A unique id relating to the users browser.
  • id — This references to the id number in the “catalogue” table.
  • amount — The quantity of the item ordered.

Creating The Table “cart”

CREATE TABLE cart (
punkisID varchar(255) NOT NULL default '',
id int(11) NOT NULL default '0',
amount int(11) NOT NULL default '1'
) TYPE=MyISAM;

This is a table which the user can change the data stored within it. There are three ways the user can change data stored with a table within this e-commerce website. They can add, delete & update data stored in a specific table. Below are some code examples to add, delete & update data within the table.

Add Data To The Table “cart”

$add = mysql_query("INSERT INTO cart (ID,amount)". "VALUES ('$addid','$amount')");

Delete Data From The Table “cart”

$delete = mysql_query("DELETE FROM cart WHERE id LIKE '$id' ")

Update Data In The Table “cart”

$update = mysql_query("UPDATE cart SET amount='$amount' ");

Cookies

What are cookies in computer terms? Here is a definition from PopUpCop.com

A small string of text that is stored on your machine by the browser at the request of a Web site. Its purpose is to keep track of who you are (profile information such as your name) and what you do (your activities at that site such as which products you look at). Cookies are not usually nefarious in their purposes. One site’s cookies cannot be accessed by another site. Often, cookies are used by advertisers to record the fact that they have already shown you a popup advertisment, and should wait a while before showing the ad to you again. Many web sites rely on cookies for managing complex interactions and cannot function without them.

PopUpCop, 2002

I have used one cookie on this E-Commerce shopping cart. The cookie stores the users session ID number which is generated automatically when a browser window is opened. In PHP, the session has to be called before any HTML code is outputted to the browser. This is done with this piece of code session_start();.

Creating a Cookie in PHP

// a cookie consists of a title, the information to be stored and the expiry time in seconds.
setcookie(title, information, time()+000);

$PUNKISID = $HTTP_COOKIE_VARS["PHPSESSID"]; // setting a variable to the session ID.
setcookie(punkis, $PUNKISID); // note: an expiry time does not have to be set.

Reading From A Cookie in PHP

$variable = $_COOKIE["title"];
$PUNKISID = $_COOKIE["punkis"]; // reads the data back into the $PUNKISID variable

Every time a user opens up a browser and views the Punk Inflicted Ska’s e-commerce shopping cart website, a random ID number is generated from the browser and stored in a cookie on their computer. This is then referenced on different pages to maintain their specific cart information. Once the browser window is closed, the random ID number is lost, and the cookie is deleted.

Combining the cookie information with the three simple add, delete & update MySQL queries, a complete e-commerce shopping cart can be built. First checking the ID of the computers browser matches the “cart” tables ‘punkisID’ row, displaying this information, then manipulating the appropriate rows.

Mail()

Using the mail() command within PHP I was able to send different information via linux’s sendmail function to both the merchant & clients email address. The client recieves an email to the address they submitted to the form, which details they’re shipping address, date ordered, random number & delivery address. The merchant recieves this data along with what items they have ordered & the clients entered card details. This data is not secure. To secure this data, the information must be passed through some encryption such as GnuPG and outputted as a variable, while there is a secure connection between the client and the webserver. This connection is made using Secure Sockets Layer (SSL).

The server sends a plain text email to the merchant which is easily read, and a HTML email to the client, which is based upon the e-commerce web site. This is done by defining different headers for each email, and using the appropriate markup for the HTML email.

Mail() Headers

// used to stop email servers treating the mail as spam
"X-Priority: 1\r\n";
"X-MSMail-Priority: High\r\n";
"X-Mailer: php\r\n";

// headers to define a reply address and a failed-send address.
// the from part displays text instead of an email address.
"From: PunkIS Webshop Order <$merchant>\r\n";
"Reply-to: <$merchant>\r\n";
"Return-path: <$merchant>\r\n";

"MIME-Version: 1.0\r\n"; // defines the multipurpose internet mail extension version.

"Content-type: text/html; charset=iso-8859-1\r\n"; // used for HTML email.
"Content-type: text/plain; charset=iso-8859-1\r\n"; // used for plain email.

Send Mail()

// uses the appriopriate variables and sends them to the appropriate email address.
mail($client, $client_subject, $client_details, $cheaders);
mail($merchant, $merchant_subject, $merchant_details, $mheaders);

Working Locally

I worked locally on most of this assignment, running off the web server Apache (version 2.0.48) on Microsoft Windows XP. I also had PHP 4.4.3, OpenSSL 0.9.7c & Mod_SSL 2.0.48 modules installed. Using this system I was able to test the website after every small change made, giving me instant feedback. This was a great way to error check the PHP code. I was able establish a secure connection by writing my own security certificates. This method uses the SSL protocol, the security method implemented by thousands of commercial E-Commerce websites worldwide.

Conclusions

I encountered many problems while testing out the shopping cart at various stages and I hopefully managed to overcome most of them. However, there are still some bugs and overall I feel it could be improved substantially with more time.

Improvements

  • Tweaking the layout to iron out a few errors.
  • Styling the checkout & account information better.
  • Error checking & validating the information on checkout.
  • A “confirm order” screen with the client’s details.

Version Compatabilites

As stated earlier I worked locally in developing the e-commerce website using PHP version 4.3.3 which was the latest build in September 2003, when I installed the server. When I uploaded the working web site to the university server hosting the module submit folder, I encountered numerous problems & errors, all relating to the version of PHP the server was running. The university server was running version 4.0.4 of the PHP module.

Below are some of the problems I encountered and the code I altered to fix them:

Fix Random Number

rand(); // used to generate a random number
srand(time()); used before rand(); // to seed the random number generator

Global Variables

$PHP_SELF; // outputs the string of the URL.
$HTTP_SERVER_VARS["REQUEST_URI"]; // this is compatible with older versions of PHP.

$PHPSESSID; // variable for the random session ID
$HTTP_COOKIE_VARS["PHPSESSID"]; // works with version PHP 4.0.4.

$_GET['variable'] // used to retrieve the variable from the URL.

// this converts $_GET into a variable older versions of PHP understand.
if($HTTP_GET_VARS) foreach ($HTTP_GET_VARS as $k=>$v) $_GET[$k] = $v;