CODEWITHSUNDEEP
phpmysql
swapnil
swapnil

Reputation: 353

Delete from two table using mysql in php

I have two table category and subcategory I am trying to delete record from this table using mysql query

Query is=

$sql="Delete t1, t2 From category as t1 
       INNER JOIN  subcategory as t2 on t1.c_id = t2.c_id
       WHERE t1.c_id='$del_c_id' ";

This query only delete row from category(t1) whose primary key used into subcategory(t2) table.

Not delete row from category(t1) whose primary key not used in subcategory(t2).

Upvotes: 0

Views: 356

Answers (2)

Gavin Towey
Gavin Towey

Reputation: 3200

You need to use a LEFT JOIN instead of INNER JOIN. By definition LEFT JOIN returns all results from t1, even if they don't have a match in t2.

See this link for more info on join types: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Upvotes: 2

iLaYa ツ
iLaYa ツ

Reputation: 4017

Try CASCADE option in mysql. Subcategory will be deleted automatically if you delete the category. You need to use the InnoDB storage engine to use this feature.

Here is the accepted answer

Upvotes: 2

Related Questions