Mixing the output of multiple query results together

chronomantic

Hey everyone,

If I had a restaurant review website and wanted to show all of the latest activity (constructed by 3 queries) for a chosen restaurant ordered by the date, what would be the best way to do this? Below is a watered down example of the tables in the database.

rest
rest_id
rest_name

media
media_id
media_rest_id
media_url
media_date_added

review
review_id
review_rest_id
review_name
review_author
review_date_added

comment
comment_id
comment_rest_id
comment_text
comment_author
comment_date_added

The restaurant can have photos added to it, comments made about it and reviews written about it. So right now I have three separate queries:
1) SELECT ... FROM comment WHERE comment_rest_id = 1 ORDER BY comment_date_added
2) SELECT ... FROM review WHERE review_rest_id = 1 ORDER BY review_date_added
3) SELECT ... FROM media WHERE media_rest_id = 1 ORDER BY media_date_added

I'm outputting the results in 3 FOR EACH loops but that doesn't allow me to mix the data.

I would like it to be a list that shows each activity no matter the type, just ordered by the date. But depending on the type, there is different supporting text.. Like 'click here to read review' for reviews but 'A comment was made' for comments.

For example it might be:

12/30/09 - A review has been added, click here to read it.

12/29/09 - A comment was made:
Lorem ipsum dolor sit amet....

12/28/09 - Another comment was made:
Lorem ipsum dolor sit amet...

12/17/09 - A picture of the restaraunt was added:
< img src = "blah.jpg" />

I was debating using jquery to sort DIV elements but was wondering if there's another way with PHP / MySQL to do this?

Hopefully my description makes sense 🙂 Thanks anyone for any help on this...!

djjjozsi

as i can see, your review table and comment are very similar.
You can add a type field, and give the comment/review type.

Then an order will show you the real order.

johanafm

# $m, $r and $c are arrays of media, review and comment respectively
/* no need to actually fetch data into intermediate arrays first. do this while fetching
   each row from the result sets */
$all = array();
foreach ($m as $v)
	$all[$v['media_date_added']][] = $v;
foreach ($r as $v)
	$all[$v['review_date_added']][] = $v;
foreach ($c as $v)
	$all[$v['comment_date_added']][] = $v;

asort($all);

Also, I recommend adding "'media' as type", "'review' as type" etc to each your queries. That way, when you foreach through $all, you can check the type element to see how you should treat the data if they're to be displayed differently

dagon

you could do a union and sort on date in the sql.

chronomantic

Thanks a lot for the help everyone.

I had never used UNION before, thanks for pointing that out. I think I can accomplish it using union and, as was suggested, adding a 'type' column to the query so that I can handle the display formatting for each type.

🆒