Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14
  1. #1
    Member kennedrw's Avatar
    Join Date
    Mar 2006
    Posts
    72
    Member #
    12717
    I've modified this script "Transio" and "Wired" developed:
    http://www.webdesignforums.net/showthread.php?t=6180
    to fit with my page and here's how it looks:
    http://www.textbookpower.com/national/list_reviews.php

    Here's the thing: I want to order the entries 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.

    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";  
    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);
    ?>
    my site: textbookpower.com
    my email: rkennedy@ [domain above]

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    GROUP BY i.id ORDER BY (SUM(r.rating) / COUNT(r.rating)) DESC";

    Placed brackets around the divisor area.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    Member kennedrw's Avatar
    Join Date
    Mar 2006
    Posts
    72
    Member #
    12717
    Thanks Wired. How do manage to be such a prolific poster?
    my site: textbookpower.com
    my email: rkennedy@ [domain above]

  5. #4
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    umm... was that a joke?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  6. #5
    Member kennedrw's Avatar
    Join Date
    Mar 2006
    Posts
    72
    Member #
    12717
    No. I just didn't see that you're a moderator.
    my site: textbookpower.com
    my email: rkennedy@ [domain above]

  7. #6
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    oh, moderating has nothing to do w/ posting and whatnot. We just keep the rabblerousers out
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Did some testing of it... this should work:
    Code:
    SELECT i.id, i.name, i.description, i.url, IFNULL((SUM(r.rating) / COUNT(r.rating)), 0) AS avg
    FROM item AS i LEFT JOIN review AS r ON i.id = r.item_id
    GROUP BY i.id ORDER BY avg DESC

  9. #8
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    /bows down to SQL guru
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  10. #9
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    / joins Wired in bowing.

  11. #10
    Member kennedrw's Avatar
    Join Date
    Mar 2006
    Posts
    72
    Member #
    12717
    This is awesome. You should post this script somewhere for more people to be able to use. On one of the PHP sites -- I bet all sorts of people would be downloading it.
    Oh, yeah, what about auto-numbering entries based on their current rank? Because currently ranking numbers are static html and don't change as user votes change.

    See: http://www.textbookpower.com/biggies/list_reviews.php

    Maybe this is too involved or you're tired of this script though. Either way, thanks again. And I still say this should be spread to the masses more.
    my site: textbookpower.com
    my email: rkennedy@ [domain above]


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:02 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com