Database for Simple Web Development Project with PHP and MySQL
Conventional Web Development with PHP and MySQL – Part 4
Forward: In this part of the series, you will create a database for the web development project described in the previous part of the series.
By: Chrysanthus Date Published: 2 Aug 2012
Introduction
We shall make things simple for this pedagogic project: The database will have just the Members table, the Products table, the Sales table, and the SaleItems table. For simplicity, we shall not track ordering of items (from suppliers) and inventory (number of a particular item in stock, reorder and minimum level). This is a pedagogic project, and we shall work only with the tables mentioned in this paragraph. By the time you complete this series, you should be able to do a commercial project adding the other tables and other database issues.
Note: you will create the database and tables (as root) using the mysql client program.
Registration
Before the database starts being used, it would have the tables, Members, Products, Sales and SaleItems. When a member registers, the root MySQL user will create an account name and password for the user in the server. This will be done automatically. In other words, the PHP registration script will connect to the database using the user name and password of the root. After that the root will give the user (client), certain privileges to the tables, automatically, by the script. In this simple project, the student (user) will not have the UPDATE or DELETE privilege for any table. The client (student) will also not have any trigger privilege for any table. I will tell you the particular privileges for particular tables that the user will have as we go along (in this part of the series).
Project Begins
Start your personal web server. Start the MySQL5 server (which has been configured to work with the server) in a command prompt window with a command, similar to:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --console
In a new command prompt window, start the mysql client program, login as root, with a command similar to:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -h localhost -u root -ppsswrd
The database will be called Bookshop. So create and select Bookshop as follows, in the root mysql client window (do not forget to press the Enter key after the second statement below):
create database Bookshop;
use Bookshop;
The products table has an AUTO-INCREMENT primary key. It has a column for productName, a column for category, a column for image filename, a column for the directory in the server that has the image file and a column for the price. This table also has a column for the item number (position in the table) in a particular category; the name of this column is categoryItemNo . Let us allow things that simple. So, create the Products table as follows (do not forget to press the Enter key after the last line):
create table Products
(
productID INTEGER NOT NULL AUTO_INCREMENT,
productName varchar(40),
category varchar(30),
categoryItemNo smallint NOT NULL,
quantityAvailable int,
imageFileName varchar(30),
imageDir varchar(30),
shortSummary varchar(175),
explanation varchar(1000),
price DECIMAL(19,2),
PRIMARY KEY (productID ASC),
INDEX (productName),
INDEX (category)
) ENGINE = INNODB;
The Sales Table
The Sales table has an AUTO_INCREMENT primary key, a date-and-time column when the sale was made, a memberID column and an employeeID column. For this simple project, the employeeID column can have NULL data. Create the Sales table with the following code:
create table Sales
(
saleID INTEGER NOT NULL AUTO_INCREMENT,
dateAndTime TIMESTAMP,
memberID INTEGER NOT NULL,
employeeID INTEGER,
PRIMARY KEY (SaleID ASC)
) ENGINE = INNODB;
The SaleItems Table
The SaleItems table has as primary (composite) key, saleID and productID. It has a column for the quantity of a particular item sold. Create the table as follows:
create table SaleItems
(
saleID INTEGER NOT NULL,
productID INTEGER NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (saleID, productID)
) ENGINE = INNODB;
In the Members table, each row has the credentials for a registered member. Create the Members table using the following code:
create table Members
(
memberID int not null auto_increment,
registeredDate timestamp not null,
username varchar(20),
email varchar(40) not null,
phone varchar(12),
firstname varchar(20) NOT NULL,
middlename varchar(20),
lastname varchar(20) not null,
university varchar(40),
address varchar(60),
city varchar(30),
state varchar(30),
country varchar(40),
PRIMARY KEY (memberID)
) ENGINE = INNODB;
In the table, the address, city, and state fields give the location where the purchase items will be posted. The rest of the column names in the table are self-explanatory. Note that the user name and password are not recorded in this table. They are recorded in a table in a database called mysql. This database is created and managed by the server. The server inserts the user name and password of a new user into the table, during registration.
Let us insert some data into the products table. Execute the following statements:
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book A", "Book", 1, "BookA.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book B", "Book", 2, "BookB.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book C", "Book", 3, "BookC.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book D", "Book", 4, "BookD.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book E", "Book", 5, "BookE.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book F", "Book", 6, "BookF.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book G", "Book", 7, "BookG.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Book H", "Book", 8, "BookH.jpg", "itemsDir", "A short note …", "Some fine details…", 9.99);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Blue Pen", "Pen", 1, "BluePen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Red Pen", "Pen", 2, "RedPen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Black Pen", "Pen", 3, "BlackPen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Green Pen", "Pen", 4, "GreenPen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Yellow Pen", "Pen", 5, "YellowPen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Purple Pen", "Pen", 6, "PurplePen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Orange Pen", "Pen", 7, "OrangePen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
insert into Products (productName, category, categoryItemNo, imageFileName, imageDir, shortSummary, explanation, price) VALUES ("Pink Pen", "Pen", 8, "PinkPen.jpg", "itemsDir", "A short note …", "Some fine details…", 1.50);
If you have done that, then you can close the command prompt windows (close the mysql client program and the server first).
With that we reach the end of this part of the series. We take a break here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT