hello,
I have a table that contains information about user uploads, such as the username, filepath, id, views and date
I then have a table which contains the info for the rating of the uploads, including username, imageID, ratingID and then rating.
the average rating is found from a bit of variable mathematics.
up until now ive ordered the results by the amount of views so i could simply 'ORDER BY views' within the first sql query below (you can also see i also split the results into dates, i have a seperate page for the last 24 hours, last week, month and then all time)
But now I want to create a 'best of' page, which orders the results by the ratings, however im unsure how to do this as the rating is found by the variables and not stored in the ratings table.
One thing i thought was: to query the uploads table, then find the average ratings with the second table and variables etc, and then insert into the uploads table that average rating (obviously id need to create a new field for it). then after all of this i could have another query for the uploads table and ORDER BY ratings
this all seems pretty long winded to me though, and i was wondering if anyone can see a better way? or just to confirm that my thought process above would work :queasy:
<?
// select appropriate results from the UPLOADS table:
$sql =("SELECT * FROM uploads WHERE TIMEDIFF('".$today."',date) < '24:00:00'");
$result = mysql_query($sql);
// count total number of appropriate listings:
$tcount = mysql_num_rows($result);
// count number of pages:
$tpages = ($tcount) ? ceil($tcount/$rpp) : 1; // total pages, last page number
include_once("pagination2.php");
echo paginate_two($reload, $page, $tpages, $adjacents);
$count = 0;
$i = ($page-1)*$rpp;
while(($count<$rpp) && ($i<$tcount)) {
mysql_data_seek($result,$i);
$query = mysql_fetch_array($result);
$imageID = $query['id']; //get the image ID from the database
$imageURL = $query['filepath']; //get the image URL from the database
// output each row:
$rating_runningtotal=0;
$ratingsQuery = mysql_query("SELECT * FROM ratings WHERE imageID ='$imageID'")or die(mysql_error()); //query the database for all ratings which are related to this image.
$numberOfRatings = mysql_num_rows($ratingsQuery); //get the number of ratings this image has
while($getRatings = mysql_fetch_array($ratingsQuery)){ //loop through each rating
$rating = $getRatings['rating']; //get the actual rating and assign it to a variable
$rating_runningtotal = $rating_runningtotal + $rating; //add the rating to the running total
}
$image_rating = round(($rating_runningtotal / $numberOfRatings),1); //this will find the average of all the ratings and round to the nearest whole number
$ID = $query['id'];
?>
<a href="viewArticle.php?ID=<? echo $ID?>"><div id="mainWindow">
<div id="image"><img src="<? echo $query['filepath']?>" class="image"/></div>
<div id="smallInfo">
<div id="smallTitle"><? echo $query['title']?></div>
<div id="smallUsername">by <? echo $query['username']?></div>
<div id="smallRating"><? echo "<font color='#66c8c5'><b>$image_rating</b></font>"?>/10</div>
<div id="smallViews">views: <? echo $query['views']?></div>
