1

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...

Community
  • 1
  • 1
DragonFire
  • 3,722
  • 2
  • 38
  • 51
  • Your current logic for deleting records uses only the user's id and has nothing involving a relation between the two tables. – Tim Biegeleisen Mar 02 '17 at 02:48
  • favorite_properties_property_id and property_id are related – DragonFire Mar 02 '17 at 02:51
  • Is your requirement to delete all property records from `tbl_property` for a given user? – Tim Biegeleisen Mar 02 '17 at 02:55
  • no that i can already do when i delete a user, he has some properties which have been favorited by another user, but if these properties are deleted in tbl_property, then it will show error for another user in tbl_favorite_properties (because this second user has favorited properties which do not exist anymore).. hence all those properties must also be deleted from tbl_favorite_properties which belong to the user who is deleting his account – DragonFire Mar 02 '17 at 03:01
  • i suppose i can do this in two query part, first select all properties from tbl_property which this user has and put in array, then delete these properties from tbl_favorite_properties using IN function.. this will be two step query maybe someone with good knowledge can give me an elegant way to write code – DragonFire Mar 02 '17 at 03:03
  • All you need to do is delete records in both tables for a given _user_. The relationship between properties doesn't seem to play a role here, because, as you said, if we delete using the property ID then we run the risk of killing someone else's favorites. You might want to rethink your database design. – Tim Biegeleisen Mar 02 '17 at 03:05
  • it is ok, for now there is no problem in killing someone elses favorite basically a user has some properties in tbl_property, these properties can be favorited by any user in tbl_favorite_properties, but if these properties are deleted in tbl_property, then they need to be deleted from tbl_favorite_properties also – DragonFire Mar 02 '17 at 03:08

1 Answers1

1

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.

DragonFire
  • 3,722
  • 2
  • 38
  • 51
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • they are different in the sense that.. user_id : in tbl_favorite_properties it refers to the person who has favorited the property... but in tbl_property it refers to the person who has posted the property – DragonFire Mar 02 '17 at 03:19
  • @DragonFire Thanks for the comment, now I feel better about your design. – Tim Biegeleisen Mar 02 '17 at 03:22
  • this is working only thing is favorite_properties_id should be favorite_properties_property_id.... i know i name the dbase very confusingly sorry about that – DragonFire Mar 02 '17 at 03:45