· 8 years ago · Feb 05, 2018, 03:54 AM
1<?php
2
3//DB connection variables
4$host = 'localhost';
5$user = 'root';
6$password = '';
7
8//create mysql connection
9$mysqli = new mysqli($host,$user,$password);
10if ($mysqli->connect_errno) {
11 printf("Connection failed: %s\n", $mysqli->connect_error);
12 die();
13}
14
15//Drop database if exist
16$mysqli->query('
17DROP DATABASE IF EXISTS `pooler`;
18') or die($mysqli->error);
19
20
21//create the database
22if ( !$mysqli->query('CREATE DATABASE pooler') ) {
23 printf("Errormessage: %s\n", $mysqli->error);
24}
25
26
27//create admin table
28$mysqli->query('
29CREATE TABLE `pooler`.`admins`
30(
31 `admin_id` VARCHAR(20) NOT NULL,
32 `fname` VARCHAR(50) NOT NULL,
33 `lname` VARCHAR(50) NOT NULL,
34 `password` VARCHAR(50) NOT NULL,
35 PRIMARY KEY (`admin_id`)
36);') or die($mysqli->error);
37
38//create students table
39$mysqli->query('
40CREATE TABLE `pooler`.`students`
41(
42 `student_id` VARCHAR(50) NOT NULL,
43 `fname` VARCHAR(50) NOT NULL,
44 `lname` VARCHAR(50) NOT NULL,
45 `password` VARCHAR(50) NOT NULL,
46 `age` INT NOT NULL,
47 `form` VARCHAR(50) NOT NULL,
48 `gender` VARCHAR(6) NOT NULL,
49 `telephone` VARCHAR(50) NOT NULL,
50 `parent_telephone` VARCHAR(50) NOT NULL,
51 `career1` VARCHAR(50) NOT NULL,
52 `career2` VARCHAR(50) NOT NULL,
53 `career3` VARCHAR(50) NOT NULL,
54 `year` INT NOT NULL,
55 PRIMARY KEY (`student_id`)
56);') or die($mysqli->error);
57
58
59//create teachers table
60$mysqli->query('
61CREATE TABLE `pooler`.`teachers`
62(
63 `teacher_id` VARCHAR(50) NOT NULL,
64 `fname` VARCHAR(50) NOT NULL,
65 `lname` VARCHAR(50) NOT NULL,
66 `gender` VARCHAR(6) NOT NULL,
67 `password` VARCHAR(50) NOT NULL,
68 PRIMARY KEY (`teacher_id`)
69);') or die($mysqli->error);
70
71
72//create courses table
73$mysqli->query('
74CREATE TABLE `pooler`.`courses`
75(
76 `course_id` VARCHAR(100) NOT NULL,
77 `teacher_id` VARCHAR(50) NOT NULL,
78 `course_name` VARCHAR(50) NOT NULL,
79 `pool` VARCHAR(10) NOT NULL,
80 `day1` VARCHAR(50) NOT NULL,
81 `start_time1` VARCHAR(50) NOT NULL,
82 `end_time1` VARCHAR(50) NOT NULL,
83 `day2` VARCHAR(50) NOT NULL,
84 `start_time2` VARCHAR(50) NOT NULL,
85 `end_time2` VARCHAR(50) NOT NULL,
86 PRIMARY KEY (`course_id`,`pool`)
87);') or die($mysqli->error);
88
89
90//create class members table
91$mysqli->query('
92CREATE TABLE `pooler`.`class_members`
93(
94 `course_id` VARCHAR(100) NOT NULL,
95 `student_id` VARCHAR(50) NOT NULL,
96 `teacher_id` VARCHAR(50) NOT NULL,
97 `pool` VARCHAR(10) NOT NULL,
98 `day1` VARCHAR(50) NOT NULL,
99 `start_time1` VARCHAR(50) NOT NULL,
100 `end_time1` VARCHAR(50) NOT NULL,
101 `day2` VARCHAR(50) NOT NULL,
102 `start_time2` VARCHAR(50) NOT NULL,
103 `end_time2` VARCHAR(50) NOT NULL,
104 PRIMARY KEY (`course_id`,`student_id`)
105);') or die($mysqli->error);
106
107
108//create general time table
109$mysqli->query('
110CREATE TABLE `pooler`.`general_table`
111(
112 `table_id` VARCHAR(30) NOT NULL,
113 `pool1_time1` VARCHAR(10) NOT NULL,
114 `pool2_time1` VARCHAR(10) NOT NULL,
115 `pool3_time1` VARCHAR(10) NOT NULL,
116 `pool4_time1` VARCHAR(10) NOT NULL,
117 `pool5_time1` VARCHAR(10) NOT NULL,
118 `pool6_time1` VARCHAR(10) NOT NULL,
119
120 `pool1_time2` VARCHAR(10) NOT NULL,
121 `pool2_time2` VARCHAR(10) NOT NULL,
122 `pool3_time2` VARCHAR(10) NOT NULL,
123 `pool4_time2` VARCHAR(10) NOT NULL,
124 `pool5_time2` VARCHAR(10) NOT NULL,
125 `pool6_time2` VARCHAR(10) NOT NULL,
126 `form` VARCHAR(20) NOT NULL,
127 `year` INT NOT NULL,
128 PRIMARY KEY (`table_id`)
129);') or die($mysqli->error);
130
131
132//create notice table
133$mysqli->query('
134CREATE TABLE `pooler`.`notice`
135(
136 `notice_id` VARCHAR(30) NOT NULL,
137 `school_year` INT NOT NULL,
138 `admin_name` VARCHAR(100) NOT NULL,
139 `notice_detail` VARCHAR(5000) NOT NULL,
140 `form` VARCHAR(20) NOT NULL,
141 `date` VARCHAR(30) NOT NULL,
142 PRIMARY KEY (`notice_id`,`school_year`)
143);') or die($mysqli->error);
144
145
146//sent messages table
147$mysqli->query('
148CREATE TABLE `pooler`.`sent`
149(
150 `sent_id` VARCHAR(30) NOT NULL,
151 `student_id` VARCHAR(30) NOT NULL,
152 `sent_detail` VARCHAR(5000) NOT NULL,
153 `date` VARCHAR(30) NOT NULL,
154 PRIMARY KEY (`sent_id`,`student_id`)
155);') or die($mysqli->error);
156
157
158//course stats table
159$mysqli->query('
160CREATE TABLE `pooler`.`stats`
161(
162 `course_id` VARCHAR(30) NOT NULL,
163 `course_year` INT NOT NULL,
164 `num_students` VARCHAR(10) NOT NULL,
165 `num_pools` VARCHAR(10) NOT NULL,
166 `num_teachers` VARCHAR(10) NOT NULL,
167 PRIMARY KEY (`course_id`,`course_year`)
168);') or die($mysqli->error);
169
170
171
172//adding course record
173$mysqli->query('INSERT INTO `pooler`.`courses`(`course_id`, `teacher_id`, `course_name`, `pool`, `day1`, `start_time1`, `end_time1`, `day2`, `start_time2`, `end_time2`) VALUES ("Geo123", "Mr.Jones", "Geography", "1", "Monday", "10:30 Am", "11:50 Am", "Wednesday", "11:40 Am", "1:00 Am");') or die($mysqli->error);
174
175//adding student record
176$mysqli->query('INSERT INTO `pooler`.`students`(`student_id`, `fname`, `lname`, `password`, `age`, `form`, `gender`, `telephone`, `parent_telephone`, `career1`, `career2`,`career3`,`year`) VALUES ("stud1995", "Gary", "Roberts","1995", "22", "4-5", "Male", "529-4118", "426-783", "Doctor", "Mechanical Engineer", "Civil Engineer",2017);') or die($mysqli->error);
177
178//adding teacher record
179$mysqli->query('INSERT INTO `pooler`.`teachers`(`teacher_id`, `fname`, `lname`,`gender`,`password`) VALUES ("teach1995", "John", "Brown","Male","1995");') or die($mysqli->error);
180
181echo "Information added to database successfully";
182
183?>