A Simple E-Shop Application Using PHP and MySQL

Create a simple E-shop application using PHP and MySQL*. You can use this to create a bookshop or music shop, or clothing store. Customers should have access to a “user” interface that allows them to choose and buy items. Payments are not required. A password-protected interface for the “owner” should be available to enable the E-shop operator to view/edit stock or pending orders.

This exercise is intentionally “hard” due to the deadline. I don’t expect everyone to finish it perfectly. This will be considered in the marking.

MARKING SCHEMA:

[A 5%] Ability write working HTML code code that creates successful markup in a browser window **

[B 5%] Possibility to upload an HTML web page on a server so that it can be seen on the World Wide Web. ***

[C 10%] The ability to communicate HTML forms with a PHP script. **

[D 10%] The ability to make a PHP script communicate with a MySQL table. **

[E 40%] Functionality: The extent to which software’s operation conforms to – or exceeds – the original specification.

[F 10%] User friendliness. The extent to which the browser displays information that is clear and easy to understand.

[G 10%] Presentation. This could include the appropriate use of images and color as well as style-sheets, style-sheets, and dynamic HTML functions.

[H 10%] Readability and clarity of code and related documentation (e.g. Use of comments, proper indentation, etc.

Notes

*You can use other web-based technologies, such as JSP, Pearl. Please use ASP, JSP or Pearl to access other web-based technologies. Please do not use any high-level software programs that require programming skills (e.g. These editors are not necessary for this module.

** The marks in categories A through D are given in a “binary”, i.e., they can be divided into two equal parts. The student gets either the full weighting mark or zero. Simply by having a basic understanding of HTML, PHP, and MySQL, 30% can be achieved. The rest of the categories are marked in “continuous” (i.e. Any number between zero and the weighting amount can be given.

*** In case of server failure or other technical difficulties, Category B will be deleted and the weightings for the other categories adjusted accordingly.

SOLUTION

My electronic shop application is to sell books and other items sold by the Wandle Industrial Museum.

This is an extension of an existing static website. This pre-existing work is the basis for my application’s data and layout.

THE MAIN INTERFRACE

The initial screen without the original frames that were used on the Museum website. The majority of the content within the top and bottom frames are not functional. This is done to demonstrate the dynamic HTML which provided secondary navigation structures.

This screen shows the main body and “shopping basket” functions. This page gives you access to:

1) Complete list of all the online “catalogues”

2) A subset of catalogues arranged by product category

3) A subset based on user-defined search terms.

4) Additional information about the item

5) The ability to add items to a session that is based on a “shopping basket”.

6) The ability to review books by registered users

When a user logs in for the first time, they are shown the message “Welcome.” Are you already a customer? Sign in by clicking here. Otherwise, they will see their user name. A button is located to the right called “My account”, which allows them to edit their information. This information is saved in a client-side cookie and is kept for 30 minutes.

ABOUT SUMMARY

Summary of Items displays the item code, title, and price of each item. It also includes a description and stock level. Two additional options are also included in the display. 1 item that is out of stock is highlighted in red text. 2 items not sold in the online shop are marked “grayed out” in these cases, the “buy” button for each item is removed.

THE BASKET

The Shopping Basket is initially empty until an item is added to it. After that, clicking on the “view basket” button will display a default screen.

The default quantity of an item added is 1. You can increase this quantity by either adding another item to your basket or clicking the increment button shown on the basket display screen. After creating a basket and entering the system, the following options will be available:

1) Clear cart

2) Continue shopping;

3) Checkout;

The item is removed from your basket if it has been de-incremented to zero.

The basket is built on PHP sessions. Unlike the users identity which is reined after 30 minutes, it will not persist once the instance is closed.

If the user is not logged in to the application, they will be asked for their password.

Registering for an account is required before the user can purchase anything.

ADDING ITEMS to THE SHOPPING BASIC

You can add items to your shopping basket from either a summary listing or the item description page.

A summary page contains basic information about the item in a tabular format without images. Click on the “buy” button to the left of the item line.

You can also do this from the item detail page. This is done by clicking on the items code displayed to the left of the summary display.

Every item detail page includes a “buy” button, as well as other shopping basket tools. These buttons are located on the summary page. Scroll down to see them.

There are two types of items in the database: “books”, which contain information specific to reading materials, as well as “goods”, which only contain basic information such as item code, title, price, and item code.

Images that are not readily available will be replaced until the administrator requests it.

ABOUT YOU

If the user is not logged in to the application, they will be asked for their password. If the user does have an account, he/she can log in and continue the transaction.

Registering for an account is required before the user can purchase anything. This is the result of an inadvertent attempt to create a new account. Once all anomalies are resolved, the user receives a text description.

Once the user has registered, he/she will be asked to print the form. After logging in, you can purchase without authentication.

