php - 使用 PHP mySQL 显示来自两个 sql 表的数据
问题描述
我制作了这个社交网站http://friendquest.rf.gd。我在新闻提要中有一个问题，无法解决。所以一切正常，但不是我想要的方式！我创建了一个名为posts and reposts 的SQL 表，其中posts 是用户输入以显示在我网站的新闻提要中的数据，而repost 的作用类似于twitter 上的分享按钮或转发按钮。
现在我希望两个表同时显示。
例如，我发布了一个 id 为 31 的帖子。现在我的一个朋友重新发布了 id 31，按照时间排序它应该显示如下 -
重新发布 id 3 为帖子 id 31
帖子 id 33
帖子 id 32
帖子 id 31
重新发布后我的代码如何显示数据 -
帖子 id 33
帖子 id 32
重新发布
id 31 为帖子 id 31
帖子 id 31
根据发布的时间，我无法弄清楚如何在顶部获得转发.. 这是我的转发代码
public function getRepostsPosts($post_id, $first_name, $last_name, $body, $imageDiv, $orig_poster, $imagePath){
$query = mysqli_query($this->con, "SELECT * FROM reposts WHERE post_id='$post_id'");
$html = "";
if(mysqli_num_rows($query) > 0){
while($row = mysqli_fetch_array($query)){
$repost_body = $row['body'];
$repost_by = $row['repost_by'];
$users_query = mysqli_query($this->con, "SELECT * FROM users WHERE username='$repost_by'");
$users_row = mysqli_fetch_array($users_query);
$reposted_first_name = $users_row['first_name'];
$reposted_last_name = $users_row['last_name'];
$num = mysqli_num_rows($query);
if($this->user_obj->isFriend($repost_by)){
$link = "<a href='$repost_by'> $reposted_first_name $reposted_last_name </a>";
}
else{
$link = "$reposted_first_name $reposted_last_name";
}
if($repost_body != ""){
$body_html = "<br>
<p>And said \"$repost_body\".</p>
<br>";
}
else{
$body_html = "";
}
$html .= "<div class='status_post'>
<div class='reposted_by' style='color:#ACACAC;'>
$link Reposted <a href='$orig_poster'>$first_name $last_name</a>'s <a href='post.php?id=$post_id'>Post</a>
$body_html
<div id='repost_body' onclick='location.href = \"post.php?id=$post_id\"'>
$body
<br>
$imageDiv
<br>
<br>
</div>
</div>
</div>
<div id='myModal$post_id' class='imageModal' style='display: none'>
<div class='modalContent'>
<img src='$imagePath' class='modalImage'>
<br>
<button class='button cursor' onclick='closeModal$post_id()'>Close</button>
</div>
</div>
<br>";
//return $html;
}
return $html;
}
else{
return "";
}
}
这是我的帖子代码
public function loadPostsFriends($data, $limit) {
$page = $data['page'];
$userLoggedIn = $this->user_obj->getUsername();
if($page == 1)
$start = 0;
else
$start = ($page - 1) * $limit;
$str = ""; //String to return
$data_query = mysqli_query($this->con, "SELECT * FROM posts WHERE deleted='no' ORDER BY id DESC");
if(mysqli_num_rows($data_query) > 0) {
$num_iterations = 0; //Number of results checked (not necasserily posted)
$count = 1;
while($row = mysqli_fetch_array($data_query)) {
$id = $row['id'];
$body = $row['body'];
$added_by = $row['added_by'];
$date_time = $row['date_added'];
$imagePath = $row['image'];
//Prepare user_to string so it can be included even if not posted to a user
if($row['user_to'] == "none") {
$user_to = "";
}
else {
$user_to_obj = new User($this->con, $row['user_to']);
$user_to_name = $user_to_obj->getFirstAndLastName();
$user_to = "to <a href='" . $row['user_to'] ."'>" . $user_to_name . "</a>";
}
//Check if user who posted, has their account closed
$added_by_obj = new User($this->con, $added_by);
if($added_by_obj->isClosed()) {
continue;
}
$user_logged_obj = new User($this->con, $userLoggedIn);
if($user_logged_obj->isFriend($added_by)){
if($num_iterations++ < $start)
continue;
//Once 10 posts have been loaded, break
if($count > $limit) {
break;
}
else {
$count++;
}
if($userLoggedIn == $added_by){
$delete_button = "<button class='delete_button' id='post$id' data-toggle='modal' data-target='#delete_form$id'>Delete Post</button>";
$edit_button = "<button class='edit_button' id='post$id' data-toggle='modal' data-target='#edit_form$id'>Edit Post</button>";
$repost_button = "";
}
else{
$delete_button = "";
$edit_button = "";
$repost_button = "<button class='edit_button' id='post$id' data-toggle='modal' data-target='#repost_form$id'><img src='assets/images/icons/repost.png' class='repostButton'>Repost</button>";
}
$user_details_query = mysqli_query($this->con, "SELECT first_name, last_name, profile_pic FROM users WHERE username='$added_by'");
$user_row = mysqli_fetch_array($user_details_query);
$first_name = $user_row['first_name'];
$last_name = $user_row['last_name'];
$profile_pic = $user_row['profile_pic'];
?>
<script>
function openModal<?php echo $id?>(){
document.getElementById('myModal<?php echo $id ?>').style.display = "block";
}
function closeModal<?php echo $id?>(){
document.getElementById('myModal<?php echo $id ?>').style.display = "none";
}
function toggle<?php echo $id; ?>(){
var target = $(event.target);
if(!target.is("a") && !target.is("button") && !target.is("img") && !target.is("textarea") && !target.is("")){
var element = document.getElementById("toggleComment<?php echo $id; ?>");
if(element.style.display == "block"){
element.style.display = "none";
}
else{
element.style.display = "block";
}
}
}
</script>
<?php
$comments_check = mysqli_query($this->con, "SELECT * FROM comments WHERE post_id='$id'");
$comments_check_num = mysqli_num_rows($comments_check);
//Timeframe
$date_time_now = date("Y-m-d H:i:s");
$start_date = new DateTime($date_time); //Time of post
$end_date = new DateTime($date_time_now); //Current time
$interval = $start_date->diff($end_date); //Difference between dates
if($interval->y >= 1) {
if($interval == 1)
$time_message = $interval->y . " year ago"; //1 year ago
else
$time_message = $interval->y . " years ago"; //1+ year ago
}
else if ($interval-> m >= 1) {
if($interval->d == 0) {
$days = " ago";
}
else if($interval->d == 1) {
$days = $interval->d . " day ago";
}
else {
$days = $interval->d . " days ago";
}
if($interval->m == 1) {
$time_message = $interval->m . " month". $days;
}
else {
$time_message = $interval->m . " months". $days;
}
}
else if($interval->d >= 1) {
if($interval->d == 1) {
$time_message = "Yesterday";
}
else {
$time_message = $interval->d . " days ago";
}
}
else if($interval->h >= 1) {
if($interval->h == 1) {
$time_message = $interval->h . " hour ago";
}
else {
$time_message = $interval->h . " hours ago";
}
}
else if($interval->i >= 1) {
if($interval->i == 1) {
$time_message = $interval->i . " minute ago";
}
else {
$time_message = $interval->i . " minutes ago";
}
}
else {
if($interval->s < 30) {
$time_message = "Just now";
}
else {
$time_message = $interval->s . " seconds ago";
}
}
if($imagePath != "") {
$imageDiv = "<div class='postedImage'>
<img src='$imagePath' onclick='openModal$id()'>
</div>";
}
else {
$imageDiv = "";
}
if($this->getRepostsPosts($id, $first_name, $last_name, $body, $imageDiv, $added_by, $imagePath) != ""){
$str .= $this->getRepostsPosts($id, $first_name, $last_name, $body, $imageDiv, $added_by, $imagePath);
}
$str .= "<div class='status_post' onClick='javascript:toggle$id()'>
<div class='post_profile_pic'>
<img src='$profile_pic' width='50'>
</div>
<div class='posted_by' style='color:#ACACAC;'>
<a href='$added_by'> $first_name $last_name </a> $user_to $time_message
</div>
<div id='post_body'>
$body
<br>
$imageDiv
<br>
<br>
</div>
<div class='modal fade' id='edit_form$id' tabindex='-1' role='dialog' aria-labelledby='postModalLabel' aria-hidden='true'>
<div class='modal-dialog' role='document'>
<div class='modal-content'>
<div class='modal-header'>
<h5 class='modal-title' id='exampleModalLabel'>Edit Your Post</h5>
<button type='button' class='close' data-dismiss='modal' aria-label='Close'>
<span aria-hidden='true'>×</span>
</button>
</div>
<div class='modal-body'>
<p>Edit your Post</p>
<form class='post_form' action='index.php' method='POST'>
<div class='form-group'>
<textarea class='form-control' id='post_text' name='post_text' placeholder='Got something to edit?'>$body</textarea>
<input type='hidden' name='post_id' value='$id'>
<button type='submit' class='btn btn-primary' name='edit' id='post_button'>Post</button>
</div>
</form>
</div>
<div class='modal-footer'>
<button type='button' class='btn btn-secondary' data-dismiss='modal'>Close</button>
</div>
</div>
</div>
</div>
<div class='modal fade' id='delete_form$id' tabindex='-1' role='dialog' aria-labelledby='postModalLabel' aria-hidden='true'>
<div class='modal-dialog' role='document'>
<div class='modal-content'>
<div class='modal-header'>
<h5 class='modal-title' id='exampleModalLabel'>Delete Your Post</h5>
<button type='button' class='close' data-dismiss='modal' aria-label='Close'>
<span aria-hidden='true'>×</span>
</button>
</div>
<div class='modal-body'>
<p>Are you sure you want to Delete?</p>
<form class='post_form' action='index.php' method='POST'>
<div class='form-group'>
<input type='hidden' name='post_id' value='$id'>
<button type='submit' class='btn btn-primary' name='delete' id='post_button'>Yes!</button>
</div>
</form>
</div>
<div class='modal-footer'>
<button type='button' class='btn btn-secondary' data-dismiss='modal'>No!</button>
</div>
</div>
</div>
</div>
<div class='modal fade' id='repost_form$id' tabindex='-1' role='dialog' aria-labelledby='postModalLabel' aria-hidden='true'>
<div class='modal-dialog' role='document'>
<div class='modal-content'>
<div class='modal-header'>
<h5 class='modal-title' id='exampleModalLabel'>Repost the Post</h5>
<button type='button' class='close' data-dismiss='modal' aria-label='Close'>
<span aria-hidden='true'>×</span>
</button>
</div>
<div class='modal-body'>
<p>Repost</p>
<p>\"$body\"</p>
<form class='post_form' action='index.php' method='POST'>
<div class='form-group'>
<textarea class='form-control' id='post_text' name='post_text' placeholder='Want something to say about the Repost?'></textarea>
<input type='hidden' name='post_id' value='$id'>
<input type='hidden' name='repost_by' value='$userLoggedIn'>
<button type='submit' class='btn btn-primary' name='repost' id='post_button'>Repost!</button>
</div>
</form>
</div>
<div class='modal-footer'>
<button type='button' class='btn btn-secondary' data-dismiss='modal'>Cancel</button>
</div>
</div>
</div>
</div>
<div class='newsFeedPostOptions'>
Comments($comments_check_num)
<iframe src='like.php?post_id=$id' id='likes_iframe' scrolling='no'></iframe>
$delete_button
$edit_button
$repost_button
</div>
</div>
<div class='post_comment' id='toggleComment$id' style='display:none;'>
<iframe src='comment_frame.php?post_id=$id' id='comment_iframe' frameborder='0'>
</iframe>
</div>
<div id='myModal$id' class='imageModal' style='display: none'>
<div class='modalContent'>
<img src='$imagePath' class='modalImage'>
<br>
<button class='button cursor' onclick='closeModal$id()'>Close</button>
</div>
</div>
<br>";
}
} //End while loop
if($count > $limit)
$str .= "<input type='hidden' class='nextPage' value='" . ($page + 1) . "'>
<input type='hidden' class='noMorePosts' value='false'>";
else
$str .= "<input type='hidden' class='noMorePosts' value='true'><center><p style='text-align: centre; padding-top: 30px; color: #ACACAC '> No more posts to show! </p></center>";
}
echo $str;
}
解决方案
首先，我应该警告您有关 SQL 注入的风险，并且您的代码可能存在风险，具体取决于您如何过滤 $post_id 之类的东西，因为它可能是有人潜入 drop table 或类似的东西真正毁了您一天的地方.
我强烈建议查看准备好的语句来避免这种情况，但更建议查看高度维护的库，这些库将 SQL 构建工作抽象出来，以更加程序化地构建查询。例如 Eloquent，它是称为 Laravel 的奇妙 MVC 框架的一部分，但可以独立使用或作为其他框架的一部分使用。
要指出的另一件事是对数据库的过多查询，因为您有一个初始查询来获取转发/发布，然后您迭代结果并在每个帖子上查询用户，您添加了太多查询到一个查询可以达到相同结果的函数。如果您在代码中手动编写 SQL，或者如果您切换到 Eloquent，那么请考虑使用连接，那么关系是一种绝妙的方式来执行称为急切加载的事情，您可以在其中查询一个表并使用您要求的关系来包含用户或其他通过结构相关的东西，比如外键。所有这些只花费你一两个查询，而不是 n ，其中 n 是转发/发布的数量。
if($this->getRepostsPosts($id, $first_name, $last_name, $body, $imageDiv, $added_by, $imagePath) != ""){
$str .= $this->getRepostsPosts($id, $first_name, $last_name, $body, $imageDiv, $added_by, $imagePath);
}
这有可能执行两次相同的许多查询，第一次是检查是否有转发，然后再次执行相同的功能请求。考虑将第一次调用的结果分配给一个变量并检查条件，如果为真，则使用该变量，见下文。
if (!empty($reposts = $this->getRepostsPosts($id, $first_name, $last_name, $body, $imageDiv, $added_by, $imagePath))) {
$str .= $reposts;
}
你甚至可以只使用三元：
$str .= (
!empty($reposts = $this->getRepostsPosts($id, $first_name, $last_name, $body, $imageDiv, $added_by, $imagePath))
? $reposts
: ''
);
现在有很多东西要学，但让你的 SQL 安全是一个很好的起点，当你发现新技术时，你的代码效率就会提高。
至于您的问题，您显示帖子的顺序可能会受到您设计的编程方法的影响，您真正应该做的是将您的帖子和转发的数组构建为属性对象，然后当您完成填充数组的所有查询，您可以使用usort根据其项目属性之一（可能是日期时间属性或您希望排序的其他值）对数组重新排序，然后在排序后您可以使用 a 迭代该数组foreach 并一步构建 HTML 输出。
使用此提议的更改来提供代码的更改版本需要付出很大的努力，因此我认为您需要调整方法并更多地考虑逐步执行代码，并尽量不要开始准备数据的呈现当您仍在获取数据并取而代之的是获取所有数据时，组织和减少数据，然后生成演示文稿。
推荐阅读
- excel - 选择案例代码范围的最后一行代码
- javascript - 带有单击的javascript进度条
- angularjs - 在 720kb 日历上突出显示日期
- python-3.x - 将数据加载到 aws elasticsearch 时出错：RequestError(400, '没有为 uri [/:443/movies/_doc/5] 和方法 [PUT] 找到处理程序'
- oracle - ORA-32359: 无法为此查询指定 FRESH_MV 提示 - 查询 RTMV
- python - 在 cx_Oracle 中处理多个游标
- powershell - powershell：类括号内的逗号和参数？
- javascript - 我在反应应用程序中使用了 axios，但无法从提供的链接中获取用户数据
- c# - 如何将分钟添加到 DateTime 并创建新的 dateTime？xamarin
- apache-flink - Flink 状态后端失败后无法恢复任务管理器