我正在尝试制作一个脚本,检查用户是否有权查看文档,我是这样做的两个数据库表。以便列出他拥有权限的所有文档标题。
我有结果,但是当我将一个文档分配给Table-2时,可能因为超出了周期,所以没有显示出来。
如果我将它插入到循环中,而每次向Table-2添加新记录时都得到相同的标题|| (例如。表1中的标题1将显示4次)
表-1
id | document |
1 | Title 1 |
2 | Title 2 |
3 | Title 3 |
4 | Title 4 |表-2
id | userid | docid |
1 | 1 | 2 |
2 | 1 | 3 |
3 | 2 | 2 |
4 | 1 | 4 |脚本
$query = "SELECT * FROM docdistributed order by id DESC";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
$id_distributed = $row["id"];
$userdistributed = 1;
$albums_query = "SELECT * FROM docacknowledge order by id DESC";
$albums_result = mysql_query($albums_query);
while($row1 = mysql_fetch_array($albums_result))
{
$id_acknowledge = $row1["docid"];
$useracknowledge = $row1["userid"];
if ($id_distributed == $id_acknowledge && $userdistributed == $useracknowledge){
$node = 0;
} else {
$node = 1;
}
}
if($node==0){
echo'<tr style="background-color:#;">';
echo'<td>'.$row["id"].'</td>';
echo'<td>'.$row["document"].'</td>';
echo'<td>';
echo'<div class="btn-group">';
echo'<button type="button" class="btn btn-sm btn-info">Action</button>';
echo'<button type="button" class="btn btn-sm btn-info dropdown-toggle" data-toggle="dropdown" aria-expanded="false">';
echo'<span class="caret"></span>';
echo'<span class="sr-only">Toggle Dropdown</span>';
echo'</button>';
echo'<ul class="dropdown-menu" role="menu">';
echo'<li><a href="#">Open</a></li>';
echo'<li><a href="#">Download</a></li>';
echo'</ul>';
echo'</div>';
echo'</td>';
echo'</tr>';
}
if($node==1){
echo'<tr style="background-color:#FFFFDF;">';
echo'<td>'.$row["id"].'</td>';
echo'<td>'.$row["document"].'</td>';
echo'<td>';
echo'<div class="btn-group">';
echo'<button type="button" class="btn btn-sm btn-info">Action</button>';
echo'<button type="button" class="btn btn-sm btn-info dropdown-toggle" data-toggle="dropdown" aria-expanded="false">';
echo'<span class="caret"></span>';
echo'<span class="sr-only">Toggle Dropdown</span>';
echo'</button>';
echo'<ul class="dropdown-menu" role="menu">';
echo'<form id="ack" action="ack_bulletin.php" method="post"><input type="text" name="docid" value="'.$row["id"].'"><input type="text" name="userid" value="'.$userdistributed.'"><li onclick="ack.submit();"><a href="#">Acknowledge</a></li></form>';
echo'</ul>';
echo'</div>';
echo'</td>';
echo'</tr>';
}
}
?>发布于 2015-09-22 22:10:55
您应该在两个数据库表上利用MySQL LEFT JOIN,这样您就不需要执行这些嵌套循环,只需在1 SELECT中获取所需的数据。尝试使用此查询:
SELECT *
FROM docdistributed dd
LEFT JOIN docacknowledge da ON dd.id = da.docid
ORDER BY dd.id DESC这有效地取代了使用基于PHP的逻辑的需要,如下所示:
if ($id_distributed == $id_acknowledge && $userdistributed == $useracknowledge) {这反过来又防止了像所描述的那样的两难境地,在这种情况下,您可以选择在脚本的某些结合点上哪些位的关系数据是可用的。
https://stackoverflow.com/questions/32718956
复制相似问题