The application was hard-coded with one administrative account. The username is “system” while the password is “god”. Note that there is no way to modify this currently.

Administration displays allow users to:

1) define new item types;

2) Edit and delete users

3) Add, Edit and Delete categories

This screen allows you to edit an existing data item within a particular category, identified by its product code, or create a new product-date item. As shown in the following image, the application requires a set of validation criteria. The items are listed alphabetically, from left to right.

This is the result of an unsuccessful data entry operation. The crosses denote invalid or missing entries, and the tick denotes that the field is valid.
You can have a blank entry.

In certain cases, the question mark can be used to double-check that you intend to leave blank.

This screen displays that the administrator has selected to delete the user’s account from the database. The edit and delete functions both use the same display, except that the delete function doesn’t show the delete warning.

Interface The image is the initial screen (welcome), and does not include frames that are used on the Museum website.

This screen shows the main body and “shopping basket” functions. The button is at the top. This page gives you access to:

1) Complete list of all the online “catalogues”

2) A subset of catalogues arranged by product category

3) The ability to order the catalogue by product code, price, or title in an acceding ordering

4) A subset based on user-defined search terms.

5) Additional information about the item

6) The ability to add items to a session that is based on a “shopping basket”.

When a user logs in for the first time, they are shown the message “Welcome.” Are you already a customer? Click here to log in. Otherwise, they will see their user name along with a button called “My account”, which allows them to modify their information. This information is saved in a client-side cookie and is kept for 30 minutes.

Administration displays allow users to:

1) Define new types of items;

2) Edit and delete users

3) Add, Edit and Delete categories

4) Edit, move, and delete products

5) Add help information to edit.

6) Make and restore tables dumps;

DATABASE DICTIONARY

A data dictionary is a list of common codes and conventions. Below are the required database lookup tables for the shop database application.

Table: bookinfo:
Comments: Book specific metadata. Field Type Null Default
code varchar(5) No No
author varchar(50) No Unknown
publisher varchar(100) No Unknown
isbn varchar(15) No None
illustrations varchar(15) Yes Yes - gray
date date Yes NULL
dimensions varchar(100) No Unknown
summary text No
type varchar(16) No
Table: categories:
Comments: Product metadata Field Type Null Default
id int(3) No
name varchar(50) No
etype int(1) No 2
Keywords varchar(100) No

Table: customers:
Comments: Customer billing information. Field Type Null Default
uname varchar(15) No
first_name varchar(25) No
last_name varchar(25) No
phone varchar(20) No
email varchar(50) No
zip varchar(10) No
city varchar(50) No
country_code int(2) No 0
bill_address varchar(90) No
changed timestamp(14) Yes NULL

Table: orderdata:
Comments: Customer order information Field Type Null Default
ordernumber int(5) No 0
itemcode varchar(5) title varchar(100) No
quantity int(3) No 0
total float No 0
status int(11) No 0
stamp timestamp(14) Yes NULL

Table: orders:
Comments: customer order refferance data. Field Type Null Default
uname varchar(15) No 0
ordernumber int(5) No
status int(1) No 0
date date Yes NULL

Table: reviews:
Comments: user book reviews and rations. Field Type Null Default
uname varchar(15) No 0
type int(1) No
rating int(1) No 0
stamp timestamp(14) Yes NULL
review text No

Table: products:
Table comments: Primary product data. Field Type Null Default
code varchar(5) No
category_id int(3) No 1
title varchar(100) No
keywords varchar(255) No
small_image varchar(15) No nopic.jpg
large_image varchar(15) No nopic.jpg
description text No
price decimal(5,2) No 00.00
date_modified date No 0000-00-00
status int(1) No 1
Table: purchase:
Comments: Primary product data. Field Type Null Default
code varchar(5) No
date timestamp(14) Yes NULL
stock int(3) No> 15
cost decimal(5,2) No 00.00

Table: pwl:
Comments: User password/access information. Field Type Null Default
uname varchar(15) No
upass varchar(100) No 5d2e19393cc5ef67
created timestamp(14) Yes NULL

Table: syshelpindex:
Comments: System help subject index.Field Type Null Default
cat int(2) No 0
subject varchar(30) No

Table: syshelpinfo:
Comments: system help information.Field Type Null Default
cat int(3) No 0
title varchar(30) No
info text No
image varchar(20) No
type int(1) No 0

FILE STRUCTURE

This electronic shop application allows you to sell books and other items sold by the Wandle Industrial Museum. This extension is a natural extension of an already existing static website.

The application’s files are stored in 9 subdirectories. These are as follows:
../htdocs/order.htg/ This is the root directory of the web application, it contains all resources related to the website. All generic files and ancillary elements are stored at this level. (3.74mb 263 files)

