CODEWITHSUNDEEP
phpmysqlsql
Robin Svensson
Robin Svensson

Reputation: 3

PHP SELECT data from multiple tables

I have 3 tables; I need to retrieve data from to a "Latest activity" feed. I have tried to fetch the data as following:

$sql = "
SELECT comments.comment_id, comments.comment_time,
   support.ticket_id, support.ticket_date,
   images.image_id, images.image_time
FROM
   comments,
   support,
   images
WHERE
   comments.comment_time >= 1408557172
OR
   support.ticket_date >= 1408557172
OR
   images.image_time >= 1408557172
";

Where time is the start of this day, but when I loop through the array I get a lot of duplicates. I know I am doing something wrong but I don't know how to fix it.

Upvotes: 0

Views: 77

Answers (1)

ffflabs
ffflabs

Reputation: 17481

the UNION ALL operator lets you list results from several tables. Every table must have equal quantity of columns, so I took the liberty to alias them and add an origin column so you can differentiate them.

SELECT comment_id as element_id, 
       comment_time as element_time, 
       'comments' as origin
FROM comments
WHERE comments.comment_time >= 1408557172

UNION ALL

SELECT ticket_id as element_id, 
       ticket_date as element_time, 
       'tickets' as origin
FROM support
WHERE support.ticket_date >= 1408557172

UNION ALL

SELECT image_id as element_id, 
       image_time as element_time, 
       'images' as origin
FROM images
WHERE images.image_time >= 1408557172

Upvotes: 3

Related Questions