SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just released version 1.21 of my open source PHP links script ssLinks. Unfortunately there are still reports of a bizzare bug in the "Top Rated" function. I have been unable to recreate the problem some people are having and I have a suspicion that the people who are having the problem may be using an earlier version of mySQL.

    Here's the SQL that is causing the problem:
    Code:
    $links_sql = "SELECT * FROM sslinks WHERE link_validated = 'yes' AND link_numvotes > $minvotes ORDER BY (link_totalrate / link_numvotes) DESC LIMIT 0,$nol";
    It's meant to display the top $nol (usually set to 10) rated links from the links database. A link's rating is calulated using link_totalrate / link_numvotes. $minvotes is the minimum number of votes needed for a link to show up on the top rated list (to prevent links with only one vote coming top).

    A better example of what this does can be seen here:

    http://www.tfc-central.co.uk/sslinks...p?action=rated

    The code works fine on the machines I have tested it on, but some of my users are reporting that they get a database error on that page.

    I'm sure the thing that's throwing it is the ORDER BY clause, but I'm not sure how to achieve the same effect without using that syntax...

    Any ideas?

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if this is your problem, it looks interesting. From the Manual (Chapter 7.4):
    Code:
    Note that if you are using MySQL Version 3.22 (or earlier) or if you are trying to
     follow ANSI SQL, you can't use expressions in GROUP BY or ORDER BY clauses. You
     can work around this limitation by using an alias for the expression: 
    
    mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
               GROUP BY id,val ORDER BY val;
    http://www.mysql.com/documentation/m...Reference.html

    Just to add to that, I believe what it is saying is that in older versions you must use a column name or an alias in your ORDER BY clause.
    Last edited by freakysid; May 10, 2001 at 16:36.

  3. #3
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freakysid I'm certain that's the problem. I looked through the mySQL manual but completely missed that vital paragraph. Thanks


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
  •