• continfo.php – contact and ordering information (PHP 4.01kb).
• create_db.sql – Database structure (SQL dump 5.82kb).
• credit-cards.gif – Usable credit cards types (image 2.02kb).
• cvc.jpg – Clarifying where to find a CVC value (image 6.0kb).
• dictionary.txt – MYSQL Data Dictionary (text 2.85kb).
• discount.gif – Animalised 10% discount for members notice (image 111.kb).
• discount1.gif – Advertising a 10% discount for members (image 53.6kb).
• avicon.ico – Depiction of a simple waterwheel (icon 318b).
• index.htm – Custom website 404 error page (HTML 1.76kb).
• new_ani.gif – An animation of the word ‘new’ (image 1.66kb).
• offers.gif – Graphic of the text ‘check these special offers’ (image 1.77).
• pleasenote.gif – A burning animation of the text ‘please note’ (image 14.7kb).
• reset.gif – Graphical input form reset button (image 1.28kb).
• shop.gif – Small photograph of the real museum shop (image7.31kb).
• submit.gif – Graphical input form submit button (image 1.18kb).

../htdocs/order.htg/common.htg/ This directory contains all common utility scripts and other resources that the web application depends upon. (185kb 50 files)

• about0.gif – Graphic of the text ‘home page’ (image 0.98kb).
• alart.gif – Animalised rotating red warning bulb (image 2.04kb).
• alert.gif – A human skull with the word ‘warning’ (3.39kb).
• archive0.gif – Graphic of the text ‘archive’ (image 970b).
• backg.jpg – Wallpaper drawing depicting a watermill as a watermark (image 13.6kb).
• background.jpg – Wallpaper featuring a marble stone type effect (image 4.59kb).
• basic.css – Website formatting and formatting descriptions (style sheet 7.53kb).
• brics.gif – Animalised exposed circuits in a wall behind the wallpaper (image 7.46kb).
• caurtion.gif – Animalised rotating caution road sign (image 6.53kb).
• cnote.htm – Website copyright and attribution information (HTML 1.95kb).
• constants.inc – Application global data values (PHP include 161b).
• contact0.gif – Graphic of the text ‘contacts’ (image 976b).
• db_lib.php – Common database access functions and utilities (PHP 25.2kb .
• disclaimer.gif – Animalised flashing graphic of the text ‘disclaimer’ (image 4.57kb).
• email2.gif – Drawing of an mail outbox over a globe (image 1.12kb).
• fax.gif – Drawing of a basic fax machine (image 231b).
• feed0.gif – Graphic of the text ‘feedback’ (image 990b).
• focus.js – Script to place focus on a hidden popup window (javascript 165b).
• home0.gif – Graphic of the text ‘general’ (image 967b).
• iiv.gif – Transparent graphic of the Investing in Volunteers logo (image 2.63kb).
• iiv.fpg – Opaque graphic of the Investing in Volunteers logo (image 4.49kb).
• index.htm – Custom website 404 error page (HTML 1.76kb).
• info.gif – Transparent museum logo with contact information (image 2.31).
• jfooter.js – Code to dynamically generate page footer information (javascript 2.41kb)
• letter.gif – Drawing of two simple letters (image 698b).
• linksb.jpg – A spider in a web on a small depiction of a browser (image 1.18.kb).
• linkware.gif – Transparent graphic of the text ‘all materials on this website are link ware’ (image 1.56).
• map0.gif – Graphic of the text ‘sitemap’ (image 989b).
• members0.gif – Graphic of the text ‘members’ (image 984b).
• misc.inc – Freeola SQL service access authentication data (PHP include 144b).
• news0.gif – Graphic of the text ‘news letter’ (image 947b).
• l_misc.inc – SQL authentication data for my server (PHP include 144b).
• nopic.jpg – Notice of there being no available photo (image 5.40).
• order0.gif – Graphic of the text ‘Mail Order’ (image 0.96kb).
• phone.gif – Transparent graphic of a red telephone (image 281kb).
• print.css – Screen formatting descriptions (style sheet 3.65kb).
• registered.gif – Opaque graphic of the registered museums logo (image 428b).
• registered3.gif – Transparent graphic of the registered museums logo (image 2.80b).
• resize.js – Code to dynamically change a window to the image size (javascript 1.05kb).
• search.gif – Graphic of the text search’ (image 108b).
• sqldumper.inc – Code to dynamically create an SQL dump file (PHP include
• stop.gif – Transparent graphic of a red hand (image 2.64kb).
• time.js – Code to dynamically display a countdown timer (javascript 591b).
• toc.js – Code to dynamically display a navigation structure (javascript 3.34kb)
• wandlemuseu.gif – Transparent animated graphic of the text ‘The Wandle Industrial Museum’ (image 42.3kb).
• webbo.gif – Transparent graphic of webbo’s head (image 1.16kb).
• webo1.gif – Transparent graphic of webbo (image 3.81kb).
• wimlogo.gif – Opaque graphic of the museum’s waterwheel logo (image 417b)

../htdocs/order.htg/data_backup/ This directory is used to store SQL dump files..(242kb 3 files)
../htdocs/order.htg/help/ This directory is used to store graphics for help desk database. (336kb 26 files)
../htdocs/order.htg/images.htg/ This directory is used to store graphics for the shop database. (2.41mb 91 files)

../htdocs/order.htg/mysqlsnips This directory contains a set of text files related to example SQL commands.

../htdocs/order.htg/shop.htg/ This directory contains all the source files related to the public areas of the shop database utility scripts and other template resources that the web application depends upon. (85.1kb 20 files)

• basket.php – Create and maintains a session based shopping basked (PHP 9.29bk).
• book_detail.php – Code block for displaying detailed information for a selected book (PHP 2.41kb).
• buttons.inc – Code block for displaying action bottoms related to product information (include 2.43kb).
• catalog.php – Generates users view of the primary catalogue interface (PHP 6.63kb).
• checkout.php – Script for updating the product and order database (PHP 5.02kb).
• help.php – Display users view of the system help desk interface (PHP 6.71kb).
• login.php – Script for user and database administration login operations (PHP 7.91).
• old login.php – Obsolete login script (PHP8.99kb).
• orderend.php – Code block for displaying shopping basket buttons (PHP 1.36kb).
• ordertop.php – Code block for displaying shot information and primary controls (PHP5.73kb).
• pagefoot.inc – Code block to create the ending HTML page structure (Include 801b).
• pagehead.inc – Code block to create the starting HTML page structure (Include 854b).
• product_detail.php – Code block for displaying detailed product information (PHP 1.15kb).
• product_list.php – Code block for displaying summary item information (PHP 3.07kb).
• product_show.php – Code block that displays alternative summary information (PHP3.16).
• prompt.inc – Code block for displaying a message to the user (include 897b).
• signin.php – Display logon simple login screen.(PHP 3.08kb).
• terms.php – Display terms and conditions (PHP 5.83kb).
• terms2.php – Display terms and conditions far a child window (PHP 5.75kb).
• thankyou.php – Display final confirmation screen (PHP 3.28kb).

../htdocs/order.htg/update.htg/ This directory contains all the source files related to the administration areas of the shop database and other utility scripts. (240kb 34 files)

• addcat.php – Add additional item categories to the database (PHP 2.18kb).
• addproduct.php – Add additional items to the database (PHP 6.30kb).
• admin.php – Website content management interface (PHP 65.9kb).
• backup.php – Generates database dump files (PHP 5.21kb).
• berror_form.inc – Current errors for new book database items (Include PHP 3.0kb).
• bookinfo.inc – Code block for book data entry (Include 6.64kb).
• bookup.inc – Display editable book information (Include 10.2kb).
• buttons.inc – Code block to create editing command buttons (Include 4.41kb).
• change.php – Source code for update confirmation messages (PHP14.4kb).
• edithelp.php – Enables editing and creation of help desk information (PHP14.0kb).
• entbuttons.inc – Code block for data entry forms command buttons (Include 1.21kb).
• error.gif – Transparent graphic of a cartoon cross (Image 118b).
• fine.gif – Transparent graphic fo a cartoon tick (Image 126b).
• help.php – Display administrators view of the system help desk interface (PHP 6.22kb).
• index.php – Administrative access to database modification functionality (PHP 12.5kb)
• invoice.php – Generate display customer invoices for printing (PHP 5.33kb).
• mysqlsend.php – Enable user to execute custom SQL instructions (PHP 4.59)
• newcat_form.inc – .Display new the category name error correction (Include 773b).
• newname_table.inc – Display new product entry form (Include 438b).
• newproduct.php – Update/delete data from the inventory database (PHP 781kb).
• newtypeform.inc – Display new category entry form (Include 563kb).
• note.inc – Display system information messages (Include 524b).
• oldorders.php – Display summary data for closed orders (PHP 3.22kb).
• orders.php – Display selected open order information (PHP 7.77kb).
• perror_form.inc – Display product information Errors (Include 1.99).
• productinfo.inc – Display product input form (Include 3.68).
• productup.inc – Display editable product information (Include 7.02kb).
• quest.gif – Transparent graphic fo a question mark (Image 114b).
• stock.php – Administrative stock data summary (PHP 9.07).
• upload.jpg – Graphical upload button (image 1.84kb).
• upload.php – Copy images to the images.htg directory on the server (PHP 5.40kb).
• userorders.php – Display user order history (PHP 2.36kb).
• users.php – Display editable user login information (PHP10.04kb).