Top Content

[.Darkman]

Hello Everybody,

 

I need some help displaying top content from my database.

 

For eg,

 

I have an article system.

I have two tables in the DB.

One is articles : containing article_id, title, and the article columns.

 

Other is the rating table containing article_id and a rating (btw 0-5).

 

 

Now what i want to do is display the Top 10 articles on the basis of their rating.

How do i do it ? I have thought in many ways. But none worked.

 

Please Help

 

 

Thank You

 

 

[benjaminbeazy]

$sql = "SELECT articles.article_id as id, articles.title, articles.article, rating.article_id, rating.rating FROM `articles` JOIN `rating` ON (articles.article_id = rating.article_id) ORDER BY `rating.rating` DESC LIMIT 0, 10";

 

anybody wanna back me up on this one...

[.Darkman]

Ok that helped a little.

 

But the rating table does not contain just one rating per article.

 

It contains many ratings - one by each user for each article.

[benjaminbeazy]

 

$sql = "SELECT rating.article_id as id, AVG(rating.rating) as article_rating, articles.article_id, article.title, article.article FROM rating JOIN `articles` ON (rating.article_id = articles.article_id) GROUP BY id ORDER BY `rating` DESC LIMIT 0,10";

$result = mysql_query($sql) or die(mysql_error());

while($row ....

[benjaminbeazy]

i'm not sure but give it a whirl.  should give u some ideas.

[.Darkman]

Okay, that may be complicated.

 

How about on a del.icio.us site ?

 

A link is saved. In the links table, the link and user_id are stored.

 

Top links are shown on the basis of how many people have saved it.

 

(i.e) a link with most occurrences in the links table will be on top and with least occurrences will be on bottom.

 

How shall this be done ?

[benjaminbeazy]

$sql = "SELECT COUNT(link) as count, user_id FROM `links` GROUP BY `user_id` ORDER BY `count` DESC LIMIT 0,10";

$result = mysql_query($sql) or die(mysql_error());

while($row ....

 

im new to grouping and counting and avgs in mysql, but thatSHOULD work...

[.Darkman]

I will try this later today.

 

 

Thanks,

[benjaminbeazy]

yep