Hey there!!!
https://webdamn.com/build-discussion-forum-with-php-and-mysql/
Im working on making this a multi forum but having trouble with creating topics and posts that will fit under categories pertaining to the forum header. The goal is having everything in sperate but still will be inserted into the same DB table. Here is the code....I appreciate the help 🧡
<?php
class Category {
private $categoryTable = 'forum_category';
private $topicTable = 'forum_topics';
private $postTable = 'forum_posts';
private $conn;
public function __construct($db){
$this->conn = $db;
}
public function getCategoryList(){
$sqlQuery = "
SELECT *
FROM ".$this->categoryTable." ORDER BY category_id DESC";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
return $result;
}
public function getCategory(){
if($this->category_id) {
$sqlQuery = "
SELECT name
FROM ".$this->categoryTable."
WHERE category_id = ".$this->category_id;
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$categoryDetails = $result->fetch_assoc();
return $categoryDetails;
}
}
public function getCategoryTopicsCount(){
if($this->category_id) {
$sqlQuery = "
SELECT count(*) as total_topic
FROM ".$this->topicTable."
WHERE category_id = ".$this->category_id;
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$categoryDetails = $result->fetch_assoc();
return $categoryDetails['total_topic'];
}
}
public function getCategorypostsCount(){
if($this->category_id) {
$sqlQuery = "
SELECT count(p.post_id) as total_posts
FROM ".$this->postTable." as p
LEFT JOIN ".$this->topicTable." as t ON p.topic_id = t.topic_id
LEFT JOIN ".$this->categoryTable." as c ON t.category_id = c.category_id
WHERE c.category_id = ".$this->category_id;
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$categoryDetails = $result->fetch_assoc();
return $categoryDetails['total_posts'];
}
}
public function listCategory(){
$sqlQuery = "
SELECT category_id, name, description
FROM ".$this->categoryTable." ";
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY category_id ASC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$stmtTotal = $this->conn->prepare($sqlQuery);
$stmtTotal->execute();
$allResult = $stmtTotal->get_result();
$allRecords = $allResult->num_rows;
$displayRecords = $result->num_rows;
$records = array();
while ($category = $result->fetch_assoc()) {
$rows = array();
$rows[] = ucfirst($category['name']);
$rows[] = '<button type="button" name="update" id="'.$category["category_id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';
$rows[] = '<button type="button" name="delete" id="'.$category["category_id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"iTotalRecords" => $displayRecords,
"iTotalDisplayRecords" => $allRecords,
"data" => $records
);
echo json_encode($output);
}
public function getCategoryDetails(){
if($this->id) {
$sqlQuery = "
SELECT category_id, name, description
FROM ".$this->categoryTable."
WHERE category_id = ?";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->bind_param("i", $this->id);
$stmt->execute();
$result = $stmt->get_result();
$records = array();
while ($category = $result->fetch_assoc()) {
$rows = array();
$rows['category_id'] = $category['category_id'];
$rows['name'] = $category['name'];
$rows['description'] = $category['description'];
$records[] = $rows;
}
$output = array(
"data" => $records
);
echo json_encode($output);
}
}
public function insert(){
if($this->categoryName && $_SESSION["ownerId"]) {
$stmt = $this->conn->prepare("
INSERT INTO ".$this->categoryTable."(`name`, `description`)
VALUES(?, ?)");
$this->categoryName = htmlspecialchars(strip_tags($this->categoryName));
$this->description = htmlspecialchars(strip_tags($this->description));
$stmt->bind_param("ss", $this->categoryName, $this->description);
if($stmt->execute()){
return true;
}
}
}
public function update(){
if($this->id && $this->categoryName && $_SESSION["ownerId"]) {
$stmt = $this->conn->prepare("
UPDATE ".$this->categoryTable."
SET name = ?, description = ?
WHERE category_id = ?");
$this->categoryName = htmlspecialchars(strip_tags($this->categoryName));
$this->description = htmlspecialchars(strip_tags($this->description));
$stmt->bind_param("ssi", $this->categoryName, $this->description, $this->id);
if($stmt->execute()){
return true;
}
}
}
public function delete(){
if($this->id && $_SESSION["ownerId"]) {
$stmt = $this->conn->prepare("
DELETE FROM ".$this->categoryTable."
WHERE category_id = ? ");
$this->id = htmlspecialchars(strip_tags($this->id));
$stmt->bind_param("i", $this->id);
if($stmt->execute()){
return true;
}
}
}
}
?>
<?php
class Post {
private $postTable = 'forum_posts';
private $userTable = 'forum_users';
private $conn;
public function __construct($db){
$this->conn = $db;
}
public function getPost(){
$sqlQuery = "
SELECT *
FROM ".$this->postTable." ORDER BY post_id DESC LIMIT 3";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
return $result;
}
public function insert(){
if($this->message && $this->topic_id && $_SESSION["userid"]) {
$stmt = $this->conn->prepare("
INSERT INTO ".$this->postTable."(`message`, `topic_id`, `user_id`)
VALUES(?, ?, ?)");
$stmt->bind_param("sii", $this->message, $this->topic_id, $_SESSION["userid"]);
if($stmt->execute()){
$lastPid = $stmt->insert_id;
$sqlQuery = "
SELECT post.post_id, post.message, post.user_id, DATE_FORMAT(post.created,'%d %M %Y %H:%i:%s') AS post_date, user.name
FROM ".$this->postTable." post
LEFT JOIN ".$this->userTable." user ON post.user_id = user.user_id
WHERE post.post_id = '".$lastPid."'";
$stmt2 = $this->conn->prepare($sqlQuery);
$stmt2->execute();
$result = $stmt2->get_result();
$record = $result->fetch_assoc();
echo json_encode($record);
}
}
}
public function update(){
if($this->post_id && $this->message) {
$stmt = $this->conn->prepare("
UPDATE ".$this->postTable." SET message = ?
WHERE post_id = ?");
$stmt->bind_param("si", $this->message, $this->post_id);
if($stmt->execute()){
$sqlQuery = "
SELECT post.post_id, post.message, post.user_id, DATE_FORMAT(post.created,'%d %M %Y %H:%i:%s') AS post_date, user.name
FROM ".$this->postTable." post
LEFT JOIN ".$this->userTable." user ON post.user_id = user.user_id
WHERE post.post_id = '".$this->post_id."'";
$stmt2 = $this->conn->prepare($sqlQuery);
$stmt2->execute();
$result = $stmt2->get_result();
$record = $result->fetch_assoc();
echo json_encode($record);
}
}
}
}
?>
<?php
class Topic {
private $topicTable = 'forum_topics';
private $postTable = 'forum_posts';
private $userTable = 'forum_users';
private $categoryTable = 'forum_category';
private $conn;
public function __construct($db){
$this->conn = $db;
}
public function insert(){
if($this->topicName && $this->message && $this->categoryId && $_SESSION["userid"]) {
$stmt = $this->conn->prepare("
INSERT INTO ".$this->topicTable."(`subject`, `category_id`, `user_id`)
VALUES(?, ?, ?)");
$stmt->bind_param("sii", $this->topicName, $this->categoryId, $_SESSION["userid"]);
if($stmt->execute()){
$lastTopicId = $stmt->insert_id;
$stmt2 = $this->conn->prepare("
INSERT INTO ".$this->postTable."(`message`, `topic_id`, `user_id`)
VALUES(?, ?, ?)");
$stmt2->bind_param("sii", $this->message, $lastTopicId, $_SESSION["userid"]);
$stmt2->execute();
echo $lastTopicId;
}
}
}
public function getTopicList(){
if($this->category_id) {
$sqlQuery = "
SELECT c.name, c.category_id, t.subject, t.topic_id, t.user_id, t.created
FROM ".$this->topicTable." as t
LEFT JOIN ".$this->categoryTable." as c ON t.category_id = c.category_id
WHERE t.category_id = ".$this->category_id."
ORDER BY t.topic_id DESC";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
return $result;
}
}
public function getTopic(){
if($this->topic_id) {
$sqlQuery = "
SELECT subject, category_id
FROM ".$this->topicTable."
WHERE topic_id = ".$this->topic_id;
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$topicDetails = $result->fetch_assoc();
return $topicDetails;
}
}
public function getPosts(){
if($this->topic_id) {
$sqlQuery = "
SELECT t.topic_id, p.post_id, p.message, p.topic_id, p.user_id, p.created, u.name
FROM ".$this->topicTable." as t
LEFT JOIN ".$this->postTable." as p ON t.topic_id = p.topic_id
LEFT JOIN ".$this->userTable." as u ON p.user_id = u.user_id
WHERE p.topic_id = ".$this->topic_id."
ORDER BY p.post_id ASC";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
return $result;
}
}
public function getTopicPostCount(){
if($this->topic_id) {
$sqlQuery = "
SELECT count(*) as total_posts
FROM ".$this->postTable."
WHERE topic_id = ".$this->topic_id;
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$categoryDetails = $result->fetch_assoc();
return $categoryDetails['total_posts'];
}
}
}
?>
