Show sourcecode
The following files exists in this folder. Click to view.
config.php
connect_db.php
db_manager.php
footer.php
head.php
header.php
login_check.php
db_manager.php
274 lines UTF-8 Windows (CRLF)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274
<?php
function createUsersTable($conn) {
$sql = "CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
points INT NOT NULL DEFAULT 0,
admin BOOLEAN NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)";
$conn->exec($sql);
}
function createQuizzesTable($conn) {
$sql = "CREATE TABLE quizzes (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
description TEXT
)";
$conn->exec($sql);
}
function createQuestionsTable($conn) {
$sql = "CREATE TABLE questions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
text TEXT NOT NULL,
points INT NOT NULL DEFAULT 1,
quiz_id INT UNSIGNED NOT NULL,
FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
INDEX (quiz_id)
)";
$conn->exec($sql);
}
function createChoicesTable($conn) {
$sql = "CREATE TABLE choices (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
text TEXT NOT NULL,
is_correct BOOLEAN NOT NULL,
question_id INT UNSIGNED NOT NULL,
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
INDEX (question_id)
)";
$conn->exec($sql);
}
function createAttemptsTable($conn) {
$sql = "CREATE TABLE attempts (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
score INT DEFAULT 0,
quiz_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX (quiz_id),
INDEX (user_id)
)";
$conn->exec($sql);
}
function createAnswersTable($conn) {
$sql = "CREATE TABLE answers (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
choice_id INT UNSIGNED NOT NULL,
attempt_id INT UNSIGNED NOT NULL,
question_id INT UNSIGNED NOT NULL,
FOREIGN KEY (choice_id) REFERENCES choices(id) ON DELETE CASCADE,
FOREIGN KEY (attempt_id) REFERENCES attempts(id) ON DELETE CASCADE,
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
INDEX (attempt_id),
INDEX (question_id),
INDEX (choice_id)
)";
$conn->exec($sql);
}
function createTables($conn) {
createUsersTable($conn);
createQuizzesTable($conn);
createQuestionsTable($conn);
createChoicesTable($conn);
createAttemptsTable($conn);
createAnswersTable($conn);
}
function dropTables($conn) {
$sql = "DROP TABLE IF EXISTS users, quizzes, questions, choices, attempts, answers";
$conn->exec($sql);
}
function makeAdmin($conn, $id) {
$sql = "UPDATE users SET admin = 1 WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);
}
function getUser($conn, $key, $value) {
$sql = "SELECT * FROM users WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetch();
}
function getUsers($conn) {
$sql = "SELECT * FROM users ORDER BY points DESC";
$stmt = $conn->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
function addUser($conn, $username, $password, $admin = 0) {
$password_hash = password_hash($password, PASSWORD_DEFAULT);
$sql = "INSERT INTO users (username, password_hash, admin) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([$username, $password_hash, $admin]);
return $conn->lastInsertId();
}
function editUser($conn, $key, $value, $id) {
$sql = "UPDATE users SET {$key} = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value, $id]);
}
function deleteUser($conn, $id) {
$user = getUser($conn, "id", $id);
if ($user['admin']) {
return;
}
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);
}
function addPoints($conn, $id, $points) {
$sql = "UPDATE users SET points = points + ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$points, $id]);
}
function recordLogin($conn, $id) {
$sql = "UPDATE users SET last_login = FROM_UNIXTIME(?) WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([time(), $id]);
}
function addAttempt($conn, $score, $quiz_id, $user_id) {
$sql = "INSERT INTO attempts (score, quiz_id, user_id) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([$score, $quiz_id, $user_id]);
return $conn->lastInsertId();
}
function getAttempt($conn, $key, $value) {
$sql = "SELECT * FROM attempts WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetch();
}
function getAttempts($conn, $key, $value) {
$sql = "SELECT * FROM attempts WHERE {$key} = ? ORDER BY score DESC";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetchAll();
}
function addQuiz($conn, $title, $description) {
$sql = "INSERT INTO quizzes (title, description) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([$title, $description]);
return $conn->lastInsertId();
}
function getQuiz($conn, $key, $value) {
$sql = "SELECT * FROM quizzes WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetch();
}
function getQuizzes($conn) {
$sql = "SELECT * FROM quizzes";
$stmt = $conn->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
function deleteQuiz($conn, $id) {
$sql = "DELETE FROM quizzes WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);
}
function addQuestion($conn, $text, $points, $quiz_id) {
$sql = "INSERT INTO questions (text, points, quiz_id) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([$text, $points, $quiz_id]);
return $conn->lastInsertId();
}
function getQuestion($conn, $key, $value) {
$sql = "SELECT * FROM questions WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetch();
}
function getQuestions($conn, $key, $value) {
$sql = "SELECT * FROM questions WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetchAll();
}
function addChoice($conn, $text, $is_correct, $question_id) {
$sql = "INSERT INTO choices (text, is_correct, question_id) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([$text, $is_correct, $question_id]);
return $conn->lastInsertId();
}
function getChoice($conn, $key, $value) {
$sql = "SELECT * FROM choices WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetch();
}
function getChoices($conn, $key, $value) {
$sql = "SELECT * FROM choices WHERE {$key} = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value]);
return $stmt->fetchAll();
}
function addAnswer($conn, $choice_id, $attempt_id, $question_id) {
$sql = "INSERT INTO answers (choice_id, attempt_id, question_id) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->execute([$choice_id, $attempt_id, $question_id]);
}
function getAnswer($conn, $key, $value, $attempt_id) {
$sql = "SELECT * FROM answers WHERE {$key} = ? AND attempt_id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$value, $attempt_id]);
return $stmt->fetch();
}
/* Nollställ tabeller (för exempelvis ändringar i databasstruktur) */
if ($cleanup) {
dropTables($conn);
createTables($conn);
// Töm alla sessionsvariabler
session_unset();
// Förstör sessionen
session_destroy();
// Ta bort sessionscookien
setcookie(session_name(), '', time() - 3600, '/');
}
?>