DB-SmartHouse/index.php at master · AUTProjects/DB-SmartHouse · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
<?php

$servername = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "smarthouse";

// Create connection
$conn = new mysqli($servername, $username, $password);
//create DB

$sql = "CREATE DATABASE " . $dbname;
$conn->query($sql);

echo "</br>";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    echo "</br>";
}


//create Table user
$sql = "CREATE TABLE user (
username VARCHAR(20) PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
password VARCHAR(20)
);";

$conn->query($sql);

//create Table Product Type
$sql = "CREATE TABLE product_type (
type_code INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(30) NOT NULL
)";

$conn->query($sql);

//create Table product stock
$sql = "CREATE TABLE product_stock (
product_code VARCHAR(6)   PRIMARY KEY,
type_code INT(6) UNSIGNED NOT NULL,
price INT(20) NOT NULL,
stock INT(20) DEFAULT '0',
FOREIGN KEY (type_code) REFERENCES product_type(type_code)
)";

$conn->query($sql);

//create Table sell
$sql = "CREATE TABLE sell (
order_number INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
product_code VARCHAR(6) ,
username VARCHAR(20),
FOREIGN KEY (product_code) REFERENCES product_stock(product_code),
FOREIGN KEY (username) REFERENCES user(username)
)";

$conn->query($sql);

//create Table light sensor
$sql = "CREATE TABLE light_sensor (
order_number INT(6) UNSIGNED ,
report_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  ,
light_intensity INT(6) UNSIGNED,
base_light_intensity INT(6) UNSIGNED,
FOREIGN KEY (order_number) REFERENCES sell(order_number),
PRIMARY KEY (order_number,report_time)
)";

$conn->query($sql);

//create Table temperature sensor
$sql = "CREATE TABLE temperature_sensor (
order_number INT(6) UNSIGNED ,
report_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  ,
temperature INT(3) UNSIGNED,
FOREIGN KEY (order_number) REFERENCES sell(order_number),
PRIMARY KEY (order_number,report_time)
)";

$conn->query($sql);

//create Table humidity sensor
$sql = "CREATE TABLE humidity_sensor (
order_number INT(6) UNSIGNED ,
report_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  ,
humidity INT(3) UNSIGNED,
FOREIGN KEY (order_number) REFERENCES sell(order_number),
PRIMARY KEY (order_number,report_time)
)";

$conn->query($sql);

//create Table gas sensor
$sql = "CREATE TABLE gas_sensor (
order_number INT(6) UNSIGNED ,
report_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  ,
co2 INT(3) UNSIGNED,
co INT(3) UNSIGNED,
ch4 INT(3) UNSIGNED,
FOREIGN KEY (order_number) REFERENCES sell(order_number),
PRIMARY KEY (order_number,report_time)
)";

$conn->query($sql);


//create Table gas sensor
$sql = "INSERT INTO `product_type` VALUES (1,'light sensor')";
$conn->query($sql);

$sql = "INSERT INTO `product_type` VALUES (2,'temperature sensor')";
$conn->query($sql);

$sql = "INSERT INTO `product_type` VALUES (3,'humidity sensor')";
$conn->query($sql);

$sql = "INSERT INTO `product_type` VALUES (4,'gas sensor')";
$conn->query($sql);


$conn->close();

header("Location: ./home.php"); /* Redirect browser */
exit();
?>