public static function create() { $connect = new mysqli('localhost', 'root', ''); // Check connection if ($connect->connect_error) { die("Connection failed: " . $connect->connect_error); } // Create database $sql = "CREATE DATABASE IF NOT EXISTS Cafeteria_DataBase"; if ($connect->query($sql) === FALSE) { echo "Error creating database: " . $connect->error; } connection::$obj = mysqli_connect('localhost', 'root', '', 'Cafeteria_DataBase'); if (mysqli_connect_errno()) { echo 'Error: Could not connect to database. Please try again later.'; } ////create table users $sq = "CREATE TABLE IF NOT EXISTS Users (uid int unsigned not null auto_increment primary key,name char(50) not null,email char(50) not null,password char(50) not null,profilePicture char(50) not null,EXT char(50) not null,rid int unsigned not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } ////create table products $sq = "CREATE TABLE IF NOT EXISTS Products (pid int unsigned not null auto_increment primary key,pname char(50) not null,productPicture char(50) not null,price int not null,available enum('0','1') not null,cid int unsigned not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } ////create table Category $sq = "CREATE TABLE IF NOT EXISTS Category (cid int unsigned not null auto_increment primary key,categoryName char(50) not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } ////create table Room $sq = "CREATE TABLE IF NOT EXISTS Room (rid int unsigned not null auto_increment primary key,roomNumber int not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } //////////////////////////////////////////// ////////////////////--Rooms--/////////////// for ($i = 2000, $j = 1; $i < 2020; $i += 1, $j++) { $query = "insert into Room values({$j},'" . $i . "');"; mysqli_query(connection::$obj, $query); } /////////////--adminRecord--//////////// $query = "insert into Users values(1,'admin','*****@*****.**',md5('123'),'admin.jpg','1010',1);"; mysqli_query(connection::$obj, $query); /////////////////////////////////// //////////////////////////////////////////// ////create table Orders $sq = "CREATE TABLE IF NOT EXISTS Orders (oid int unsigned not null auto_increment primary key,uid int unsigned not null,orderDate date not null,amount int not null,status char(50) not null,comment char(100) not null,rid int unsigned not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } /////create table orderProducts $sq = "CREATE TABLE IF NOT EXISTS orderProducts (oid int unsigned not null,pid int unsigned not null,numofItems int not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } ///////////////////////////////////////////////////create table change $sq = "CREATE TABLE IF NOT EXISTS changes (tableName char(20) not null UNIQUE ,changeDate date not null,serverDate date not null);"; if (connection::$obj->query($sq) === FALSE) { echo "Error creating table: " . connection::$obj->error; } /////////////--change table data--//////////// $query = "insert into changes values('Users',NOW(),NOW());"; mysqli_query(connection::$obj, $query); $query = "insert into changes values('Products',NOW(),NOW());"; mysqli_query(connection::$obj, $query); $query = "insert into changes values('Category',NOW(),NOW());"; mysqli_query(connection::$obj, $query); $query = "insert into changes values('Room',NOW(),NOW());"; mysqli_query(connection::$obj, $query); $query = "insert into changes values('Orders',NOW(),NOW());"; mysqli_query(connection::$obj, $query); ///////////////////////create trigger to user////////////*************** $query = "DELIMITER //\n\t\t\tCREATE TRIGGER changes BEFORE INSERT ON\n\t\t\tUsers FOR EACH ROW BEGIN\n\t\t\t\t\t\tUPDATE changes\n\t\t\tSET changeDate = NOW() where tableName='Users'\n\t\t\tEND //\n\t\t\tDELIMITER ;"; mysqli_query(connection::$obj, $query); ///////////////////////create trigger to products////////////*************** $query = "DELIMITER //\n\t\t\tCREATE TRIGGER changes BEFORE INSERT ON\n\t\t\tProducts FOR EACH ROW BEGIN\n\t\t\t\t\tUPDATE changes\n\t\t\tSET changeDate = NOW() where tableName='Products'\n\t\t\tEND //\n\t\t\tDELIMITER ;"; mysqli_query(connection::$obj, $query); ///////////////////////create trigger to category////////////*************** $query = "DELIMITER //\n\t\t\tCREATE TRIGGER changes BEFORE INSERT ON\n\t\t\tCategory FOR EACH ROW BEGIN\n\t\t\t\t\t\tUPDATE changes\n\t\t\tSET changeDate = NOW() where tableName='Category'\n\t\t\tEND //\n\t\t\tDELIMITER ;"; mysqli_query(connection::$obj, $query); ///////////////////////create trigger to Room////////////*************** $query = "DELIMITER //\n\t\t\tCREATE TRIGGER changes BEFORE INSERT ON\n\t\t\tRoom FOR EACH ROW BEGIN\n\t\t\t\t\t\tUPDATE changes\n\t\t\tSET changeDate = NOW() where tableName='Room'\n\t\t\tEND //\n\t\t\tDELIMITER ;"; mysqli_query(connection::$obj, $query); //////////////////////create trigger to Orders////////////*************** $query = "DELIMITER //\n\t\t\tCREATE TRIGGER changes BEFORE INSERT ON\n\t\t\tOrders FOR EACH ROW BEGIN\n\t\t\t\t\t\tUPDATE changes\n\t\t\tSET changeDate = NOW() where tableName='Orders'\n\t\t\tEND //\n\t\t\tDELIMITER ;"; mysqli_query(connection::$obj, $query); /////////////////add relationship between tables $sq = "alter table Users add foreign key (rid) references Room (rid);"; connection::$obj->query($sq); $sq = "alter table Products add foreign key (cid) references Category (cid);"; connection::$obj->query($sq); $sq = "alter table Orders add foreign key (uid) references Users (uid);"; connection::$obj->query($sq); $sq = "alter table Orders add foreign key (rid) references Room (rid);"; connection::$obj->query($sq); $sq = "alter table orderProducts add foreign key (oid) references Orders (oid);"; connection::$obj->query($sq); $sq = "alter table orderProducts add foreign key (pid) references Products (pid);"; connection::$obj->query($sq); $sq = "alter table orderProducts add primary key (oid,pid);"; connection::$obj->query($sq); }