删除带有内部联接的行 [英] Deleting a row with inner join

问题描述

我已经创建了两个表 simplecomments 和 commentors ，并将它们与INNER JOIN连接起来.

I've created two tables, simplecomments and commentors, and connected them with INNER JOIN.

• 简单注释是每个评论者的详细信息，包括他们的评论，reg_date，commentorid等...
• 评论员是评论者的个人信息，其中包括以下几列:ID，姓名，电子邮件.

我已经成功加入了他们，但是我发现很难从加入的表中删除它们.

I've joined them successfully, however I'm finding it hard to delete from the joined table.

我想使它像这样的逻辑:

I want to make it like this logic:

1. 如果，则有一个评论程序的最后一行称为-让我们说A-然后从表中删除他/她的评论详细信息和A本人.

1. If there's last row of a commentor called --let's say A-- then delete both his/her comment details and A himself/herself from the table.

其他如果A曾用不同的评论发表过很多次，请删除他/她的评论详细信息，但由于A那里还有其他评论，因此保留他/她的个人信息.

Else if A has commented plenty of times, with different comments, delete his/her comment details, but let his/her personal info remain since A has other comments there.

这就是我做到的方式:

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

}

但是代码不起作用.我收到错误消息:

However code won't work. I get an error:

警告:mysql_num_rows()期望参数1为资源[..] ...

Warning: mysql_num_rows() expects parameter 1 to be resource [..]...

推荐答案

考虑使用子查询条件运行DELETE...INNER JOIN和DELETE，并避免使用if/else进行PHP查询获取循环，因为逻辑似乎如下:

Consider running DELETE...INNER JOIN and DELETE with subquery conditionals and avoid PHP query fetch looping with if/else as the logic seems to be the following:

1. 如果评论者只有一个评论，则删除他/她的个人资料和评论
2. 仅在评论者具有多个(即，不止一个)评论的情况下删除评论.

是的，所有三个DELETE可以在所有id上同时运行，因为互斥条件位于前两个和最后一个之间.因此，每次迭代前两个影响行或后一个影响行.不受影响的表将从任一表中删除零行.

And yes, all three DELETE can be run at same time across all ids since mutually exclusive conditions are placed between the first two and last one. Therefore, either first two affects rows or last one affects rows per iteration. The unaffected one(s) will delete zero rows from either table.

此外，简单注释记录也首先被删除，因为该表由于一对多的关系可能具有注释的外键约束.最后，下面假设 comment id传递到循环中(不是 commentor id).

Also, simplecomments records are deleted first since this table may have a foreign key constraint with commentor due to its one-to-many relationship. Finally, below assumes comment ids are passed into loop (not commentor 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语句:

Alternatively, for a DRY-er approach, loop SQL statements in an array:

$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();
   }
}

这篇关于删除带有内部联接的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！