[RESOLVED] One query for two tables

bumbar

Hallo!

I have two tables 'users' and 'user_files'

CREATE TABLE users (
user_id int(11) unsigned NOT NULL auto_increment,
username varchar(100) NOT NULL default '',
password varchar(100) NOT NULL default ''
PRIMARY KEY (user_id)
)

CREATE TABLE user_files (
file_id int(11) unsigned NOT NULL auto_increment,
user_id int(11) NOT NULL default '0',
file_url varchar(200) NOT NULL default ''
PRIMARY KEY (file_id)
)

Table 'user_files' contain user files.

Table 'users' there is relationship with a table 'user_files' in the fields 'user_id'.

How do I can only with one query (DELETE) erase the user from the table 'users' and its files from the table 'user_files'?

Thanx!😉

laserlight

One way is to declare a foreign key constraint that user_files.user_id references users(user_id), then have it set to ON DELETE CASCADE. This way, deleting a user would automatically delete the user's files.

If not, you would simply DELETE FROM user_files WHERE user_id=$deleted_user_id.

NogDog

I believe you can do something like:

$sql = "DELETE users FROM users 
LEFT JOIN user_files ON users.user_id = user_files.user_id
WHERE users.user_id = $id";

laserlight

If you already know the user_id, a join is not needed here.

bumbar

Thank you folk!

I tried that

$sql = "DELETE users FROM users LEFT JOIN user_files ON users.user_id =user_files.user_id WHERE users.user_id = $id";

but the user was deleted only, its files in table 'user_files' remained ...

😕

bumbar

OOOOOOOOps!

I find the bug....

$sql = "DELETE users, user_files FROM users LEFT JOIN user_files ON users.user_id =user_files.user_id WHERE users.user_id = $id";

here is the problem $sql = "DELETE users, user_files .......

And the request is solved! 😉