-
Notifications
You must be signed in to change notification settings - Fork 0
/
init_db.php
185 lines (168 loc) · 12 KB
/
init_db.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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
<?php
include 'db_controller.php';
// Check connection before executing anything DB related
if (!$conn->connect_error) {
try {
$conn->query("CREATE DATABASE IF NOT EXISTS Alumni"); // DB creation
$conn->select_db("Alumni"); // DB selection
} catch (Exception $e) {
$_SESSION['flash_mode'] = "alert-danger";
$_SESSION['flash'] = "Failed to create Database.";
die();
}
try{
// user_table creation
$conn->query("CREATE TABLE IF NOT EXISTS user_table (
email VARCHAR(50) NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
dob DATE NULL,
gender VARCHAR(6) NOT NULL,
contact_number VARCHAR(15) NULL,
hometown VARCHAR(50) NOT NULL,
current_location VARCHAR(50) NULL,
profile_image VARCHAR(100) NULL,
job_position VARCHAR(50) NULL,
qualification VARCHAR(70) NULL,
year INT(4) NULL,
university VARCHAR(50) NULL,
company VARCHAR(50) NULL,
resume VARCHAR(100) NULL
)");
// account_table creation
$conn->query("CREATE TABLE IF NOT EXISTS account_table (
email VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
type VARCHAR(5) NOT NULL,
status VARCHAR(8) NOT NULL,
FOREIGN KEY (email) REFERENCES user_table(email) ON DELETE CASCADE ON UPDATE CASCADE
)");
// event_table creation
$conn->query("CREATE TABLE IF NOT EXISTS event_table (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
location VARCHAR(50) NOT NULL,
description VARCHAR(700) NOT NULL,
event_date DATE NOT NULL,
photo VARCHAR(100) NULL,
type VARCHAR(10) NOT NULL
)");
// event_registration_table creation
$conn->query("CREATE TABLE IF NOT EXISTS event_registration_table (
event_id INT NOT NULL,
participant_email VARCHAR(50) NOT NULL,
FOREIGN KEY (event_id) REFERENCES event_table(id) ON DELETE CASCADE ON UPDATE CASCADE
)");
// advertisement_table creation
$conn->query("CREATE TABLE IF NOT EXISTS advertisement_table (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
description VARCHAR(700) NOT NULL,
date_added DATE NOT NULL,
button_message VARCHAR(50) NULL,
button_link VARCHAR(700) NULL,
photo VARCHAR(100) NULL,
category VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL,
advertiser VARCHAR(50) NOT NULL,
appliable BOOLEAN,
date_to_hide TIMESTAMP NULL,
FOREIGN KEY (advertiser) REFERENCES user_table(email) ON DELETE CASCADE ON UPDATE CASCADE
)");
// advertisement_registration_table creation
$conn->query("CREATE TABLE IF NOT EXISTS advertisement_registration_table (
advertisement_id INT NOT NULL,
email VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
dob DATE NULL,
gender VARCHAR(6) NOT NULL,
contact_number VARCHAR(15) NULL,
hometown VARCHAR(50) NOT NULL,
current_location VARCHAR(50) NULL,
profile_image VARCHAR(100) NULL,
job_position VARCHAR(50) NULL,
qualification VARCHAR(70) NULL,
year INT(4) NULL,
university VARCHAR(50) NULL,
company VARCHAR(50) NULL,
resume VARCHAR(100) NULL,
FOREIGN KEY (advertisement_id) REFERENCES advertisement_table(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (email) REFERENCES user_table(email) ON DELETE CASCADE ON UPDATE CASCADE
)");
// user_table dummy data populate
if ($conn->query("SELECT COUNT(*) as count FROM user_table")->fetch_assoc()['count'] == 0){
$conn->query("INSERT INTO user_table (email, first_name, last_name, dob, gender, hometown)
VALUES
('[email protected]', 'Admin', 'User', '2023-10-25', 'Male', 'Kuching'),
('[email protected]', 'Ellen', 'Ripley', '2023-12-25', 'Female', '2_fort'),
('[email protected]', 'Pootis', 'Pow', '2023-11-25', 'Male', 'Turbine'),
('[email protected]', 'Beatrix', 'Kiddo', '2023-10-25', 'Female', 'Texas'),
('[email protected]', 'John', 'McClane', '2023-09-25', 'Male', 'New Jersey'),
('[email protected]', 'Judge Joe', 'Dredd', '2023-08-25', 'Male', 'Perth'),
('[email protected]', 'Alex', 'Murphy', '2023-07-25', 'Male', 'Brunei'),
('[email protected]', 'Thomas A.', 'Anderson', '2023-06-25', 'Male', 'Singapore'),
('[email protected]', 'James', 'Johnson', '2023-05-25', 'Male', 'Johor'),
('[email protected]', 'Cordell', 'Walker', '2023-04-25', 'Male', 'Kota Kinabalu'),
('[email protected]', 'B. A.', 'Baracus', '2023-03-25', 'Male', 'Kuching'),
('[email protected]', 'Exodia the Forbidden', 'One', '2023-02-25', 'Male', 'Perlis'),
('[email protected]', 'John James', 'Rambo', '2023-01-25', 'Male', 'Miri')"
);
}
// account_table dummy data populate
$hashedDefaultUserPassword = password_hash("user", PASSWORD_BCRYPT); //hash "user" password using BCrypt
$hashedDefaultAdminPassword = password_hash("admin", PASSWORD_BCRYPT); //hash "admin" password using BCrypt
if ($conn->query("SELECT COUNT(*) as count FROM account_table")->fetch_assoc()['count'] == 0){
$conn->query("INSERT INTO account_table (email, password, type, status)
VALUES
('[email protected]', '$hashedDefaultAdminPassword', 'admin', 'Approved'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Approved'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Approved'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Approved'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Approved'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Rejected'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Rejected'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Approved'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Rejected'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Rejected'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Pending'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Pending'),
('[email protected]', '$hashedDefaultUserPassword', 'user', 'Pending')"
);
}
// event_table dummy data populate
if ($conn->query("SELECT COUNT(*) as count FROM event_table")->fetch_assoc()['count'] == 0){
$conn->query("INSERT INTO event_table (title, location, description, event_date, photo, type)
VALUES
('Glamping Event', 'Miri, Sarawak', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-25', 'glamping_event.jpg', 'Event'),
('Tournament', 'Kuching, Sarawak', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-11-15', 'tournament.PNG', 'Event'),
('Mentorship', 'Perth, Australia', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-12-05', 'mentorship.PNG', 'News'),
('Exciting Stuff', 'Sydney, Australia', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-12-05', 'default_events.jpg', 'Event'),
('Party', 'Kota Kinabalu, Sabah', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-12-05', 'default_events.jpg', 'Event'),
('OH NO', 'Texas, USA', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-12-05', 'default_news.png', 'News'),
('Robot Invasion', '2_fort', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-12-05', 'default_events.jpg', 'News')"
);
}
// advertisement_table dummy data populate
if ($conn->query("SELECT COUNT(*) as count FROM advertisement_table")->fetch_assoc()['count'] == 0){
$conn->query("INSERT INTO advertisement_table (title, description, date_added, button_message, button_link, photo, category, status, advertiser, appliable)
VALUES
('Lecturers and Tutors!', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-15', 'Visit us!', 'https://www.swinburne.edu.my/courses/engineering', 'default_advertisement.jpg', 'Engineering', 'Active', '[email protected]', TRUE),
('Cloud Administrators!', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-20', 'More info!', 'https://www.swinburne.edu.my/courses/ict', 'default_advertisement.jpg', 'IT', 'Active', '[email protected]', TRUE),
('Accountants!', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-25', 'Check me!', 'https://www.swinburne.edu.my/courses/business', 'default_advertisement.jpg', 'Business', 'Inactive', '[email protected]', TRUE),
('Are you a designer?', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-31', 'Gain better understanding!', 'https://www.swinburne.edu.my/courses/design', 'default_advertisement.jpg', 'Design', 'Inactive', '[email protected]', FALSE),
('We talk business!', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-31', 'Know us better!', 'https://www.swinburne.edu.my/courses/business', 'default_advertisement.jpg', 'Business', 'Active', '[email protected]', FALSE),
('Building architects!', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-31', 'Acknowledge us!', 'https://www.swinburne.edu.my/courses/engineering', 'default_advertisement.jpg', 'Engineering', 'Active', '[email protected]', FALSE),
('Calling all artists!', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.', '2023-10-31', 'View me!', 'https://www.swinburne.edu.my/courses/design', 'default_advertisement.jpg', 'Design', 'Inactive', '[email protected]', TRUE)"
);
}
$conn->close(); // close DB connection
} catch (Exception $e) {
$_SESSION['flash_mode'] = "alert-warning";
$_SESSION['flash'] = "Failed to populate database with dummy data.";
}
} else {
header('Location: maintenance.php');
die();
}
?>