删除内连接行
我创建了两个表,简单注释和评论者,并将它们连接到INNER JOIN
。删除内连接行
- Simplecomments是每个评论者的每一个细节,涉及其评论,reg_date,commentorid等...
- 评论者是评论者的个人信息有下列:ID,姓名,电子邮件..
我已经加入他们成功,但我很难从联合表中删除。
我想使它像这样的逻辑:
如果有一个名为--let评注的最后一行说A--然后同时删除他/她的评论详细信息和他/她自己从桌子上。
否则如果A已评论很多次,有不同的意见,删除了他/她的意见的细节,但让他/她的个人信息仍然因为A有没有其他意见。
这是我如何做了它:
if (!empty($_POST["delete"]))
{
foreach ($_POST["delete"] as $key => $value)
{
$resultid = $conn->query("SELECT commentorid FROM `simplecomments` WHERE id=".$value);
$rowid = $resultid->fetch_assoc();
$outputdelete = $rowid["name"] . " has been deleted" . "<br>";
$deletedname = $deletedname.$outputdelete;
$RES = mysql_num_rows($resultid);
$counter = 0;
while($row = $RES)
{
//IF IT'S LAST ROW, DELETE COMMENTOR AND HIS/HER COMMENTDETAILS
if(++$counter == $results) {
$resultid = $conn->query("DELETE FROM `commentor`");
}
//ELSE JUST DELETE HIS/HER COMMENTDETAILS, LET HIS/HER INFO REMAIN
else{
$resultid = $conn->query("DELETE FROM `simplecomments` WHERE id=".$value);
}
}
}
}
但是代码将无法正常工作。我得到一个错误:
Warning: mysql_num_rows() expects parameter 1 to be resource [..]...
考虑运行DELETE...INNER JOIN
和DELETE
与子查询条件语句,避免PHP查询获取循环与if/else
的逻辑似乎是以下几点:
- 删除任何评注者的个人资料,如果评论他/她只有一条评论
- 如果他/她有多个(即多个)评论,则只删除评论者的评论。
是的,所有三个DELETE
可以在所有ID上同时运行,因为互斥条件放在前两个和最后一个之间。因此,前两个影响行或最后一个影响每次迭代的行。未受影响的人将从两个表中删除零行。
此外,simplecomments记录首先删除,因为这个表可能与评注外键约束,因为它一个一对多的关系。最后,下面假设评论 ID传递到循环(不是评论或 id)。
PHP(使用参数,假定$ conn是一个mysqli的连接对象)
foreach ($_POST["delete"] as $key => $value) {
// DELETE COMMENTS AND THEN PROFILE FOR COMMENTORS WITH ONE POST
$sql = "DELETE FROM `simplecomments` s
WHERE s.id = ?
AND (SELECT COUNT(*) FROM `simplecomments` sub
WHERE sub.commentorid = s.commentorid) = 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $value);
$stmt->execute();
$stmt->close();
$sql = "DELETE c.* FROM `simplecomments` c
INNER JOIN `simplecomments` s ON s.commentorid = c.id
WHERE s.id = ?
AND (SELECT COUNT(*) FROM `simplecomments` sub
WHERE sub.commentorid = s.commentorid) = 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $value);
$stmt->execute();
$stmt->close();
// DELETE COMMENTS FOR COMMENTORS WITH MULTIPLE POSTS BUT KEEP PROFILE
$sql = "DELETE FROM `simplecomments` s
WHERE s.id = ?
AND (SELECT COUNT(*) FROM `simplecomments` sub
WHERE sub.commentorid = s.commentorid) > 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $value);
$stmt->execute();
$stmt->close();
}
或者,对于DRY-ER方法中,循环的SQL语句中的阵列:
$sqls = array(
0 => "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1",
1 => "DELETE c.* FROM `simplecomments` c INNER JOIN `simplecomments` s ON s.commentorid = c.id WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1",
2 => "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) > 1"
);
foreach ($_POST["delete"] as $key => $value) {
foreach($sqls as $sql) {
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $value);
$stmt->execute();
$stmt->close();
}
}
非常感谢你的男人,感激它!然而,c代表评论者,s代表简单评论和sub? –
太棒了!乐意效劳。是的,这些字母是实际表格的表别名,有助于缩短查询时间,并有助于在使用相同表格的子查询和外部查询之间进行关联。 – Parfait
请发布你得到的结果和你期望得到的结果 –
@BrianDewhirst我得到一个错误..“警告:mysql_num_rows()期望参数1是资源,”.. 我在最后两段中的问题中已经说明了我希望如何。 –
https://*.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row -expects-parameter-1 –