mehdi mehdi - 6 months ago 9
PHP Question

multiple SELECT FROM multi tables and SORT by date

I am looking a way to select multi data from multi tables

WHERE uid = :uid

and
sort
them by
data
.

some thing like this :

SELECT * FROM
(SELECT * from comments) AND
(SELECT * from likes) AND
(SELECT * from photos) AND
(SELECT * from messages) WHERE uid = :uid ORDER BY date LIMIT 30


Actually, in a profile timeline I need to show
likes
,
comments
,
photos
and ... And all of these parts should be
sort by date
with
limitation
...

EDIT :

Select cid,comment,pid,date from `ctg_comments` where uid = 69
UNION
Select pid,date from `ctg_likes` where uid = 69
UNION
Select address,postid,date from `ctg_photos` where byuid = 69
UNION
Select postid,date from `ctg_reservation` where uid=69
Order by date limit 30


I need some of every table column but they are not same to use UNION .

Thanks .

Answer
Select * from comments where uid=:uid
UNION
Select * from likes where uid:uid
UNION
Select * from photos where uid:uid
UNION
Select * from messages where uid:uid
Order by date limit 30

This solution requires that the columns from each tables are of same type, the resultset will take the columns names of the first table. If only one or two columns differs on each table, you can just extract the similar columns by naming them instead of extracting all columns : http://dev.mysql.com/doc/refman/5.7/en/union.html

Extracting all columns with * is usually frowned on, because your code become unreadble without knowledge of the database. And it might extract more data than needed.

With your data structure you might want to try this (I guessed the column type, so correct it if needed) :

Select 'comment' as type, 'none' as address, cid, comment, pid, date from `ctg_comments` where uid = 69
UNION
Select 'like' as type, 'none' as address, -1 as cid, 'none' as comment, pid, date from `ctg_likes` where uid = 69
UNION
Select 'photo'as type, address, -1 as cid, postid as pid, 'none' as comment, date from `ctg_photos` where byuid = 69
UNION
Select 'reservation' as type, 'none' as address, -1 as cid, postid as pid, 'none' as comment, date from `ctg_reservation` where uid=69
Order by date limit 30

I added a type column so it is easier to deal with in php after (you just test this field with a switch case instead of testing if address == 'none' or cid == -1 ...).