php - 带回复的 PHP MySQL 评论系统
问题描述
我有能够捕获初始评论和第一级回复的代码,但它似乎没有捕获对回复的回复。我知道它需要使用某种形式的递归的不确定代码,但不太确定如何正确实现它。这是我的 phpMyAdmin 表的样子:
id name comment reply_id
1 BigBadProducer1 我喜欢这个 vst!我用它所有的时间!0
2 DrummaBoy504 嘿,我是 Drum Squad 的 Drumma!0
3 Mike Smith 你是如何让 vst 听起来如此出色的... 1
4 BigBadProducer1 是的,我从 YouTube 学会了如何调整它 Mike S... 3
5 SmoothBeatz3 伙计,我一直在寻找这样的 vst对于一个... 0
6 FanBoy123 嘿 Drumma,你什么时候发布一个新的 hi... 2
7 Mike Johnson Hey Fanboy123,你为什么这么喜欢 Drum S... 6
这是代码:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<body>
<?php
// Create connection
$conn = new mysqli('localhost', 'root', 'mypassword', 'commentsystem2');
$sql1 = "SELECT * FROM comments WHERE reply_id = 0";
$result1 = mysqli_query($conn, $sql1);
while ($comment = mysqli_fetch_array($result1)) {
$id = $comment['id'];
$name = $comment['name'];
$comment = $comment['comment'];
echo '
<div class="comments" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div>'.$name.'</div>
<div>'.$comment.'<br><br></div>
</div>
';
$sql2 = "SELECT * FROM comments WHERE reply_id = $id";
$result2 = mysqli_query($conn, $sql2);
while ($reply = mysqli_fetch_array($result2)) {
$id_reply = $reply['id'];
$reply_name = $reply['name'];
$reply_comment = $reply['comment'];
$reply_id = $reply['reply_id'];
echo '
<div class="replies" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div style="width:80%; text-align:center;">'.$reply_name.' replied to '.$name.'</div>
<div style="width:80%; text-align:center;">'.$reply_comment.'<br><br></div>
</div>
';
}//end of replies while loop
}//end of comments while loop
?>
</body>
</html>
解决方案
递归
这是递归解决方案的一个快速示例,使用数组作为数据库查询的模型。
一些重要的注意事项:
注意结构:所有数据获取和操作都是在打印出单个事物之前完成的。
这作为一个对象会好得多,它可以避免
GLOBAL
函数内的标签,但这会让你指向那个方向。递归函数应该总是有一个测试来停止递归,以防万一出现问题。这个例子没有!您可以使用静态变量来计算递归并在达到某个限制时停止。
<?php
// mock data of "select * from comments"
$dbRows = [
1 => ['name' => 'BigBadProducer1', 'comment' => 'I love this vst! I use it all the time!', 'reply_id' => 0],
2 => ['name' => 'DrummaBoy504', 'comment' => 'Hey, this is Drumma from Drum Squad!', 'reply_id' => 0],
3 => ['name' => 'Mike Smith', 'comment' => 'How did you get the vst to sound so good like that...', 'reply_id' => 1],
4 => ['name' => 'BigBadProducer1', 'comment' => 'Yes, I learned how to tweak it from YouTube Mike S...', 'reply_id' => 3],
5 => ['name' => 'SmoothBeatz3', 'comment' => 'Dude, Ive been looking for a vst like this for a l...', 'reply_id' => 0],
6 => ['name' => 'FanBoy123', 'comment' => 'Hey Drumma, when are you going to release a new hi...', 'reply_id' => 2],
7 => ['name' => 'Mike Johnson', 'comment' => 'Hey Fanboy123, why are you such a fanboy of Drum S...', 'reply_id' => 6],
];
// mock data of "select id from comments where reply_id=?"
$children = [];
foreach($dbRows as $id => $row) {
$reply_id = $row['reply_id'];
$children[$reply_id][] = $id;
}
// format row into html
function formatRow($row, $reply_name='unknown') {
$out =<<<FORMATROW
<div class="replies" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div style="width:80%; text-align:center;">{$row['name']} replied to {$reply_name}</div>
<div style="width:80%; text-align:center;">{$row['comment']}<br><br></div>
</div>
FORMATROW;
return $out;
}
// mock of database CRUD function "select * from comments where id=?"
function find($id) {
global $dbRows;
if(array_key_exists($id, $dbRows)) {
return $dbRows[$id];
}
}
// mock of database CRUD function "select id from comments where reply_id=?"
function findChildren($id) {
global $children;
if($id == 0) { return []; }
$out = [];
if(array_key_exists($id, $children)) {
$out = $children[$id];
}
return $out;
}
function getRepliesTo($id) {
// test to end recursion
// if(!$id) { return; }
// get parent name
$row = find($id);
$parent_name = $row['name'];
// start indented list every time a traversal is called
$out = "<ul>\n";
// list of child ids. if no children are found,
// assigns an empty array so that foreach won't barf
$children = findChildren($id);
foreach($children as $cid) {
// if there are children, capture their reply and then call this same function to get replies to this reply
if($reply= find($cid)) {
$out .= ' <li>' . formatRow($reply, $parent_name);
$out .= getRepliesTo($cid);
$out .= "</li>\n";
}
}
$out .= "</ul>\n";
return $out;
}
// print_r(find(1));
// print_r(findChildren(1));
// print_r(findChildren(2));
// print traverse(1);
$id = 1; // $id = (int)$_GET['id'];
$commentData = find($id);
$replyHtml = getRepliesTo($id);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<body>
<div class="comments" style="position:relative; margin:auto; width:500px; border:1px solid black; margin-top:1px;">
<div><?= $commentData['name'] ?></div>
<div><?= $commentData['comment'] ?><br><br></div>
</div>
<?= replyHtml ?>
</body>
</html>
物化路径
另一种方法是使用物化路径查询,这需要对reply_id 字段稍作更改:
$dbRows = [
1 => ['name' => 'BigBadProducer1', 'comment' => 'I love this vst! I use it all the time!', 'reply_id' => '/0/1'],
2 => ['name' => 'DrummaBoy504', 'comment' => 'Hey, this is Drumma from Drum Squad!', 'reply_id' => '/0/2'],
3 => ['name' => 'Mike Smith', 'comment' => 'How did you get the vst to sound so good like that...', 'reply_id' => '/0/1/3'],
4 => ['name' => 'BigBadProducer1', 'comment' => 'Yes, I learned how to tweak it from YouTube Mike S...', 'reply_id' => '/0/1/3/4'],
5 => ['name' => 'SmoothBeatz3', 'comment' => 'Dude, Ive been looking for a vst like this for a l...', 'reply_id' => '/0/5'],
6 => ['name' => 'FanBoy123', 'comment' => 'Hey Drumma, when are you going to release a new hi...', 'reply_id' => '/0/2/6'],
7 => ['name' => 'Mike Johnson', 'comment' => 'Hey Fanboy123, why are you such a fanboy of Drum S...', 'reply_id' => '/0/2/6/7'],
];
想找到 2 的所有后代吗? select * from comments where reply_id like '/0/2%'
推荐阅读
- php - 在 PHP 中的页面之间传递值
- c# - AuthenticationManager.GetExternalLoginInfoAsync 始终为空
- sql-server - 在下面的 Sql Query 上需要帮助
- javascript - Firebase 服务工作者 - 如何服务工作者在 Firebase 上实时监听更改数据库?
- javascript - 如何在老虎机 jquery 上始终显示 3 位数字
- android - adb 命令在环境显示设置中禁用“始终开启”和“抬起以检查手机”
- dataset - 如何在 Pytorch 中创建类似 MNIST 的数据集?
- java - “如何使用 java 在 android 的画布上添加文本字段?”
- java - 如何编写硒脚本以从列表中获取随机字母数字
- arrays - 试图将json数组转换为nodejs中的多个json对象