[SOLVED] TOP 100 of PHOTOS, any IDEA?

[crashmaster]

Hi there,

I have now any idea how to create TOP 100 of users FOTO.

 

I have a user system. Every user can add to his profile fotos, and any another user can rate it.

 

For every foto I have in database clumns : rating, count_rates (how many people rated this foto)

 

Does anybody have idea how to speculate with this data ?

 

For example I have 1 idea, but I dont know how to improve it in DB level.

 

Idea:

 

to do mysql query with conditions : select user with the most count of rates and with best rating..But how to do it in mysql_query ??

[phpSensei]

I don't understand what your actually trying to do but

 

<?php

$result = mysql_query("SELECT * FROM rating_table ORDER BY rating_column DESC LIMIT 100")
             or die(mysql_error());

    while($row = mysql_fetch_array($result)){
         
         $userid         = $row['user_id']; // Id of the users in which the photo was rated
         $users          = mysql_fetch_array(mysql_query("SELECT * FROM users WHERE user_id = '$userid'"));
         
            echo $users['username'] . "<br>"; // print results

}
?>

[cooldude832]

I do my ratings based on users so this is what I do

<?php
$q = "Select PhotoID  from `Photo_ratings` Where count(rating) > `50` ORDER BY AVG(rating) LIMIT 1000";
$r = mysql_query($q) or die(mysql_error());
if(mysql_num_rows($r) >0){
while($row = mysql_fetch_array($r)){
  $top100[] = "PhotoID = '".$row[0]."'";
}
}
$top100 = implode(" || ",$top100);
$q2 = "Select * from `photos` Where ".$top100;
$r2 = mysql_query($q2) or die(mysql_error());
if(mysql_num_rows($r2)>0){
while($row2 = mysql_fetch_assoc($r2)){
//Display top100
}
}
?>

 

Simple and works if each users rating is its own row

[crashmaster]

Only 1 thing I want to know:

 

How make query with conditions: select from database 100 rows order by  'rating' DESC and order by 'count_rates' DESC

 

How to do 2 ORDERS in 1 query..?? is ti possible ??

[cooldude832]

well that is why I do my ratings like

PhotoID UserID Rating

 

and then I pull averages

 

since you only have 1 row per photo you need to do math in mysql saying

ORDER BY (ratings/countratings) and youshould be all set

you can do 2 order bys look it up in the mysql manual

[crashmaster]

SELECT user_id, foto_name FROM fotos ORDER BY rating DESC, count_rates DESC

 

 

This is what I was looking for )) SOLVED !