How to delete multiple rows of different table in a single query ?

Question

I have 4 tables users, posts, addresses and images. I want to delete all the posts, address of that user and images used in posts of that user when the user is deleted in mysql.

Answer 1

Got it ...
Here is the way ...

DELETE users, posts, addresses, images FROM users INNER JOIN addresses ON users.add_id=addresses.add_id INNER JOIN posts ON posts.user_id=users.user_id INNER JOIN images ON images.pid=posts.pid WHERE users.user_id=$_REQUEST['user_id'];

Answer 2

It can be deleted either a single or mutli line. That's why I am asking for a single line query which only users user_id.
There is a linkage with all the other tables via means of same coloum between two

addresses -> add_id -> users
images -> pid -> posts -> user_id -> users

They can be manipulated with users table with the use of user_id only.

HELP ME in some better way.

Answer 3

There is no such coloum which contain unique or common data.
Unique Coloums for each table is
user_id for users
pid for posts
add_id for addresses
and
img_id for images

Let's assume I got a user_id like 3 and that user have let's assume 26 as add_id.
Now that user posted 3 posts that are with
pid 5, pid 9 and pid 67 respectively. also that posts are using 6 images which are 1 of img_id 92 for pid 5, 2 of img_id 115 and 116 for pid 9 and 3 of img_id 765, 766 and 767 for pid 67.

Now all the other IDs are different and didn't serve with the request niether GET nor POST, the only thing which I have is user_id.

I have only user_id. which is not shared with all the tables except posts.

Answer 4

Thanks ... But the answer was found on Stackoverflow after searching and asking it everywhere.

Thanks to Stackoverflow 😊

Answer 5

I understand what I mean with same unique ID is that, take for example I have two table users and posts.

How do I know that a particular user Roe posted an article to the table post? Simple

I create a column in posts and name it inputter

then I check for Roe primary key, user_id, then I use this query to post his articles.

INSERT INTO post(article,title,inputter) VALUES($article, $title,$user_id);

Now my column inputted carries a value specifically for Roe hence when I want to query Post table again for Roe I only need say WHERE inputter = $user_id because Roe has this unique value he doesn't share with anyone else in all the tables his data is stored.

Answer 6

users and posts are connected with user_id in common.

users and addresses are connected with add_id in common.

posts and images are connected with pid in common.

I have only user_id sent from client.

I want to delete that user, address of that user, all the posts posted by that user and also images used in posts posted by that posts only.

Someone prepare a simple SQL Query for it using user_id only.

Answer 7

What I have up my sleeve will require you know the values of each primary key, user_id,add_id,pid.

$query = DELETE FROM users, post, address, images WHERE users.user_id =val AND (post.pid=val AND address. add_id =val);

Answer 8

Wow, well done

Answer 9

well it all depends on how the tables are structured, I will assume the user has unique ID that has same value in all the tables. if so, I will try to do the above like this --------

Assumptions :
1. you have connected to the database
2. All the tables are in one database so I will ignore multiple connection

Execution with PHP

$tables =array("users","posts","addresses","images");

$string = "DELETE * FROM {$tables} WHERE user_id=".$_GET['id'];

$query = mysqli_query($connection,$string) or die("invalid query:". mysqli_erro());

What next?

whatever you want to echo if the query is successful or unsuccessful.

Hope it helped and maybe works winks

Answer 10

user_id=$_GET['id'] assumes the page has a get request set from the page you initiated the delete, and the get variable is the users Unique ID in all the tables

Answer 11

Different tables have different primary key like, users have user_id, posts have pid, addresses have add_id and images have img_id and I only have user_id get via POST request and users and addresses have add_id in common, users and posts have user_id in common and posts and images have pid in common.

Answer 12

Structure of tables are
users (
user_id int primary key auto_increment,
name varchar,
dob date,
add_id int,
...
)

addresses (
add_id int primary key auto_increment,
street varchar,
town varchar,
City varchar,
state varchar,
country varchar,
zip varchar
)
posts (
pid int primary key auto_increment,
user_id int,
msg varchar,
time timestamp,
...
)
and
images (
img_id primary key auto_increment,
pid int,
src varchar
)

Would you please help me to delete the linked rows via user_id only.

Answer 13

Yes ... It's work fine for deleting the posts rows posted by that user with the help of user_id. What about the images used for that post with linkage as same as above with users and posts but here the id pid. Also mention some help for addresses. answer any single line query for this.

Answer 14

I understand what I mean with same unique ID is that, take for example I have two table users and posts.

How do I know that a particular user Roe posted an article to the table post? Simple

I create a column in posts and name it inputter

then I check for Roe primary key, user_id, then I use this query to post his articles.

INSERT INTO post(article,title,inputter) VALUES($article, $title,$user_id);

Now my column inputted carries a value specifically for Roe hence when I want to query Post table again for Roe I only need say WHERE inputter = $user_id because Roe has this unique value he doesn't share with anyone else in all the tables his data is stored.

Answer 15

Same procedure can be applied as long as you have that column synonymous to the inputter example I used in all the tables, doesn't matter what the column names are all you need do is query against the values, so if the value of say 4 are in all tables then a single line query will work. otherwise it can still work but you need more comparative operators and maybe logical operators

Answer 16

At this point just look for a value Unique to the user that's in all tables

Answer 17

Now I understand you better! Try:

$query = "DELETE * FROM users, post, address, images WHERE user_id=pid=add_id=".$_GET['id'];

Am still assuming all tables are connected to user table either by email or username and the connection is unique!

in short if your system was functioning and you where able to query from all tables for a particular user then there should be a unique value you use, and that is what I mean by the $_GET

Answer 18

now if I may ask how do you query the users posts and images and address from the information you have given with a single query? is there nothing common in all these columns?

And if there's none and you query this data with more than one query line then I doubt you can delete them with a single query line

Answer 19

The different tables didn't containing the same data @Meru, as you used 'val'. The primary keys of different tables containing different value as I shown earlier.

Answer 20

I guess, JOINS are helpful here.