Sams Teach Yourself PHP, MySQL and Apache All in One (2012)

In this short chapter, you create a generic online storefront. As with the previous project chapters, you learn how to create the relevant database tables as well as the scripts for displaying the information to the user. The examples used in this chapter represent one of an infinite number of possibilities to complete these tasks and are meant to provide a foundation of knowledge rather than a definitive method for completing this task.

Planning and Creating the Database Tables

Before you tackle the process of creating database tables for an online store, think about the real-life shopping process. When you walk into a store, items are ordered in some fashion: The hardware and the baby clothes aren’t mixed together, the electronics and the laundry detergent aren’t side by side, and so on. Applying that knowledge to database normalization, already you can see that you need a table to hold categories and a table to hold items. In this simple store, each item belongs to one category.

Next, think about the items themselves. Depending on the type of store you have, your items might or might not have colors, and might or might not have sizes. But all your items will have a name, a description, and a price. Again, thinking in terms of normalization, you can imagine that you might have one general items table and two additional tables that relate to the general items table.

Table 22.1 shows sample table and field names to use for your online storefront. In a minute, you create the actual SQL statements, but first you should look at this information and try to see the relationships. Ask yourself which of the fields should be primary or unique keys.

Table 22.1 Storefront Table and Field Names

As you can see in the following SQL statements, the store_categories table has two fields besides the id field: cat_title and cat_desc, for title and description, respectively. The id field is the primary key, and cat_title is a unique field because there’s no reason you would have two identical categories:

CREATE TABLE store_categories (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
cat_title VARCHAR (50) UNIQUE,
cat_desc TEXT
);

Next we tackle the store_items table, which has five fields besides the id field—none of which are unique keys. The lengths specified in the field definitions are arbitrary; you should use whatever best fits your store.

The cat_id field relates the item to a particular category in the store_categories table. This field is not unique because you will want more than one item in each category. The item_title, item_price, and item_desc (for description) fields are self-explanatory. The item_image field holds a filename (in this case, the file is assumed to be local to your server) that you use to build an HTML <img> tag when it is time to display your item information:

CREATE TABLE store_items (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
cat_id INT NOT NULL,
item_title VARCHAR (75),
item_price FLOAT (8,2),
item_desc TEXT,
item_image VARCHAR (50)
);

Both the store_item_size and store_item_color tables contain optional information: If you sell books, they won’t have sizes or colors, but if you sell shirts, they will. For each of these tables, the item_id and item_size fields are not unique keys because you can associate as many colors and sizes with a particular item as you want:

CREATE TABLE store_item_size (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
item_id INT NOT NULL,
item_size VARCHAR (25)
);
CREATE TABLE store_item_color (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
item_id INT NOT NULL,
item_color VARCHAR (25)
);

These are all the tables necessary for a basic storefront—that is, for displaying the items you have for sale. Chapter 23, “Creating a Shopping Cart Mechanism,” integrates the user experience into the mix. For now, just concentrate on your inventory.

In Chapter 20, “Creating an Online Address Book,” you learned how to use PHP forms and scripts to add or delete records in your tables. If you apply the same principles to this set of tables, you can easily create an administrative front end to your storefront. We do not go through that process in this book, but feel free to do it on your own. (If you understood what was going on in Chapter 20, you know enough about PHP and MySQL to complete the tasks.)

For now, you can simply issue MySQL queries, via the MySQL monitor or other interface, to add information to your tables. Following are some examples, if you want to follow along with sample data.

Inserting Records into the store_categories Table

The following queries create three categories in your store_categories table: hats, shirts, and books.

INSERT INTO store_categories VALUES
('1', 'Hats', 'Funky hats in all shapes and sizes!');

INSERT INTO store_categories VALUES ('2', 'Shirts', 'From t-shirts to
sweatshirts to polo shirts and beyond.');

INSERT INTO store_categories VALUES ('3', 'Books', 'Paperback, hardback,
books for school or play.');

In the next section, we add some items to the categories.

Inserting Records into the store_items Table

The following queries add three item records to each category. Feel free to add many more.

INSERT INTO store_items VALUES ('1', '1', 'Baseball Hat', '12.00',
'Fancy, low-profile baseball hat.', 'baseballhat.gif');

INSERT INTO store_items VALUES ('2', '1', 'Cowboy Hat', '52.00',
'10 gallon variety', 'cowboyhat.gif');

INSERT INTO store_items VALUES ('3', '1', 'Top Hat', '102.00',
'Good for costumes.', 'tophat.gif');

INSERT INTO store_items VALUES ('4', '2', 'Short-Sleeved T-Shirt',
'12.00', '100% cotton, pre-shrunk.', 'sstshirt.gif');

INSERT INTO store_items VALUES ('5', '2', 'Long-Sleeved T-Shirt',
'15.00', 'Just like the short-sleeved shirt, with longer sleeves.',
'lstshirt.gif');

INSERT INTO store_items VALUES ('6', '2', 'Sweatshirt', '22.00',
'Heavy and warm.', 'sweatshirt.gif');

INSERT INTO store_items VALUES ('7', '3', 'Jane\'s Self-Help Book',
'12.00', 'Jane gives advice.', 'selfhelpbook.gif');

INSERT INTO store_items VALUES ('8', '3', 'Generic Academic Book',
'35.00', 'Some required reading for school, will put you to sleep.',
'boringbook.gif');

INSERT INTO store_items VALUES ('9', '3', 'Chicago Manual of Style',
'9.99', 'Good for copywriters.', 'chicagostyle.gif');