therealwesfoster Posted July 22, 2008 Share Posted July 22, 2008 Ok, situation 1: I have 2 tables, 1 is "comments" and the other is "images". Users comment the images etc. Well on one page I'm wanting to sort the images by their number of comments. How would I create an SQL query to do this? The easy way would be to put a "total_comments" field in the "images" table, but I'll do that as a last resort. Situation 2 I'm wanting to sort the images by their rating (which is figured by dividing "pic_rating" / "pic_votes"). How can I do that simple math in an SQL query in order to get the top rated pics to show? Thanks Link to comment https://forums.phpfreaks.com/topic/116030-solved-query-question/ Share on other sites More sharing options...
mbeals Posted July 22, 2008 Share Posted July 22, 2008 first you need to count the number of comments per image: Assuming that each comment has a reference back to the image table (imageID) Select imageID, count(imageID) as number from Comments group by imageID Creates a table consisting of the image ID and number of comments with that ID. Now join it to the comments table: Select images.*, commentInfo.* from (Select imageID, count(imageID) as number from Comments group by imageID) as commentInfo join images on commentinfo.imageID = images.index order by commentInfo.number Sit 2 Select pic_rating / pic_votes as rating from images order by rating Link to comment https://forums.phpfreaks.com/topic/116030-solved-query-question/#findComment-596731 Share on other sites More sharing options...
therealwesfoster Posted July 22, 2008 Author Share Posted July 22, 2008 Wow, good call. It works. Big thanks to you man! Wes Link to comment https://forums.phpfreaks.com/topic/116030-solved-query-question/#findComment-596766 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.