Search code examples
phpmysqlsqlinner-joinsql-delete

IF Query in SQL DELETE in PHP

so I got a blog page, where useres can post "articles" and in these articles there is the possibility to write comments. I want to create a IF Query in PHP to delete the article, if only a article is there and no comment or if there is a comment delete the article and the comment in the database.

$sql = "DELETE beitrag , kommentar 
        FROM beitrag 
        INNER JOIN kommentar 
        WHERE beitrag.beitrag_id= kommentar.beitrag_id 
          and beitrag.beitrag_id=$pid";

Currently this is how my DELTE is looking. It only deletes, if there is article and a comment.

Solution

  • You should be using a LEFT JOIN instead. Use proper joining (by using ON) and use a prepared statement, see How can I prevent SQL injection in PHP?.

    LEFT JOIN with a delete means that you delete from the main table, but if there are matches in the right table (for you, that's the comments), delete those too.

    $sql = "DELETE beitrag, kommentar 
        FROM beitrag 
        LEFT JOIN kommentar 
          ON beitrag.beitrag_id= kommentar.beitrag_id 
        WHERE beitrag.beitrag_id = $pid";

    Though you can set up a foreign key relation on the comments, so that when the article is deleted, the comments gets deleted too (using the ON DELETE CASCADE attribute). That way, you just delete the article, and the comments follows.

    ALTER TABLE kommentar 
ADD FOREIGN KEY (beitrag_id) REFERENCES beitrag(beitrag_id)
ON DELETE CASCADE;

    Then delete the article with a plain deletion query

    $sql = "DELETE FROM beitrag WHERE beitrag_id = $pid";