Jump to content

[SOLVED] Query Question


therealwesfoster

Recommended Posts

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

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

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.