【解決方法】複数の入力を単一の DB テーブルに配置するようにクエリを変更する

[ad_1]

皆さんこんにちは!!!
Web サイト上で複数の情報を分離できるが、同じ DB テーブルに流れ込むようにクエリを編集する作業を行っています。元のファイルは Post、User、Category という名前でしたが、post1-6、user1-6、cat1-6 に変更しました。関連付けられたクエリに、元のファイルではなくさまざまなファイルを反映させたいのですが、助けが必要です。ありがとうございます:)

<?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;
			}
		}
	} 
}
?>

私が試したこと：

スクリプトの作成者が私のメールに返信していません

解決策 1

引用：

スクリプトの作成者が私のメールに返信していません

それにはおそらく理由があります。彼または彼女は、私たちと同じように、あなたのために仕事をするためにそこにいるわけではありません。

「開発」の概念は、その言葉が示すとおり、「特定の目的または要件を満たすために科学的および技術的知識を体系的に使用すること」です。 BusinessDictionary.com[^]

それは、「簡単にググっても、正確なコードが見つからなければあきらめる」ということとは異なります。
したがって、誰かにお金を払ってもらうか、自分で書き方を学びましょう。

何をする必要があるのか、どこで行き詰まっているのか、あいまいな説明の 3 つの貧弱なコードからは誰も解決できません。かまわないから」。開発の仕事をしたいですか？すぐに実行できる必要があるコードを見つけることを期待するのではなく、開発方法を学びましょう …

[ad_2]