Remove data from multiple table

[Reiss]

Hi Guys

I've written a query that just seems too long winded, I'm sure there is a more efficient way to remove data from multiple tables. They all have the same constant - *.email

here are my current working queries -

$temp_var = "DELETE FROM user WHERE user.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM session WHERE session.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM seeker WHERE seeker.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM profile WHERE profile.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM organiser WHERE organiser.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM mail WHERE mail.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM login WHERE login.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM house WHERE house.email = '$email';";
$data_query = (mysql_query($temp_var, $db));
$temp_var = "DELETE FROM favorites WHERE favorites.email = '$email';";
$data_query = (mysql_query($temp_var, $db));

is there a way to delete from all the tables with only 1 query?

thanks

[Vege]

If your using myisam:
DELETE user, session,seeker,profile,organiser,mail,login,house,favorites
FROM user
JOIN session ON (session.email = user.email)
JOIN seeker ON (seeker.email = user.email)
JOIN profile ON (profile.email = user.email)
JOIN organiser ON (organiser.email = user.email)
JOIN mail ON (mail.email = user.email)
JOIN login ON (login.email = user.email)
JOIN house ON (house.email = user.email)
JOIN favorites ON (favorites.email = user.email)
WHERE user.email = '$email'

If your using INNODB it's allot easier with relations.

[Reiss]

thanks Vege

Will give that a go

[sandstorm]

Are you using MYSQL?

If so,

DELETE FROM session, seeker, profile, organiser, mail, login, house, favourites USING t1 WHERE t1.email='$email';

Im not sure whether this will work or if you have to say something like USING t1,t2,t3... etc.

See http://dev.mysql.com/doc/refman/5.1/en/delete.html

[Reiss]

Thanks sandstorm, yes using MYSQL 4.1.22 Type MyISAM.

Going to create a new account later to try out the 2 delete queries.

thanks guys