Home arrow PHP arrow Page 5 - Building an E-Commerce Site Part 1: Building a Product Catalog

Step 2: Creating the Product Catalog - PHP

This is the first article in a three-part series dealing with using PHP 4 and MySQL to make a comprehensive e-commerce storefront solution. This article covers the product catalog database design and creating the catalog administration interface.

TABLE OF CONTENTS:
  1. Building an E-Commerce Site Part 1: Building a Product Catalog
  2. Assumptions and Requirements
  3. Overview of a Simplified E-Commerce System
  4. Step 1: Creating the Database
  5. Step 2: Creating the Product Catalog
  6. Step 3: Populating the Tables with Data
  7. Step 4: Creating Catalog Maintenance Screens
  8. Step 5: Putting It All Together
By: Ying Zhang
Rating: starstarstarstarstar / 129
April 25, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
We have the user account created, now let's create the product catalog tables. Let's say that our product catalog looks like this (yes we have a very small catalog :)

[Top] | +--[Fruits] | | | +--[Apples] | | | | | +-- Granny Smith | | +-- Red Delicious | | +-- Rome Beauty | | +-- Apple Chips | | | +--[Citrus] | | | | | +-- Orange | | +-- Lemons | | +-- Grapefruit | | | +--[Berries] | | | | | +-- Blueberries | | +-- Strawberries | | +-- Raspberries | | | +-- Bananas | +--[Vegetables] | | | +-- [Lettuce] | | | | | +-- Butterhead | | +-- Cutting | | +-- Crisp | | | +-- [Mushrooms] | | | | | +-- Truffles | | +-- Shiitake | | +-- Enoki | | | +-- [Potatoes] | | | | | +-- Sweet Potatoes | | +-- White Rose | | +-- Russet | | +-- Potatoe Chips | | | +-- Cucumbers | +--[Snacks] | +-- [Chips] | | | +-- Potato Chips | +-- Apple Chips | +-- Corn Chips | +-- [Icecream] | |
| +-- Vanilla | +-- Chocolate | +-- Popcorn
What we want is an easy way to manage this catalog in our database. Product categories are arranged in a tree. There is one root category called Top, under which all other categories will sit. Next, go the subcategories under those, etc. For our products, we want to be able to put a product into one or many categories to make it easy for the customer to find.

So in summary, we have what looks like this:

Reading this clockwise, we say that:

  • A category contains zero or more (0|M) products
    eg. We have 3 products in the [Apples] category
  • A product belongs to one or more (1|M) categories
    eg. Potato chips are in the [Potatoes] and [Chips] category
  • A category contains one or more (1|M) categories
    eg. We have 3 categories under the [Fruits] category
  • A category belongs to one and only one (1|1) category
    eg. The [Chips] category can only belong under snacks

NOTE: The 0|M, 1|M are just my own notations to represent the optionality and cardinality, if that doesn't make sense to you don't worry about it too much, you can look these terms up in a database book later.

NOTE: We can make categories a many-to-many relation upon itself, such that a category can have multiple sub-categories and belong to multiple parent-categories, but it's a too much to explain for this howto :) so we won't do it like that.

In terms of the database entity relations, we have 2:

  1. the categories entity is a one-to-many relation upon itself
  2. the products entity is a many-to-many relation with the categories entity

Now based on the relations that we have defined, we will need three tables to drive our product catalog:

  1. categories

    This table will hold the names of our categories, as well as telling us where this category sits in our catalog.

    Fieldname

    Type

    Description

    PK

    id

    int auto_increment

    An internal identifier (ID) for this category

    IDX

    parent_id

    int

    The ID of the parent category

    IDX

    name

    varchar(25)

    A name for this category

    description

    varchar(255)

    A description for this category

    Since the ID will only be used internally, we will be using a MySQL autonumber to generate them. Also, since the ID field will be used to identify records it will be the primary key of this table. We want to index the parent_id and name fields because we may want to perform searches or look up categories by them.

  2. products

    This table will hold the information about our products, for example the name, description and price, etc. In real life you will have a lot more fields to hold other information about the product, but for the purposes of this guide, this is all you need.

    Fieldname

    Type

    Description

    PK

    id

    int auto_increment

    An internal identifier (ID) for this product

    IDX

    name

    varchar(25)

    A name for this product

    description

    varchar(255)

    A description for this product

    price

    float(5,2)

    The price of this product

  3. products_categories

Since we have a many-to-many relation between products and categories, we need a table to keep track of these relations. This table simply keeps pairs of product IDs and category IDs that relate to each other.

Fieldname

Type

Description

PK

product_id

int

The ID for the product

PK

category_id

int

The ID for the category

Okay, so we know what tables we want, now let's write the SQL CREATE TABLE queries to create them in MySQL. Run the following commands (lines in /* */ are comments and will be ignored by MySQL):


mysql> /* create the categories table */ -> CREATE TABLE categories ( -> id int auto_increment not null, -> parent_id int not null, -> name varchar(25) not null, -> description varchar(255) not null, -> PRIMARY KEY (id), -> INDEX parent_id (parent_id), -> INDEX name (name) -> );
mysql> /* create the products table */ -> CREATE TABLE products ( -> id int auto_increment not null, -> name varchar(25) not null, -> description varchar(255) not null, -> price float(5,2) not null, -> PRIMARY KEY (id), -> INDEX name (name) -> );
mysql>/* create the products_categories table */ ->CREATE TABLE ->products_categories ( ->product_id int not null, ->category_id int not null, ->PRIMARY KEY (product_id, category_id) ->);
Let's take a look at what we've just created, let's list the tables with the following command:

mysql> SHOW TABLES; +---------------------+ | Tables in mymarket | +---------------------+ | categories | | products_categories | | products | +---------------------+
Now let's look each table with the DESCRIBE command:

mysql> DESCRIBE categories; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | | parent_id | int(11) | | MUL | 0 | | | name | varchar(25) | | MUL | | | | description | varchar(255) | | | | | +-------------+--------------+------+-----+---------+----------------+
mysql>DESCRIBE products; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | 0 | auto_increment | | name | varchar(25) | | MUL | | | | description | varchar(255) | | | | | | price | float(5,2) | | | 0.00 | | +-------------+--------------+------+-----+---------+----------------+
mysql>DESCRIBE products_categories; +-------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | product_id | int(11) | | PRI | 0 | | | category_id | int(11) | | PRI | 0 | | +-------------+---------+------+-----+---------+-------+
Now that we have all our tables, let's move on and add some data.

 
 
>>> More PHP Articles          >>> More By Ying Zhang
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: