Broad Network


Database for Simple Web Development Project with ECMAScript and MySQL

Web Development Basics with ECMAScript and MySQL – Part 5

Web Development with ECMAScript and MySQL in Node.js

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: 16 Jul 2016

Introduction

This is part 5 of my series, Web Development Basics with ECMAScript and MySQL. In this part of the series, you will create a database for the web development project described in the previous part of the series. I assume you have read the previous parts of the series, before reaching here; this is a continuation.

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 EMySQL API. For simplicity, you will use the API at the console (Prompt or DOS Window). However, the web pages and ECMAScript 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 (subscribes), 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 ECMAScript registration server 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.

Project Begins
Start the MySQL5.x 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.js . 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.

In the clientmysql.js file, start by typing the following:

const mysql = require('./server/Mysql.js');

    con = new mysql.Connection("root", "secret", "localhost", 3306, function (err)
            {
                if (err)
                    {
                        errMsg = "Connection to database could not be made. See webmaster";
                        console.log(errMsg);
                        return;
                    }
            });

    var crdb = "create database Bookshop";
    con.query(crdb, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

The database is called Bookshop as you should have deduced from the above code. You connect as the root; replace the 'secret' with your own password. The connect and query function each has a callback function. Such callback function is called from the (MySQL) module and not from the script. err means error.

Selecting a Database
A query to select a database (Bookshop) is:

   con.query("USE Bookshop", function(err)
            {
                if (err)
                   {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                   }
                });

Add this code to the bottom of the above code.

The Products Table
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:

   var 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`;
    con.query(crtbl, function(err)
            {
                if (err)
                   {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                   }
                });

    var indx = `CREATE INDEX pNameCategory ON Products (ProductName, Category)`;
    con.query(indx, function(err)
            {
                if (err)
                   {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                   }
             });

Note that the query strings are in back ticks. Do not forget to add this code to the bottom of the above.

The Sales Table
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:

    var crtblS = `create table Sales
        (
            saleID INTEGER NOT NULL AUTO_INCREMENT,
            dateAndTime TIMESTAMP,
            memberID INTEGER NOT NULL,
            employeeID INTEGER,

            PRIMARY KEY (SaleID ASC)
        ) ENGINE = INNODB`;
    con.query(crtblS, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

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:

    var crtblSI = `create table SaleItems
        (
            saleID INTEGER NOT NULL,
            productID INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
    
            PRIMARY KEY (saleID, productID)
        ) ENGINE = INNODB`;
    con.query(crtblSI, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

The Members Table
In the Members table, each row has the credentials for a registered member. Include the following Members table code into the above code:

    var 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`;
    con.query(crtblM, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

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.

Data for the Products Table
Let us insert some data into the Products table. Include the following code into the above code:

    var arr = new Array();
        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)";
        
        for (item of arr)
            con.query(item, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

    con.close();

If you have done all that correctly, then you can go on to run the file by typing

    node clientmysql.js

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 Members Table
In the Members table, each row has the credentials for a registered member. Include the following Members table code into the above code:

    var 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`;
    con.query(crtblM, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

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.

Data for the Products Table
Let us insert some data into the Products table. Include the following code into the above code:

    var arr = new Array();
        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)";
        
        for (item of arr)
            con.query(item, function(err)
            {
                if (err)
                    {
                        errMsg = "Could not execute command. See webmaster.";
                        console.log(errMsg);
                        return;
                    }
                });

    con.close();

If you have done all that correctly, then you can go on to run the file by typing

    node clientmysql.js

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 ECMAScript and MySQL
ECMAScript Validation of HTML Form Data
Web Live Text Chart Application using ECMAScript and MySQL
More Related Links
Node Mailsend
EMySQL API
Node.js Web Development Course
Major in Website Design
Low Level Programming - Writing ECMAScript Module
ECMAScript Course

BACK NEXT

Comments

Become the Writer's Follower
Send the Writer a Message