Example 1: Simple Web Page with MySQL connection

CREATE DATABASE itblogs DEFAULT CHARACTER SET utf8;

USE itblogs;

CREATE TABLE COMPANY(

id BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

background  VARCHAR(255) NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE SHOP (

id BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

telephone VARCHAR(50) NOT NULL,

fax VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL,

address VARCHAR(255) NOT NULL,

map_latitude DOUBLE,

map_longitude DOUBLE,

status  TINYINT(1),

PRIMARY KEY (id)

);

CREATE TABLE PROMOTION (

id BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

details VARCHAR(255) NOT NULL,

image_file VARCHAR(50) NOT NULL,

start_date TIMESTAMP NOT NULL,

end_date TIMESTAMP NOT NULL,

status  TINYINT(1),

PRIMARY KEY (id)

);

CREATE TABLE PRODUCT (

id BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

details VARCHAR(255) NOT NULL,

price DOUBLE,

status  TINYINT(1),

PRIMARY KEY (id)

);

CREATE TABLE CUSTOMER (

id BIGINT NOT NULL AUTO_INCREMENT,

firstname VARCHAR(50) NOT NULL,

lastname VARCHAR(50) NOT NULL,

telephone VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL,

age TINYINT UNSIGNED,

gender char(1),

username VARCHAR(50) NOT NULL,

password VARCHAR(50) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO COMPANY(name,background) VALUES (‘IT-Blogs.com’,’Welcome to IT-Blogs.com … Feel free to check with our latest promotions and products.’);

INSERT INTO SHOP(name,telephone,fax,email,address,map_latitude,map_longitude,status) VALUES (‘NewYork Shop’,'(US) 1111 1111′,'(US) 2222 2222′,’newyork@itblogs.com’,’2010 Scroggins Rd, Alexandria, VA 22302, USA’,38.822591,-77.080078,1);

INSERT INTO SHOP(name,telephone,fax,email,address,map_latitude,map_longitude,status) VALUES (‘Jiangsu Sheng Shop’,'(CN) 1111 1112′,'(CN) 2222 2223′,’china@itblogs.com’,’25 Chang Xing Lu, Zhangjiagang Shi, Suzhou Shi, Jiangsu Sheng, China’,31.900878,120.531006,1);

INSERT INTO SHOP(name,telephone,fax,email,address,map_latitude,map_longitude,status) VALUES (‘Hong Kong Shop’,'(HK) 1111 1113′,'(HK) 2222 2224′,’hongkong@itblogs.com’,’Hong Kong City Hall, 5 Edinburgh Pl, Central, Hong Kong’,22.2822646,114.16152750000003,1);

INSERT INTO SHOP(name,telephone,fax,email,address,map_latitude,map_longitude,status) VALUES (‘France Shop’,'(FR) 1111 1114′,'(FR) 2222 2225′,’france@itblogs.com’,’Les Reynès, 26760 Montéléger, France’,44.840291,4.921875,1);

INSERT INTO SHOP(name,telephone,fax,email,address,map_latitude,map_longitude,status) VALUES (‘Belgium Shop’,'(BE) 1111 1115′,'(BE) 2222 2226′,’belgium@itblogs.com’,’Rue de la Gare Fleurie 16, 5100 Namur, Belgium’,50.453569,4.87381,1);

INSERT INTO PROMOTION(name,details,image_file,start_date,end_date,status) VALUES (‘2016 Jan – promotion’,’Samsung Smart TV 50` big discount…’,’qr0001.png’,’2016-01-01 00:00:00′,’2016-02-01 00:00:00′,1);

INSERT INTO PROMOTION(name,details,image_file,start_date,end_date,status) VALUES (‘2016 Mar – promotion’,’Apple products big discount…’,’qr0002.png’,’2016-03-01 00:00:00′,’2016-04-01 00:00:00′,1);

INSERT INTO PROMOTION(name,details,image_file,start_date,end_date,status) VALUES (‘2016 Jun – promotion’,’MS Surface Pro big discount…’,’qr0003.png’,’2016-06-01 00:00:00′,’2016-07-01 00:00:00′,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘Samsung Smart TV 50`’,’Samsung Smart TV 50` details…’,10000,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘Samsung Notebook 15`’,’Samsung Notebook 15` details…’,6500,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘Apple iPad Air 2′,’Wi-Fi 16GB – Gold’,5000,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘Apple 15.4-inch MacBook Pro’,’2.5GHz Quad-core Intel i7 with Retina Display’,8000,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘Apple Mac Mini’,’1.4GHz dual-core Intel Core i5′,3000,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘MS Surface Pro 4′,’128GB / Intel Core m3 – 4GB RAM’,7050,1);

INSERT INTO PRODUCT(name,details,price,status) VALUES (‘HP ENVY 8 Note Edition Tablet’,’Intel Atom x5-Z8300,2GB memory/32GB eMMC’,860,1);

INSERT INTO CUSTOMER(firstname,lastname,telephone,email,age,gender,username,password) VALUES (‘Peter’,’Pan’,’1111 1111′,’peterpan@itblogs.com’,100,’M’,’peterpan’,md5(‘peterpan’));

INSERT INTO CUSTOMER(firstname,lastname,telephone,email,age,gender,username,password) VALUES (‘David’,’Pan’,’1111 1222′,’davidpan@itblogs.com’,60,’M’,’davidpan’,md5(‘davidpan’));

INSERT INTO CUSTOMER(firstname,lastname,telephone,email,age,gender,username,password) VALUES (‘Mary’,’Pan’,’1111 1333′,’marypan@itblogs.com’,20,’F’,’marypan’,md5(‘marypan’));

commit;

var express = require(‘express’);

var app = express();

//Setup app variables

app.set(‘port’, 3000);

//Declare MySQL library

var mysql = require(‘mysql’);

//Declare MySQL connection pool

var pool =  mysql.createPool({

host:                      ‘localhost’,

user:                      ‘root’,

password:           ‘root’,

database:            ‘itblogs’,

port:                      3306

});

//Define Controller

app.get(‘/’, function (req, res) {

var results = ‘Welcome to ‘;

//Making Query

pool.getConnection(function(err, connection){

connection.query( ‘SELECT name,background FROM company’, function(err, rows){

if(err)    {

throw err;

}else{

console.log( rows );

results += rows[0].name + ‘<br><br>’;

results += rows[0].background + ‘<br>’;

res.send(results);

}

});

connection.release();

});

});

//Setup Listener with Port app.get(‘port’)

app.listen(app.get(‘port’), function () {

console.log(‘Demo 1 listening on port ‘ + app.get(‘port’));

});