PHP SELECT data from multiple tables

Question

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.

how are the three tables related to each other?

Linger
– Linger

2014-08-20 20:42:22 +00:00
Commented Aug 20, 2014 at 20:42 — Linger
– Linger, Commented Aug 20, 2014 at 20:42

ffflabs · Accepted Answer · 2014-08-20 20:44:14Z

3

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

answered Aug 20, 2014 at 20:44

ffflabs

17.6k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BrettFromLA Over a year ago

I didn't even really understand the OP's question until I saw your solution! Up-vote.

ffflabs Over a year ago

only if there's dupes in each of those 3 tables, but if comment_id, ticket_id and image_id are primary keys, there shouldn't be any

Robin Svensson Over a year ago

Thanks @amenadiel this solved my problem! Sorry if i were unclear with my question first time on stackowerflow : )

Collectives™ on Stack Overflow

PHP SELECT data from multiple tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related