Need Small Change with this Ranking/Review Script.

kennedrw · 05-03-2006, 08:28 PM

This is a cool ranking/review script "Transio" and "Wired" developed:
http://www.webdesignforums.net/showthread.php?t=6180
which I have implemented on my page:
http://www.textbookpower.com/national/list_reviews.php

Here's the thing: I want to order the entries on my page by their rating automatically and for them to be numbered automatically in accordance with their ranking. "Transio" wrote out how to do it for me, but it didn't work.

Below is the code and what "Transio" instructed me to change. If anyone could help me out, I would greatly appreciate it. It's probably pretty simple to someone knowledgeable about PHP.

It also might help out anyone else using or planning to use this cool script.

"Transio" posted that I should just add the last part of this to the query, but it didn't work. How can I fix it? Transio's suggestion:

Code:

$query  = "SELECT i.id, i.name, i.description, i.url, IFNULL((SUM(r.rating) / COUNT(r.rating)), 'n/a') avg  
            FROM item i LEFT JOIN review r ON i.id = r.item_id
            GROUP BY i.id  ORDER BY SUM(r.rating) / COUNT(r.rating) DESC";

This is the code currently on my site, which is not automatically sorted:

Code:

<?php
include 'db_conn.php';
                
//-- Get a list of all items and their ratings
$query  = "SELECT i.id, i.name, i.description, i.url, IFNULL((SUM(r.rating) / COUNT(r.rating)), 'n/a') avg  
            FROM item i LEFT JOIN review r ON i.id = r.item_id 
            GROUP BY i.id  ";
$result = mysql_query($query);
      
if (mysql_num_rows($result) == 0) {
    echo '<p>There are no sites listed in this database.</p>';
    exit;
}

echo '<hr/>';
$i = 0;
 while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {     
    
    echo '<p class="avg"> Current Rating:&nbsp;&nbsp;<span class="blue">'.$row["avg"].'</span></p>';
    echo '<h3>'.$row["name"].'</h3>';
    echo '<p class="review">'.$row['description'].'</p>';
    echo '<p class="submitreview"><a class="rate" href="get_reviews.php?item_id='.$row["id"].'">Rate this 

site!</a></p>';
    echo '<hr/>';
    $i += 1;
 }        

mysql_free_result($result);
?>

Vin0rz · 05-04-2006, 06:45 PM

As far as I can see that MySQL code should work, but you can also sort it afterwards using asort().

guelphdad · 05-05-2006, 02:53 AM

Did you notice this part in the suggestion you were given?

Code:

ORDER BY SUM(r.rating) / COUNT(r.rating) DESC

Did you notice that you left that part out of your script? Since that clause does the ordering it is the reason why your results are not ordering and his is.