From 5d703ab4ea6e8ead889232ba846596b948bdb051 Mon Sep 17 00:00:00 2001 From: cflip <36554078+cflip@users.noreply.github.com> Date: Tue, 10 Aug 2021 16:17:53 -0600 Subject: Update names of database variables --- includes/model/Category.php | 18 +++++++++--------- includes/model/Post.php | 6 +++--- includes/model/Thread.php | 16 ++++++++-------- includes/model/User.php | 22 +++++++++++----------- meta/setup.sql | 40 ++++++++++++++++++++-------------------- 5 files changed, 51 insertions(+), 51 deletions(-) diff --git a/includes/model/Category.php b/includes/model/Category.php index fb57918..8aa489c 100755 --- a/includes/model/Category.php +++ b/includes/model/Category.php @@ -17,7 +17,7 @@ class Category public function __construct($id) { - $sql = "SELECT cat_name, cat_description, cat_thread_count, cat_post_count FROM categories WHERE cat_id = ?;"; + $sql = "SELECT category_name, category_description, category_thread_count, category_post_count FROM categories WHERE category_id = ?;"; $result = Database::get()->query($sql, "i", $id); if (empty($result)) { @@ -25,10 +25,10 @@ class Category } $this->id = $id; - $this->name = $result[0]['cat_name']; - $this->description = $result[0]['cat_description']; - $this->thread_count = $result[0]['cat_thread_count']; - $this->post_count = $result[0]['cat_post_count']; + $this->name = $result[0]['category_name']; + $this->description = $result[0]['category_description']; + $this->thread_count = $result[0]['category_thread_count']; + $this->post_count = $result[0]['category_post_count']; $this->has_value = true; } @@ -41,13 +41,13 @@ class Category public static function get_all_categories(): array { - $sql = "SELECT cat_id FROM categories ORDER BY cat_id;"; + $sql = "SELECT category_id FROM categories ORDER BY category_id;"; $result = Database::get()->query($sql); $categories = array(); foreach ($result as $row) { - $category = new Category($row['cat_id']); + $category = new Category($row['category_id']); array_push($categories, $category); } @@ -56,7 +56,7 @@ class Category public function get_threads(): array { - $sql = "SELECT thread_id FROM threads WHERE thread_category = ? ORDER BY thread_date_lastpost DESC"; + $sql = "SELECT thread_id FROM threads WHERE thread_category = ? ORDER BY thread_last_post_date DESC"; $result = Database::get()->query($sql, "i", $this->id); $threads = array(); @@ -71,7 +71,7 @@ class Category public function get_latest_thread(): Thread { - $sql = "SELECT thread_id FROM threads WHERE thread_category = ? ORDER BY thread_date_lastpost DESC LIMIT 1"; + $sql = "SELECT thread_id FROM threads WHERE thread_category = ? ORDER BY thread_last_post_date DESC LIMIT 1"; $result = Database::get()->query($sql, "i", $this->id); return new Thread($result[0]['thread_id']); } diff --git a/includes/model/Post.php b/includes/model/Post.php index 46a0af0..f251773 100755 --- a/includes/model/Post.php +++ b/includes/model/Post.php @@ -141,7 +141,7 @@ class Post Database::get()->query("DELETE FROM posts WHERE post_id = ?", "i", $this->id); // Decrement the post count of the category - Database::get()->query("UPDATE categories SET `cat_post_count` = `cat_post_count` - '1' WHERE cat_id = ?", "i", $this->thread->category->id); + Database::get()->query("UPDATE categories SET `category_post_count` = `category_post_count` - '1' WHERE category_id = ?", "i", $this->thread->category->id); } public static function create($post_content, $post_thread, $post_category) @@ -159,11 +159,11 @@ class Post Database::get()->query($sql, "sii", $post_content, $post_thread, $user->id); // Increment the category's post count - $sql = "UPDATE categories SET `cat_post_count` = `cat_post_count` + '1' WHERE cat_id = ?;"; + $sql = "UPDATE categories SET `category_post_count` = `category_post_count` + '1' WHERE category_id = ?;"; Database::get()->query($sql, "i", $post_category); // Set the last post date of the parent thread - $sql = "UPDATE threads SET thread_date_lastpost = CONVERT_TZ(NOW(), 'SYSTEM', '+00:00') WHERE thread_id = ?;"; + $sql = "UPDATE threads SET thread_last_post_date = CONVERT_TZ(NOW(), 'SYSTEM', '+00:00') WHERE thread_id = ?;"; Database::get()->query($sql, "i", $post_thread); } diff --git a/includes/model/Thread.php b/includes/model/Thread.php index 628a325..c939738 100644 --- a/includes/model/Thread.php +++ b/includes/model/Thread.php @@ -10,7 +10,7 @@ class Thread public $id; public $subject; public $date_created = 0; - public $date_lastpost = 0; + public $last_post_date = 0; public $category; public $author; @@ -18,7 +18,7 @@ class Thread function __construct($id) { - $sql = "SELECT thread_subject, thread_date_created, thread_date_lastpost, thread_category, thread_author FROM threads WHERE thread_id = ?;"; + $sql = "SELECT thread_subject, thread_date_created, thread_last_post_date, thread_category, thread_author FROM threads WHERE thread_id = ?;"; $result = Database::get()->query($sql, "i", $id); if (empty($result)) { @@ -28,7 +28,7 @@ class Thread $this->id = $id; $this->subject = $result[0]['thread_subject']; $this->date_created = $result[0]['thread_date_created']; - $this->date_lastpost = $result[0]['thread_date_lastpost']; + $this->last_post_date = $result[0]['thread_last_post_date']; $this->category = new Category($result[0]['thread_category']); $this->author = new User(); @@ -47,14 +47,14 @@ class Thread $user = Session::get()->get_current_user(); // Insert the new thread into the database - $sql = "INSERT INTO threads(thread_subject, thread_date_created, thread_date_lastpost, thread_category, thread_author) VALUES (?, CONVERT_TZ(NOW(), 'SYSTEM', '+00:00'), CONVERT_TZ(NOW(), 'SYSTEM', '+00:00'), ?, ?);"; + $sql = "INSERT INTO threads(thread_subject, thread_date_created, thread_last_post_date, thread_category, thread_author) VALUES (?, CONVERT_TZ(NOW(), 'SYSTEM', '+00:00'), CONVERT_TZ(NOW(), 'SYSTEM', '+00:00'), ?, ?);"; Database::get()->query($sql, "sii", $subject, $category, $user->id); // Get the ID of the thread we just created $thread_id = Database::get()->get_last_id(); // Increment the category's thread count - $sql = "UPDATE categories SET `cat_thread_count` = `cat_thread_count` + '1' WHERE cat_id = ?;"; + $sql = "UPDATE categories SET `category_thread_count` = `category_thread_count` + '1' WHERE category_id = ?;"; Database::get()->query($sql, "i", $category); return $thread_id; @@ -79,11 +79,11 @@ class Thread Database::get()->query("DELETE FROM threads WHERE thread_id = ?", "i", $thread->id); // Decrement the thread count of the category - Database::get()->query("UPDATE categories SET `cat_thread_count` = `cat_thread_count` - '1' WHERE cat_id = ?", "i", $thread->category->id); + Database::get()->query("UPDATE categories SET `category_thread_count` = `category_thread_count` - '1' WHERE category_id = ?", "i", $thread->category->id); } public function has_value(): bool - { + { return $this->has_value; } @@ -105,7 +105,7 @@ class Thread public static function get_latest(): array { - $sql = "SELECT thread_id FROM threads ORDER BY thread_date_lastpost DESC LIMIT 10"; + $sql = "SELECT thread_id FROM threads ORDER BY thread_last_post_date DESC LIMIT 10"; $result = Database::get()->query($sql); $threads = array(); diff --git a/includes/model/User.php b/includes/model/User.php index 785b847..17d40cb 100755 --- a/includes/model/User.php +++ b/includes/model/User.php @@ -1,7 +1,7 @@ query($sql, "i", $id); if (empty($result)) { @@ -26,8 +26,8 @@ class User $this->id = $id; $this->name = $result[0]['user_name']; - $this->password = $result[0]['user_pass']; - $this->date = $result[0]['user_date']; + $this->password = $result[0]['user_password']; + $this->date = $result[0]['user_date_registered']; $this->level = $result[0]['user_level']; $this->has_value = true; @@ -35,7 +35,7 @@ class User public function get_by_name($name) { - $sql = "SELECT user_id, user_date, user_level, user_pass FROM users WHERE user_name = ?"; + $sql = "SELECT user_id, user_date_registered, user_level, user_password FROM users WHERE user_name = ?"; $result = Database::get()->query($sql, "s", $name); if (empty($result)) { @@ -44,8 +44,8 @@ class User $this->id = $result[0]['user_id']; $this->name = $name; - $this->password = $result[0]['user_pass']; - $this->date = $result[0]['user_date']; + $this->password = $result[0]['user_password']; + $this->date = $result[0]['user_date_registered']; $this->level = $result[0]['user_level']; $this->has_value = true; @@ -58,7 +58,7 @@ class User public static function register(string $username, string $password) { - $sql = "INSERT INTO users(user_name, user_pass, user_date, user_level) VALUES(?, ?, NOW(), 0);"; + $sql = "INSERT INTO users(user_name, user_password, user_date_registered) VALUES(?, ?, NOW());"; $pass_hash = password_hash($password, PASSWORD_DEFAULT); Database::get()->query($sql, "ss", $username, $pass_hash); } @@ -75,13 +75,13 @@ class User return; } - $sql = "UPDATE users SET user_pass = ? WHERE user_id = ?;"; + $sql = "UPDATE users SET user_password = ? WHERE user_id = ?;"; Database::get()->query($sql, "si", $pass_hash, $this->id); } public function get_threads(): array { - $sql = "SELECT thread_id FROM threads WHERE thread_author = ? ORDER BY thread_date_lastpost DESC"; + $sql = "SELECT thread_id FROM threads WHERE thread_author = ? ORDER BY thread_last_post_date DESC"; $result = Database::get()->query($sql, "i", $this->id); $threads = array(); diff --git a/meta/setup.sql b/meta/setup.sql index 6a96b8a..cb7df23 100644 --- a/meta/setup.sql +++ b/meta/setup.sql @@ -1,32 +1,32 @@ CREATE TABLE users ( user_id INT(8) NOT NULL AUTO_INCREMENT, - user_name VARCHAR(30) NOT NULL, - user_pass VARCHAR(255) NOT NULL, - user_level INT(8) NOT NULL DEFAULT 0, - user_date DATETIME NOT NULL, - UNIQUE INDEX user_name_unique (user_name), + user_name VARCHAR(30) NOT NULL, + user_password VARCHAR(255) NOT NULL, + user_level ENUM('user', 'moderator', 'administrator') NOT NULL DEFAULT 'user', + user_date_registered DATETIME NOT NULL, + UNIQUE INDEX user_name_unique (user_name), PRIMARY KEY (user_id) -) ENGINE = InnoDB; +); CREATE TABLE categories ( - cat_id INT(8) NOT NULL AUTO_INCREMENT, - cat_name VARCHAR(255) NOT NULL, - cat_description VARCHAR(255) NOT NULL, - cat_thread_count INT(8) NOT NULL DEFAULT 0, - cat_post_count INT(8) NOT NULL DEFAULT 0, - UNIQUE INDEX cat_name_unique (cat_name), - PRIMARY KEY (cat_id) -) ENGINE = InnoDB; + category_id INT(8) NOT NULL AUTO_INCREMENT, + category_name VARCHAR(255) NOT NULL, + category_description VARCHAR(255) NOT NULL, + category_thread_count INT(8) NOT NULL DEFAULT 0, + category_post_count INT(8) NOT NULL DEFAULT 0, + UNIQUE INDEX category_name_unique (category_name), + PRIMARY KEY (category_id) +); CREATE TABLE threads ( thread_id INT(8) NOT NULL AUTO_INCREMENT, thread_subject VARCHAR(255) NOT NULL, thread_date_created DATETIME NOT NULL, - thread_date_lastpost DATETIME NOT NULL, + thread_last_post_date DATETIME NOT NULL, thread_category INT(8) NOT NULL, thread_author INT(8) NOT NULL, PRIMARY KEY (thread_id) -) ENGINE = InnoDB; +); CREATE TABLE posts ( post_id INT(8) NOT NULL AUTO_INCREMENT, @@ -36,9 +36,9 @@ CREATE TABLE posts ( post_thread INT(8) NOT NULL, post_author INT(8) NOT NULL, PRIMARY KEY (post_id) -) ENGINE = InnoDB; +); -ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(cat_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE; +ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(category_id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE posts ADD FOREIGN KEY(post_thread) REFERENCES threads(thread_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE; +ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; -- cgit v1.2.3