[SOLVED] How to I query multiple tables?

[pneudralics]

I'm trying to check 4 different tables. Saw some sites about combining 2 tables but didn't quite get it. Can I only combine 2 tables? Can fields have the same name?

 

I don't want to combine them but to search all 4 and echo out all the results where touser = 10.

 

//Trying to select from comments1 comments2 comments3 comments4
//Check all 4 tables with field touser = 10
SELECT * FROM comments1 WHERE touser = '10'

[Ken2k7]

What are the column names of each table?

[pneudralics]

All the tables have the same columns except the last one

 

comments1

id, touser, fromuser, comment, image, icon

comments2

id, touser, fromuser, comment, image, avatar

comments3

id, touser, fromuser, comment, image, wallpaper

comments4

id, touser, fromuser, comment, image, banner

 

I want to check all tables for 'touser' and show everything that belongs to touser

[Ken2k7]

Wow, and are there specific columns you want to select or all of them?

[pneudralics]

 

Users browse through icons, avatars, wallpapers, banners.

They then can comment on one of the image which is then sent to the database with the image field they commented on.

I'm working on an approval system where the comment is first sent to temp tables similiar to what I posted and once it's approved the info will be inserted into the actual table.

 

So I'm trying to be able to see if I can get all the information from the different tables to display for the 'touser'. touser will be able to see all the comments posted waiting approval. Once approve data will be inserted to the actual table.

 

I know can do this by using multiple querys, but I'm not sure how to paginate all the results from the different tables. So I'm wondering if I can get info from all the tables then use only one paginate to paginate all the results from the tables instead of having 4 different paginates on one page.

 

So...what's a better way to do this?

Should I just make 4 separate pages?

 

[Ken2k7]

Very messy, but meh ...

SELECT c1.id AS comments1_id, c2.id AS comments2_id, c3.id AS comments3_id, c4.id AS comments4_id,
           c1.fromuser AS comments1_fromuser, c2.fromuser AS comments2_fromuser, c3.fromuser AS comments3_fromuser, c4.fromuser AS comments4_fromuser,
           c1.comment AS comments1_comment, c2.comment AS comments2_comment, c3.comment AS comments3_comment, c4.comment AS comments4_comment,
           c1.image AS comments1_image, c2.image AS comments2_image, c3.image AS comments3_image, c4.image AS comments4_image,
           c1.touser AS touser
FROM comments1 c1
INNER JOIN comments2 c2 ON c1.touser = c2.touser
INNER JOIN comments3 c3 ON c1.touser = c3.touser
INNER JOIN comments4 c4 ON c1.touser = c4.touser
WHERE c1.touser = 10;

[pneudralics]

Thanks for the help.