CODEWITHSUNDEEP
phpmysql
DragonFire
DragonFire

Reputation: 4092

Delete from multiple tables having common field

Hi I have two tables as per following.....

tbl_favorite_properties

1 favorite_properties_id

2 favorite_properties_property_id

3 favorite_properties_user_id

tbl_property

1 property_id

2 property_user_id

Now i want to delete fields based on user which is straight forward as below

$user_id = $_SESSION['user_id'];

$delete_tbl_favorite_properties = $con->prepare("DELETE FROM tbl_favorite_properties WHERE favorite_properties_user_id='$user_id'");
$delete_tbl_favorite_properties-> execute();

$delete_tbl_property = $con->prepare("DELETE FROM tbl_property WHERE property_user_id='$user_id'");
$delete_tbl_property-> execute();

Now I want to delete all properties in tbl_favorite_properties (favorite_properties_property_id) which match this users properties in tbl_property (property_id)

I am already able to do this in innodb cascade delete using mysql, but need a php solution.

there is a solution here How do I delete row with primary key using foreign key from other table?

but there the column names are the same and mine are different...

i am new to structuring queries in multiple tables...

Upvotes: 1

Views: 76

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I think the behavior you want is to remove properties in tbl_property belonging to a certain user, but then to also delete the corresponding favorites in tbl_favorite_properties:

DELETE t1, t2
FROM tbl_property t1
LEFT JOIN tbl_favorite_properties t2
    ON t1.property_id = t2.favorite_properties_property_id
WHERE t1.property_user_id = '$user_id'

What threw me off initially is that both tables have a user_id column. You might not need the user_id in tbl_favorite_properties if you always plan to enter that table via a join from tbl_property.

Upvotes: 1

Related Questions