Show sourcecode
The following files exists in this folder. Click to view.
public_html/exercises/quizproject/
admin/
createtable.php
dbconnection.php
icons/
index.php
login.php
logout.php
quiz/
quiz_styles.css
register.php
createtable.php
121 lines ASCII Windows (CRLF)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121
<!-- createtable.php -->
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Create</title>
</head>
<body>
<form action="./index.php" method="get"><button type="submit">Back to dashboard</button></form>
<?php
include('dbconnection.php');
if (!$dbconn) {
die("Connection failed: Can't connect to database.");
}
try {
// Skapa users table
$sql = "CREATE TABLE IF NOT EXISTS users (
user_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
is_admin BOOLEAN,
fullname VARCHAR(64) NOT NULL,
last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
// use exec() because no results are returned
$dbconn->exec($sql);
echo "Users table created successfully. <br>";
// Insert admin user endast om den inte finns
$stmt = $dbconn->query("SELECT * FROM users WHERE user_id = 1");
$adminExists = (int)$stmt->fetchColumn() > 0;
if (!$adminExists) {
$adminHash = password_hash('admin123', PASSWORD_DEFAULT);
$sql = "INSERT INTO users (username, password, is_admin, fullname) VALUES ('admin', '$adminHash', 1, 'Administrator')";
$dbconn->exec($sql);
echo "Admin user created successfully. <br>";
} else {
echo "Admin user already exists. <br>";
}
// Skapa quizzes table
$sql = "CREATE TABLE IF NOT EXISTS quizzes (
quiz_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
quiz_name VARCHAR(64) NOT NULL UNIQUE,
owner_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (owner_id) REFERENCES users(user_id) ON DELETE CASCADE
)";
// use exec() because no results are returned
$dbconn->exec($sql);
echo "Quizzes table created successfully. <br>";
// Skapa questions table
$sql = "CREATE TABLE IF NOT EXISTS questions (
question_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
question VARCHAR(64) NOT NULL,
quiz_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (quiz_id) REFERENCES quizzes(quiz_id) ON DELETE CASCADE
)";
// use exec() because no results are returned
$dbconn->exec($sql);
echo "Questions table created successfully. <br>";
// Skapa answers table
$sql = "CREATE TABLE IF NOT EXISTS answers (
answer_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
answer VARCHAR(64) NOT NULL,
is_correct BOOLEAN,
question_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (question_id) REFERENCES questions(question_id) ON DELETE CASCADE
)";
// use exec() because no results are returned
$dbconn->exec($sql);
echo "Answers table created successfully. <br>";
// Skapa submissions table
$sql = "CREATE TABLE IF NOT EXISTS submissions (
submission_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
quiz_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (quiz_id) REFERENCES quizzes(quiz_id) ON DELETE CASCADE,
user_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
submitted_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
score INT(6)
)";
// use exec() because no results are returned
$dbconn->exec($sql);
echo "Submissions table created successfully. <br>";
// Skapa submission_answer answers table
$sql = "CREATE TABLE IF NOT EXISTS submission_answers (
submission_answer_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
submission_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (submission_id) REFERENCES submissions(submission_id) ON DELETE CASCADE,
question_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (question_id) REFERENCES questions(question_id) ON DELETE CASCADE,
answer_id INT(6) UNSIGNED NOT NULL,
FOREIGN KEY (answer_id) REFERENCES answers(answer_id) ON DELETE CASCADE,
is_correct BOOLEAN DEFAULT 0
)";
// use exec() because no results are returned
$dbconn->exec($sql);
echo "Submission answers table created successfully. <br>";
} catch (PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
//Rensa kopplingen till databasen
$dbconn = null;
?>
</body>
</html>