SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Atlanta
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Query & Sort

    I need to sort the following query by rating, but the query for ratings is after the main query.

    $QueryListings need to count and grab the score and do the math, to be able to sort by rating?

    The data and rating tables are separated.

    Main Table
    listing
    `pk_listId` int(11), `listTitle` varchar(200)

    Rating Table
    listingrate
    `ListingRateID` int(10), `ListingId` int(10), `Score` tinyint(2)

    Code:
    $QueryListings = $MySQL->query"
        SELECT * FROM  listing 
        WHERE status = 'approved'
        LIMIT 10");
    
        while ($ListRow = $MySQL->fetch_array($QueryListings))
        {
            $ListId = $ListRow['pk_listId'];
            $ListTitle = $ListRow['listTitle'];
                    
            // Count Total Voters
            $TotalVoters = $MySQL->fetch_one_array("
                SELECT COUNT(ListingId) AS Count FROM listingrate
                WHERE ListingId = '$ListId'");
            $TotalVoters = $TotalVoters['Count'];
                    
             // Let get the total score
            $TotalScore = $MySQL->fetch_one_array("
                SELECT SUM(Score) AS TotalScore FROM listingrate
                WHERE ListingId = '$ListId'");
             $TotalScore = $TotalScore['TotalScore'];
                    
            // Do we have enough voters
            if ($TotalVoters == 0)
            {
                Print "$ListTitle Not Rated<br><br>";
            }
              elseif ($TotalVoters <= 2)
            {
                Print "$ListTitle Needs More Votes<br><br>";
            }
              elseif ($TotalVoters >= 2)
            {
                 // Get the final score and round it.
                $avgRating = round($TotalScore / $TotalVoters, 1);
                Print "$ListTitle Avg: $avgRating<br><br>";
            }
        }

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select listing.pk_listId
         , listing.listTitle
         , case 
            when count(listingrate.ListingId) = 0 
            then 'Not Rated'
            when count(listingrate.ListingId) <= 2 
            then 'Needs More Votes'
            else concat('Avg: ', 
               round(sum(listingrate.Score)
                      /count(listingrate.ListingId),1))
           end as rating
      from listing 
    left outer
      join listingrate
        on listing.pk_listId
         = listingrate.ListingId
     where listing.status = 'approved'
    group
        by listing.pk_listId
         , listing.listTitle
    order 
        by rating desc      
    limit 10
    most efficient, because no looping required

    caution: watch the sum of listingrate.Score, it might overflow since you defined it as tinyint -- i haven't tested this but you should
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Atlanta
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Worked Perfect,

    Thank you so much r937.
    And thanks for the caution, I will check into that.




    Happy Holidays
    Last edited by zygote; Nov 24, 2004 at 15:32.

  4. #4
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Atlanta
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok so "0" voters or less than "2" voters, were showing first on the list.
    Is there anyway for, "0" or "<= 2" to be listed after the ones that have higher ratings.

    My Results
    --Listing 3
    ----Not Rated
    --Listing 1
    ----Avg: 7.0
    --Listing 2
    ----Avg: 6.0

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ah, yes, i overlooked that, sorry

    in the CASE expression, slip a blank after the quote and in front of the words Not and Needs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Atlanta
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again thats a fine job you've done.

    Have a good one.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •