Hey all!!!
Working on editing query so that multiple info can be separated on website but flow into the same DB table. Original files were name Post, User, Category but I changed them to post1-6, user 1-6 and cat1-6. I want the queries associated to reflect the various files instead of the originals but need help. Thank ya :)
<?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"></button>';
$rows[] = '<button type="button" name="delete" id="'.$category["category_id"].'" class="btn btn-danger btn-xs delete" ></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 User {
private $userTable = 'forum_users';
private $usergroupTable = 'forum_usergroup';
private $conn;
public function __construct($db){
$this->conn = $db;
}
public function login(){
if($this->email && $this->password) {
$sqlQuery = "
SELECT * FROM ".$this->userTable."
WHERE email = ? AND password = ?";
$stmt = $this->conn->prepare($sqlQuery);
$password = md5($this->password);
$stmt->bind_param("ss", $this->email, $password);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0){
$user = $result->fetch_assoc();
$_SESSION["userid"] = $user['user_id'];
$_SESSION["name"] = $user['name'];
return 1;
} else {
return 0;
}
} else {
return 0;
}
}
public function loggedIn (){
if(!empty($_SESSION["userid"])) {
return 1;
} else {
return 0;
}
}
public function listUsers(){
$sqlQuery = "
SELECT user.user_id, user.name, user.email, usergroup.title
FROM ".$this->userTable." user
LEFT JOIN ".$this->usergroupTable." usergroup ON user.usergroup = usergroup.usergroup_id ";
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY user_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 ($user = $result->fetch_assoc()) {
$rows = array();
$rows[] = $user['user_id'];
$rows[] = ucfirst($user['name']);
$rows[] = $user['email'];
$rows[] = $user['title'];
$rows[] = '<button type="button" name="update" id="'.$user["user_id"].'" class="btn btn-warning btn-xs update"></button>';
$rows[] = '<button type="button" name="delete" id="'.$user["user_id"].'" class="btn btn-danger btn-xs delete" ></button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"iTotalRecords" => $displayRecords,
"iTotalDisplayRecords" => $allRecords,
"data" => $records
);
echo json_encode($output);
}
public function getUserDetails(){
if($this->id) {
$sqlQuery = "
SELECT user_id, name, email, usergroup
FROM ".$this->userTable."
WHERE user_id = ?";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->bind_param("i", $this->id);
$stmt->execute();
$result = $stmt->get_result();
$records = array();
while ($user = $result->fetch_assoc()) {
$rows = array();
$rows['user_id'] = $user['user_id'];
$rows['name'] = $user['name'];
$rows['email'] = $user['email'];
$rows['usergroup'] = $user['usergroup'];
$records[] = $rows;
}
$output = array(
"data" => $records
);
echo json_encode($output);
}
}
function getUserGroupList(){
$stmt = $this->conn->prepare("
SELECT usergroup_id, title
FROM ".$this->usergroupTable);
$stmt->execute();
$result = $stmt->get_result();
return $result;
}
public function insert(){
if($this->userEmail && $_SESSION["ownerId"]) {
$stmt = $this->conn->prepare("
INSERT INTO ".$this->userTable."(`name`, `email`, `password`, `usergroup`)
VALUES(?, ?, ?, ?)");
$this->userName = htmlspecialchars(strip_tags($this->userName));
$this->userEmail = htmlspecialchars(strip_tags($this->userEmail));
$this->usergroup = htmlspecialchars(strip_tags($this->usergroup));
$this->userPassword = htmlspecialchars(strip_tags($this->userPassword));
$this->userPassword = md5($this->userPassword);
$stmt->bind_param("sssi", $this->userName, $this->userEmail, $this->userPassword, $this->usergroup);
if($stmt->execute()){
return true;
}
}
}
public function update(){
if($this->id && $this->userEmail && $_SESSION["ownerId"]) {
$passwordUpdate = '';
if($this->userPassword) {
$passwordUpdate = ", password = '".md5($this->userPassword)."'";
}
$stmt = $this->conn->prepare("
UPDATE ".$this->userTable."
SET name = ?, email = ?, usergroup = ? $passwordUpdate
WHERE user_id = ?");
$this->userName = htmlspecialchars(strip_tags($this->userName));
$this->userEmail = htmlspecialchars(strip_tags($this->userEmail));
$this->usergroup = htmlspecialchars(strip_tags($this->usergroup));
$stmt->bind_param("ssii", $this->userName, $this->userEmail, $this->usergroup, $this->id);
if($stmt->execute()){
return true;
}
}
}
public function delete(){
if($this->id && $_SESSION["ownerId"]) {
$stmt = $this->conn->prepare("
DELETE FROM ".$this->userTable."
WHERE user_id = ? ");
$this->id = htmlspecialchars(strip_tags($this->id));
$stmt->bind_param("i", $this->id);
if($stmt->execute()){
return true;
}
}
}
}
?>
What I have tried:
Creator of the script has not responded to my emails