· 8 years ago · May 20, 2018, 11:40 PM
1<?php
2include 'config/db_connection.php';
3
4class SqlHandler {
5 public $server;
6 const username="root";
7 const password="";
8 const database="IP2";
9
10
11 function __construct() {
12 $this->server = $this->serverConnection();
13 }
14
15 private function serverConnection(){
16 $connection = mysql_connect("localhost", self::username,self::password)
17 or die("Unable to connect to sql server");
18 return $connection;
19 }
20
21 /*
22 * CREATE / DROP DATABASE SECTION
23 */
24
25 function dropDatabase(){
26 $drop_database_query = "DROP DATABASE IF EXISTS ".self::database;
27 mysql_query($drop_database_query) or die(mysql_error());
28 }
29
30 function createDatabase(){
31 $create_database_query = "CREATE DATABASE IF NOT EXISTS ".self::database;
32 mysql_query($create_database_query,$this->server) or die(mysql_error());
33 mysql_select_db(self::database) or die("Unable to select database");
34 }
35
36
37 /*
38 * CREATE TABLES SECTION
39 */
40
41 /**
42 * Creates all tables.
43 */
44
45 function createTbls(){
46 $this->createUserTbl();
47 $this->createProductTbl();
48 $this->createCustomerTbl();
49 $this->createPurchaseTbl();
50 $this->createPersonTbl();
51 }
52
53 function createPersonTbl(){
54 $create_person_tbl_query ="CREATE TABLE IF NOT EXISTS userTbl(
55 id INT NOT NULL AUTO_INCREMENT,
56 name TEXT,
57 address TEXT,
58 phone_number INT,
59 customer_id INT,
60 PRIMARY KEY(id))ENGINE=InnoDB";
61
62 mysql_select_db(self::database) or die("Unable to select database");
63 mysql_query($create_person_tbl_query,$this->server) or die(mysql_error());
64 }
65
66 function createCustomerTbl(){
67 $create_customer_tbl_query ="CREATE TABLE IF NOT EXISTS customerTbl(
68 id INT NOT NULL AUTO_INCREMENT,
69 PRIMARY KEY(id))ENGINE=InnoDB";
70
71 mysql_select_db(self::database) or die("Unable to select database");
72 mysql_query($create_customer_tbl_query,$this->server) or die(mysql_error());
73 }
74
75 function createUserTbl(){
76 $create_user_tbl_query = "CREATE TABLE IF NOT EXISTS userTbl(
77 id INT NOT NULL AUTO_INCREMENT,
78 name TEXT,
79 password TEXT,
80 PRIMARY KEY(id),
81 pid INT)ENGINE=InnoDB";
82 mysql_select_db(self::database) or die("Unable to select database");
83 mysql_query($create_user_tbl_query,$this->server) or die(mysql_error());
84 }
85
86 function createPurchaseRowTbl(){
87 $create_purchase_row_query = "CREATE TABLE IF NOT EXISTS purchaserowtbl (
88 purchase_id INT,
89 product_id INT)ENGINE=InnoDB";
90
91 mysql_select_db(self::database) or die("Unable to select database");
92 mysql_query($create_purchase_row_query,$this->server) or die(mysql_error());
93 }
94
95 function createPurchaseTbl(){
96 $create_purchase_tbl_query = "CREATE TABLE IF NOT EXISTS purchaseTbl(
97 id INT NOT NULL AUTO_INCREMENT,
98 timestamp TIMESTAMP,
99 customer_id INT,
100 PRIMARY KEY(id))ENGINE=InnoDB";
101
102 mysql_select_db(self::database) or die("Unable to select database");
103 mysql_query($create_purchase_tbl_query,$this->server) or die(mysql_error());
104 }
105
106 function createProductTbl(){
107 $create_product_tbl_query = "CREATE TABLE IF NOT EXISTS productTbl(
108 id INT NOT NULL AUTO_INCREMENT,
109 name TEXT,
110 price FLOAT,
111 imgpath TEXT,
112 purchase_id INT,
113 PRIMARY KEY(id))ENGINE=InnoDB;";
114 mysql_select_db(self::database) or die("Unable to select database");
115 mysql_query($create_product_tbl_query,$this->server) or die(mysql_error());
116 }
117
118 /*
119 *INSERT VALUES SECTION
120 */
121
122 /**
123 *Creates a user in userTbl.
124 * @param type $name The user name.
125 * @param type $password User password.
126 */
127 function createUser($name, $password){
128 $create_user_query = "INSERT INTO userTbl (name,password)
129 VALUES ('$name','$password')";
130 mysql_select_db(self::database) or die(mysql_error());
131 mysql_query($create_user_query,$this->server) or die(mysql_error());
132 }
133
134 function createCustomer(){
135 $create_customer_query = "INSERT INTO customerTbl VALUES()";
136 mysql_select_db(self::database) or die(mysql_error());
137 mysql_query($create_customer_query,$this->server) or die(mysql_error());
138 }
139
140 function createPurchase($customer_id){
141 $create_purchase_query = "INSERT INTO purchaseTbl (customer_id,timestamp)
142 VALUES('$customer_id','TIMESTAMP()')";
143 mysql_select_db(self::database) or die(mysql_error());
144 mysql_query($create_purchase_query,$this->server) or die(mysql_error());
145 }
146
147 function createPurchaseRow($purchase_id,$product_id){
148 $create_purchase_query = "INSERT INTO purchaseRowTbl (purchase_id,product_id)
149 VALUES('$purchase_id','$product_id')";
150 }
151
152 /**
153 *Creates a Product in productTbl.
154 * @param string $name name
155 * @param float $price price
156 * @param string $description description
157 * @param int $purchase_id id from purchase
158 * @param string $imgpath path to the image. Default value is "false".
159 */
160 function createProduct($name,$price,$description,$imgpath="false",$purchase_id = NULL){
161 $create_product = "INSERT INTO productTbl (name,price,imgpath,purchase_id)
162 VALUES ('$name','$price','$imgpath','$purchase_id')";
163 mysql_select_db(self::database) or die(mysql_error());
164 mysql_query($create_product,$this->server) or die(mysql_error());
165 }
166
167 /*
168 * SELECT VALUES SECTION
169 */
170
171 /**
172 * SELECT * FROM $table.
173 * @return Array Returns an Associative array with [column] -> value from $table.
174 */
175 function selectAllFromTable($table){
176 $select_users= "SELECT * FROM $table";
177 mysql_select_db(self::database) or die("Unable to select database");
178 $users = mysql_query($select_users,$this->server) or die(mysql_error());
179 return $users;
180 }
181}
182
183?>