forked from CallmeRebeca/Taskboard
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb_connection.php
153 lines (142 loc) · 5.45 KB
/
db_connection.php
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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
<?php
$db_hostname="127.0.0.1:3306";
$db_username="root";
$db_password="";
$database="taskboard";
function initializeDatabase() {
$db_hostname="127.0.0.1:3306";
$db_username="root";
$db_password="";
$database="taskboard";
$connection = mysqli_connect($db_hostname, $db_username, $db_password);
if(!$connection) {
echo"Database Connection Error...".mysqli_connect_error();
} else {
$sql = 'CREATE Database IF NOT EXISTS ' . $database;
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create database...".mysqli_connect_error();
}
$sql = "CREATE Table IF NOT EXISTS $database.Skills (".
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,".
"skill VARCHAR(30) NOT NULL)";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create table Skills".mysqli_error($connection);
} else {
$sql = "SELECT * FROM $database.Skills";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo "".mysqli_connect_error();
}
$count = mysqli_num_rows($retval);
if($count == 0) {
$sql = "INSERT INTO $database.Skills (skill) VALUES ('C')";
mysqli_query( $connection, $sql );
$sql = "INSERT INTO $database.Skills (skill) VALUES ('C++')";
mysqli_query( $connection, $sql );
$sql = "INSERT INTO $database.Skills (skill) VALUES ('Java')";
mysqli_query( $connection, $sql );
}
}
$sql = "CREATE Table IF NOT EXISTS $database.WorkingHours (".
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,".
"hour VARCHAR(10) NOT NULL)";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create table WorkingHours".mysqli_error($connection);
} else {
$sql = "SELECT * FROM $database.WorkingHours";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo "".mysqli_connect_error();
}
$count = mysqli_num_rows($retval);
if($count == 0) {
$sql = "INSERT INTO $database.WorkingHours (hour) VALUES ('4h/day')";
mysqli_query( $connection, $sql );
$sql = "INSERT INTO $database.WorkingHours (hour) VALUES ('6h/day')";
mysqli_query( $connection, $sql );
$sql = "INSERT INTO $database.WorkingHours (hour) VALUES ('8h/day')";
mysqli_query( $connection, $sql );
}
}
$sql = "CREATE Table IF NOT EXISTS $database.TaskStatus (".
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,".
"task_status VARCHAR(15) NOT NULL)";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create table TaskStatus".mysqli_error($connection);
} else {
$sql = "SELECT * FROM $database.TaskStatus";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo "".mysqli_connect_error();
}
$count = mysqli_num_rows($retval);
if($count == 0) {
$sql = "INSERT INTO $database.TaskStatus (task_status) VALUES ('Todo')";
mysqli_query( $connection, $sql );
$sql = "INSERT INTO $database.TaskStatus (task_status) VALUES ('In progress')";
mysqli_query( $connection, $sql );
$sql = "INSERT INTO $database.TaskStatus (task_status) VALUES ('Done')";
mysqli_query( $connection, $sql );
}
}
$sql = "CREATE Table IF NOT EXISTS $database.SkillLevel (".
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,".
"skill_level VARCHAR(10) NOT NULL)";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create table SkillLevel".mysqli_error($connection);
} else {
$sql = "SELECT * FROM $database.SkillLevel";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo "".mysqli_connect_error();
}
$count = mysqli_num_rows($retval);
if($count == 0) {
for ($x = 1; $x <= 10; $x++) {
$sql = "INSERT INTO $database.SkillLevel (skill_level) VALUES ('Level $x')";
mysqli_query( $connection, $sql );
}
}
}
$sql = "CREATE Table IF NOT EXISTS $database.TeamMembers (".
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,".
"first_name VARCHAR(20) NOT NULL,".
"last_name VARCHAR(20) NOT NULL,".
"email VARCHAR(30) NOT NULL,".
"password VARCHAR(20) NOT NULL,".
"skill INT NOT NULL,".
"skill_level INT NOT NULL,".
"work_hours INT NOT NULL,".
"role VARCHAR(10) NOT NULL,".
"CONSTRAINT fk_skill FOREIGN KEY (skill) REFERENCES Skills(id),".
"CONSTRAINT fk_nivel_skill FOREIGN KEY (skill_level) REFERENCES SkillLevel(id),".
"CONSTRAINT fk_work_hours FOREIGN KEY (work_hours) REFERENCES WorkingHours(id))";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create table TeamMembers".mysqli_error($connection);
}
$sql = "CREATE Table IF NOT EXISTS $database.Tasks (".
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,".
"task_name VARCHAR(30) NOT NULL,".
"skill_required INT NOT NULL,".
"level_required INT NOT NULL,".
"duration INT NOT NULL,".
"task_status INT NOT NULL,".
"assigned_member INT NOT NULL,".
"CONSTRAINT fk_skill_required FOREIGN KEY (skill_required) REFERENCES Skills(id),".
"CONSTRAINT fk_level_required FOREIGN KEY (level_required) REFERENCES SkillLevel(id),".
"CONSTRAINT fk_task_status FOREIGN KEY (task_status) REFERENCES TaskStatus(id),".
"CONSTRAINT fk_assigned_member FOREIGN KEY (assigned_member) REFERENCES TeamMembers(id))";
$retval = mysqli_query( $connection, $sql );
if(! $retval ) {
echo"Could not create table Tasks".mysqli_error($connection);
}
}
mysqli_close($connection);
}
?>