Workshop 10 : PHP & MySQL Online Store Pt.1

In this workshop, we will discuss the steps to create a simple online store application with PHP and MySQL.

The first step is to login to the MySQL database and select/ create a database to be used. Instructions on how to do this can be found on the previous workshop. Let's use the lohky database created earlier and then create a table using t enter records to save some time. Here we go!

Mysql > use lohky 

mysql> create table store_categories (
    -> id int not null primary key auto_increment,
    -> cat_title varchar (50) unique,
    -> cat_desc text
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table store_item_size (
    -> item_id int not null,
    -> item_size varchar (25)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table store_item_color(
    -> item_id int not null,
    -> item_color varchar (25));
Query OK, 0 rows affected (0.00 sec)

After creating the table, we would then need to store some records inside the created tables

Insert some records onto the store_categories tables

mysql> insert into store_categories values
    -> ('1','hats','funky hats in all shapes and sizes!');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_categories values ('2', 'Shirts', 'All shapes and size
s.');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_categories values ('3', 'Books', 'Fiction and all.');
Query OK, 1 row affected (0.01 sec)

Insert some records on to the store_items table

mysql> insert into store_items values ('1', '1', 'Baseball Hat', '12.00', ' Fan
cy, low profile baseball hat.', 'baseballhat.gif');
Query OK, 1 row affected (0.07 sec)

mysql> insert into store_items values ('2', '1', 'cowboy hat', '52.00', '10 gal
lon variety', 'cowboyhat.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('3', '1', 'Top Hat', '102.00', 'A classi
c', 'tophat.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('4', '2', 'Short sleeve T Shirt', '12.00
', '100% cotton, pre shrunk.', 'sstshirt.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('5', '2', 'Long sleeve T Shirt', '15.00
'> ', '100% cotton, pre shrunk.', 'lstshirt.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('6', '2', 'Sweatshirt', '22.00
'> ', 'Ideal for chilly nights.', 'sweatshirt.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('7', '3', 'Jane Self Help Book', '12.00
'> ', 'HELPING You.', 'book1.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('8', '3', 'Client Server Applications',
'40.00
'> ', 'A good reference.', 'book2.gif');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_items values ('9', '3', 'Style Guru', '40.00
'> ', 'Fashionable tips.', 'book3.gif');
Query OK, 1 row affected (0.00 sec)

Insert records into the store_item_size table

mysql> Insert into store_item_size values (1, 'one size fits all');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_size values (2, 'one size fits all');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_size values (3, 'one size fits all');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_size values (4, 'S');
Query OK, 1 row affected (0.01 sec)

mysql> Insert into store_item_size values (4, 'M');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_size values (4, 'L');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_size values (4, 'XL');
Query OK, 1 row affected (0.00 sec)

Insert records into the store_item_color table

mysql> Insert into store_item_color values (1, 'red');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_color values (1, 'black');
Query OK, 1 row affected (0.00 sec)

mysql> Insert into store_item_color values (1, 'blue');
Query OK, 1 row affected (0.00 sec)

We now have our basic database, complete with tables and records to be used in conjuction with our PHP code which we will discuss on the next Workshop.