IF Query in SQL DELETE in PHP

Question

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.

And what's your question? If you want to create something like that "IF query in PHP", what keeps you from doing it? — Nico Haase, Jun 21 '19 at 15:18
I dont know how to do it, or if there is a way to do just edit the sql statement — Tatsäch lich, Jun 21 '19 at 15:19
Sounds like you'd be better off using foreign key indexes with delete restrictions... — CD001, Jun 21 '19 at 15:21
Can't you run 2 queries which the first one deletes the article by it's ```id``` and the second one deletes it's ```comemnts``` where the ```article_id``` equals the ```id``` of the article? — Alireza A2F, Jun 21 '19 at 15:24

Qirel · Accepted Answer · 2019-06-21T15:24:22.950

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";

Thank you! LEFT JOIN worked. But as you said, I will set up a foreign key relation! — Tatsäch lich, Jun 21 '19 at 15:24

IF Query in SQL DELETE in PHP

1 Answers1