Database for Simple Web Development Project with Perl and MySQL
Web Development Basics with Perl and MySQL – Part 5
Using Apache Web Server
Foreword: 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: 28 Apr 2016
Introduction
I 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, I 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 PurePerl MySQL API that is becoming more and more famous (as it makes your life convenient). For simplicity, you will use the API at the console (Prompt or DOS Window). However, the web pages and Perl scripts will use the API from the web server.
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 Perl 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 practice you should use some other account with some root privileges, for this, for security). 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.
Start the MySQL5 server (which has been configured to work with the server, if it is not already started) in a command prompt window with a command, similar to:
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" –console
If you can start the server in some other way or if it is already started, no problem; in that case you do not have to use this command.
Your data definition SQL statements will be in a text editor file. So, create an empty text editor file in drive c (C:\) with the name, clientmysql.pl . Your connection code to the API and the SQL statements will be in this file, as I go on to show you. At this point I assume you have already done the installation of the API, web server, and MySQL server.
So, start by typing the following in your file:
#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;
if (!Mysql::connect("root", "xxxxxx", "localhost", 3306))
{
print $Mysql::Error_msg;
}
else
{
my $crdb = "create database Bookshop";
if (!Mysql::query($crdb))
{
print $Mysql::Error_msg, "\n";
}
else
{
#select and use database
if (!Mysql::select_db("Bookshop"))
{
print $Mysql::Error_msg, "\n";
}
else
{
}
}
Mysql::close();
}
The database is called Bookshop as you should have deduced from the above code. All your code will go into the else block for the select_db() function. You connect as the root; replace the xxxxxx with your own password.
Note, with Apache server, whether you are using the Windows Operating system or not, the line like “#!C:/Perl/bin/perl5.18.2.exe” should be in the script.
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, include the following Products code into the above:
my $crtbl = "create table Products
(
ProductID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
ProductName CHAR(40),
Category CHAR(30),
categoryItemNo smallint NOT NULL,
quantityAvailable int,
imageFileName varchar(30),
imageDir varchar(30),
shortSummary varchar(175),
explanation varchar(1000),
price DECIMAL(19,2)
) ENGINE = INNODB";
if (!Mysql::query($crtbl))
{
print $Mysql::Error_msg, "\n";
}
my $index = "CREATE INDEX pNameCategory ON Products (ProductName, Category)";
if (!Mysql::query($index))
{
print $Mysql::Error_msg, "\n";
}
The Sales table has an AUTO_INCREMENT primary key, a date-and-time column (timestamp) when the sale was made, a memberID column and an employeeID column. For this simple project, the employeeID column can have NULL data. Include the following Sales table code into the above code:
my $crtblS = "create table Sales
(
saleID INTEGER NOT NULL AUTO_INCREMENT,
dateAndTime TIMESTAMP,
memberID INTEGER NOT NULL,
employeeID INTEGER,
PRIMARY KEY (SaleID ASC)
) ENGINE = INNODB";
if (!Mysql::query($crtblS))
{
print $Mysql::Error_msg, "\n";
}
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. Include the following SaleItems table code into the above code:
my $crtblSI = "create table SaleItems
(
saleID INTEGER NOT NULL,
productID INTEGER NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (saleID, productID)
) ENGINE = INNODB";
if (!Mysql::query($crtblSI))
{
print $Mysql::Error_msg, "\n";
}
In the Members table, each row has the credentials for a registered member. Include the following Members table code into the above code:
my $crtblM = "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";
if (!Mysql::query($crtblM))
{
print $Mysql::Error_msg, "\n";
}
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 that table, during registration.
Let us insert some data into the products table. Include the following code into the above code:
my @arr;
$arr[0] = "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)";
$arr[1] = "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)";
$arr[2] = "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)";
$arr[3] = "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)";
$arr[4] = "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)";
$arr[5] = "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)";
$arr[6] = "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)";
$arr[7] = "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)";
$arr[8] = "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)";
$arr[9] = "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)";
$arr[10] = "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)";
$arr[11] = "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)";
$arr[12] = "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)";
$arr[13] = "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)";
$arr[14] = "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)";
$arr[15] = "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)";
foreach my $i (0..$#arr)
{
if (!Mysql::query($arr[$i]))
{
print $Mysql::Error_msg, "\n";
}
}
If you have done all that correctly, then you can go on to run the file by typing
clientmysql.pl
and pressing Enter at the C:\> prompt.
You will be able to download all the code in this part of the series and more, at the end of the series.
The TRIGGER Privilege
In a commercial project, the registered user will need to have the TRIGGER privilege for some tables. Triggers are used to automatically update certain tables. For example, if 3 of a particular item are bought, the number 3 has to be subtracted from the corresponding quantityAvailable cell in the products table. However, to keep things simple, I do not address triggers in this series. I did not even consider the quantityAvailable column of the products table when the values of the products tables were inserted. For the sake of pedagogy and to keep things simple, just allow the situation like that in this series (project).
With that we come to the end of this part of the series. We take a break here and continue in the next part.
Chrys
Related Links
Web Development Basics with Perl and MySQLPerl Validation of HTML Form Data
Page Views with Ajax and Perl and MySQL
Web Live Text Chart Application using Perl and MySQL
Search Within a Site using Perl and MySQL
More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT