· 10 years ago · May 02, 2016, 05:21 AM
1<?php
2function createdatabase() {
3$servername = "localhost";
4$username = "root";
5$password = "";
6
7// Create connection
8$conn = mysqli_connect($servername, $username, $password);
9// Check connection
10if (!$conn) {
11 die("Connection failed: " . mysqli_connect_error());
12}
13
14// Create database
15$sql = "CREATE DATABASE csc8417test";
16if (mysqli_query($conn, $sql)) {
17 echo "Database created successfully";
18} else {
19 echo "Error creating database: " . mysqli_error($conn);
20}
21
22mysqli_close($conn);
23}
24
25function createcharactor() {
26$servername = "localhost";
27$username = "root";
28$password = "";
29$dbname = "csc8417test";
30
31// Create connection
32$conn = new mysqli($servername, $username, $password, $dbname);
33// Check connection
34if ($conn->connect_error) {
35 die("Connection failed: " . $conn->connect_error);
36}
37
38// sql to create table
39
40
41$sql = "CREATE TABLE IF NOT EXISTS `charactor` (
42 `charactor_name` varchar(20) NOT NULL,
43 `vault_number` int(3) NOT NULL,
44 `history` text NOT NULL
45) ENGINE=InnoDB DEFAULT CHARSET=latin1";
46
47if ($conn->query($sql) === TRUE) {
48 echo "Table charactor created successfully";
49} else {
50 echo "Error creating charactor table: " . $conn->error;
51}
52
53$conn->close();
54
55}
56
57function creategames() {
58$servername = "localhost";
59$username = "root";
60$password = "";
61$dbname = "csc8417test";
62
63// Create connection
64$conn = new mysqli($servername, $username, $password, $dbname);
65// Check connection
66if ($conn->connect_error) {
67 die("Connection failed: " . $conn->connect_error);
68}
69
70// sql to create table
71
72
73$sql = "CREATE TABLE IF NOT EXISTS `games` (
74 `charactor_name` varchar(20) NOT NULL,
75 `vault_number` int(3) NOT NULL,
76 `group_name` varchar(20) NOT NULL
77) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
78
79if ($conn->query($sql) === TRUE) {
80 echo "Table games created successfully";
81} else {
82 echo "Error creating games table: " . $conn->error;
83}
84
85$conn->close();
86
87}
88
89function creategroups() {
90$servername = "localhost";
91$username = "root";
92$password = "";
93$dbname = "csc8417test";
94
95// Create connection
96$conn = new mysqli($servername, $username, $password, $dbname);
97// Check connection
98if ($conn->connect_error) {
99 die("Connection failed: " . $conn->connect_error);
100}
101
102// sql to create table
103
104
105$sql = "CREATE TABLE IF NOT EXISTS `groups` (
106 `group_name` varchar(20) NOT NULL,
107 `history` text NOT NULL,
108 `vault_number` int(3) NOT NULL
109) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
110
111if ($conn->query($sql) === TRUE) {
112 echo "Table groups created successfully";
113} else {
114 echo "Error creating groups table: " . $conn->error;
115}
116
117$conn->close();
118
119}
120
121function createhistory() {
122$servername = "localhost";
123$username = "root";
124$password = "";
125$dbname = "csc8417test";
126
127// Create connection
128$conn = new mysqli($servername, $username, $password, $dbname);
129// Check connection
130if ($conn->connect_error) {
131 die("Connection failed: " . $conn->connect_error);
132}
133
134// sql to create table
135
136
137$sql = "CREATE TABLE IF NOT EXISTS `history` (
138 `History` text NOT NULL,
139 `date` date NOT NULL
140) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
141
142if ($conn->query($sql) === TRUE) {
143 echo "Table history created successfully";
144} else {
145 echo "Error creating history table: " . $conn->error;
146}
147
148$conn->close();
149
150}
151
152function createlocations() {
153$servername = "localhost";
154$username = "root";
155$password = "";
156$dbname = "csc8417test";
157
158// Create connection
159$conn = new mysqli($servername, $username, $password, $dbname);
160// Check connection
161if ($conn->connect_error) {
162 die("Connection failed: " . $conn->connect_error);
163}
164
165// sql to create table
166
167
168$sql = "CREATE TABLE IF NOT EXISTS `locations` (
169 `location_name` varchar(20) NOT NULL,
170 `game` varchar(50) NOT NULL,
171 `history` text NOT NULL
172) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
173
174if ($conn->query($sql) === TRUE) {
175 echo "Table locations created successfully";
176} else {
177 echo "Error creating locations table: " . $conn->error;
178}
179
180$conn->close();
181
182}
183
184function createusers() {
185$servername = "localhost";
186$username = "root";
187$password = "";
188$dbname = "csc8417test";
189
190// Create connection
191$conn = new mysqli($servername, $username, $password, $dbname);
192// Check connection
193if ($conn->connect_error) {
194 die("Connection failed: " . $conn->connect_error);
195}
196
197// sql to create table
198
199
200$sql = "CREATE TABLE IF NOT EXISTS `users` (
201 `username` varchar(20) NOT NULL,
202 `firstname` varchar(50) NOT NULL,
203 `lastname` varchar(50) NOT NULL,
204 `password` varchar(20) NOT NULL
205) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
206
207
208
209if ($conn->query($sql) === TRUE) {
210 echo "Table users created successfully";
211} else {
212 echo "Error creating users table: " . $conn->error;
213}
214
215$conn->close();
216
217}
218
219function createvault() {
220$servername = "localhost";
221$username = "root";
222$password = "";
223$dbname = "csc8417test";
224
225// Create connection
226$conn = new mysqli($servername, $username, $password, $dbname);
227// Check connection
228if ($conn->connect_error) {
229 die("Connection failed: " . $conn->connect_error);
230}
231
232// sql to create table
233
234
235$sql = "CREATE TABLE IF NOT EXISTS `vault` (
236 `vault_number` int(3) NOT NULL,
237 `game` varchar(50) NOT NULL,
238 `history` text NOT NULL
239) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
240
241if ($conn->query($sql) === TRUE) {
242 echo "Table vault created successfully";
243} else {
244 echo "Error creating vault table: " . $conn->error;
245}
246
247$conn->close();
248
249}
250
251function usersdata() {
252 $servername = "localhost";
253$username = "root";
254$password = "";
255$dbname = "csc8417test";
256
257// Create connection
258$conn = new mysqli($servername, $username, $password, $dbname);
259// Check connection
260if ($conn->connect_error) {
261 die("Connection failed: " . $conn->connect_error);
262}
263$sql = " INSERT INTO `users` (`username`, `firstname`, `lastname`, `password`) VALUES
264('test', 'test', 'test', 'test'),
265('tim', 'tim', 'tim', 'tim');";
266
267if ($conn->query($sql) === TRUE) {
268 echo "users data created successfully";
269} else {
270 echo "Error: " . $sql . "<br>" . $conn->error;
271}
272
273$conn->close();
274
275}
276
277function altercharactortable() {
278 $servername = "localhost";
279$username = "root";
280$password = "";
281$dbname = "csc8417test";
282
283// Create connection
284$conn = new mysqli($servername, $username, $password, $dbname);
285// Check connection
286if ($conn->connect_error) {
287 die("Connection failed: " . $conn->connect_error);
288}
289$sql = " ALTER TABLE `charactor`
290 ADD PRIMARY KEY (`charactor_name`);";
291
292if ($conn->query($sql) === TRUE) {
293 echo "charactor primary key created successfully";
294} else {
295 echo "Error: " . $sql . "<br>" . $conn->error;
296}
297
298$conn->close();
299
300}
301
302function altergamestable() {
303 $servername = "localhost";
304$username = "root";
305$password = "";
306$dbname = "csc8417test";
307
308// Create connection
309$conn = new mysqli($servername, $username, $password, $dbname);
310// Check connection
311if ($conn->connect_error) {
312 die("Connection failed: " . $conn->connect_error);
313}
314$sql = " ALTER TABLE `games`
315 ADD PRIMARY KEY (`charactor_name`),
316 ADD KEY `vault_number` (`vault_number`),
317 ADD KEY `group_name` (`group_name`);";
318
319if ($conn->query($sql) === TRUE) {
320 echo "games primary key created successfully";
321} else {
322 echo "Error: " . $sql . "<br>" . $conn->error;
323}
324
325$conn->close();
326
327}
328
329function altergroupstable() {
330 $servername = "localhost";
331$username = "root";
332$password = "";
333$dbname = "csc8417test";
334
335// Create connection
336$conn = new mysqli($servername, $username, $password, $dbname);
337// Check connection
338if ($conn->connect_error) {
339 die("Connection failed: " . $conn->connect_error);
340}
341$sql = "ALTER TABLE `groups`
342 ADD PRIMARY KEY (`group_name`);";
343
344if ($conn->query($sql) === TRUE) {
345 echo "groups primary key created successfully";
346} else {
347 echo "Error: " . $sql . "<br>" . $conn->error;
348}
349
350$conn->close();
351
352}
353
354function alterlocationstable() {
355 $servername = "localhost";
356$username = "root";
357$password = "";
358$dbname = "csc8417test";
359
360// Create connection
361$conn = new mysqli($servername, $username, $password, $dbname);
362// Check connection
363if ($conn->connect_error) {
364 die("Connection failed: " . $conn->connect_error);
365}
366$sql = "ALTER TABLE `locations`
367 ADD PRIMARY KEY (`location_name`);";
368
369if ($conn->query($sql) === TRUE) {
370 echo "locations primary key created successfully";
371} else {
372 echo "Error: " . $sql . "<br>" . $conn->error;
373}
374
375$conn->close();
376
377}
378
379function alteruserstable() {
380 $servername = "localhost";
381$username = "root";
382$password = "";
383$dbname = "csc8417test";
384
385// Create connection
386$conn = new mysqli($servername, $username, $password, $dbname);
387// Check connection
388if ($conn->connect_error) {
389 die("Connection failed: " . $conn->connect_error);
390}
391$sql = "ALTER TABLE `users`
392 ADD PRIMARY KEY (`username`);";
393
394if ($conn->query($sql) === TRUE) {
395 echo "users primary key created successfully";
396} else {
397 echo "Error: " . $sql . "<br>" . $conn->error;
398}
399
400$conn->close();
401
402}
403
404function altervaulttable() {
405 $servername = "localhost";
406$username = "root";
407$password = "";
408$dbname = "csc8417test";
409
410// Create connection
411$conn = new mysqli($servername, $username, $password, $dbname);
412// Check connection
413if ($conn->connect_error) {
414 die("Connection failed: " . $conn->connect_error);
415}
416$sql = "ALTER TABLE `vault`
417 ADD PRIMARY KEY (`vault_number`);";
418
419if ($conn->query($sql) === TRUE) {
420 echo "vault primary key created successfully";
421} else {
422 echo "Error: " . $sql . "<br>" . $conn->error;
423}
424
425$conn->close();
426
427}
428
429function altergametable() {
430 $servername = "localhost";
431$username = "root";
432$password = "";
433$dbname = "csc8417test";
434
435// Create connection
436$conn = new mysqli($servername, $username, $password, $dbname);
437// Check connection
438if ($conn->connect_error) {
439 die("Connection failed: " . $conn->connect_error);
440}
441$sql = "ALTER TABLE `games`
442 ADD CONSTRAINT `games_ibfk_1` FOREIGN KEY (`charactor_name`) REFERENCES `charactor` (`charactor_name`) ON DELETE CASCADE ON UPDATE CASCADE,
443 ADD CONSTRAINT `games_ibfk_2` FOREIGN KEY (`vault_number`) REFERENCES `vault` (`vault_number`) ON DELETE CASCADE ON UPDATE CASCADE,
444 ADD CONSTRAINT `games_ibfk_3` FOREIGN KEY (`group_name`) REFERENCES `groups` (`group_name`) ON DELETE CASCADE ON UPDATE CASCADE;";
445
446if ($conn->query($sql) === TRUE) {
447 echo "game table m2m successfully";
448} else {
449 echo "Error: " . $sql . "<br>" . $conn->error;
450}
451
452$conn->close();
453
454}
455?>