PHP & MySQL - How to delete a users records from two tables.

Question

Hello I'm new to PHP & MySQL and I was wondering how can I delete all of user_id 3 records from these two tables below using PHP & MySQL?

My MySQL tables

CREATE TABLE ls (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
skill VARCHAR(255) DEFAULT NULL,
experience VARCHAR(22) DEFAULT NULL,
self_grade VARCHAR(10) DEFAULT NULL,
date_created DATETIME NOT NULL,
date_updated DATETIME DEFAULT NULL,
PRIMARY KEY (id)
);


CREATE TABLE us (
id INT(13) UNSIGNED NOT NULL AUTO_INCREMENT,
skill_id INT(13) UNSIGNED NOT NULL,
user_id INT(13) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

us table values

id  skill_id    user_id
9   7           3
10  8           3
11  9           3
12  10          3
13  17          6

ls table values

id  skill   experience      self_grade  date_created            date_updated
7   html    5 years         A           2010-10-19 07:11:08     2010-10-19 07:12:06
8   jquery  10 years        B           2010-10-19 07:11:27     2010-10-19 07:12:06
9   css     1 year          NULL        2010-10-19 07:11:38     2010-10-19 07:12:06
10  php     2 years         C           2010-10-19 07:13:05     NULL
17  php     2 years         C           2010-10-19 07:19:05     NULL

Answer 1

Yes, you can use a multi-table DELETE statement, eg.

DELETE FROM ls, us USING ls
    JOIN us ON us.skill_id = ls.id
WHERE ls.user_id = 3

Answer 2

You could fetch all rows belonging to user_id = 3 and then build a DELETE statement with a generated WHERE clause.

But, however, I'm wondering if you have an inappropriate table design as you have a m:n relation between user and skill and you want to delete all skills the user with id 3 is related with. But this implicates a 1:m relation between user and skill (1 user has multiple skills, not sharing the skills with others). Or maybe I got something wrong...?

Answer 3

MySQL does support multi-table deletions, use:

DELETE a, b
  FROM ls a 
  JOIN us b ON b.skill_id = a.id
 WHERE b.user_id = 3