ORDER BY is being ignored

[xwishmasterx]

I got this code from a previous thread:

 

mysql_query("SET @rows = 0;");
$res = mysql_query("SELECT @rows:=@rows+1 AS view_rank,COUNT(id) AS views, credit_members_id FROM vtp_tracking GROUP BY credit_members_id ORDER BY views DESC");
$n   = array(1 => 'st', 2 => 'nd', 3 => 'rd');
while($row = mysql_fetch_row($res))
{
   if ( $row[2] != $members_id )
      continue;
   if ( substr($row[0], -1) < 4 )
   {
      $row[0] .= $n[$row[0]];
   }
   else
   {
      $row[0] .= 'th';
   }
   echo ' You are in ' . $row[0] . ' place with ' . number_format($row[1]) . ' views.';
   break;
}

 

Everything seems ok except it orders by the "credit_members_id" and not "views" as entered.

 

Can someone explain why, and how to fix this?

[MarPlo]

Hi,

Try remove GROUP BY credit_members_id, so the query will be this:

$res = mysql_query("SELECT @rows:=@rows+1 AS view_rank,COUNT(id) AS views, credit_members_id FROM vtp_tracking ORDER BY views DESC");

[Psycho]

Ok, aside from your stated problem there are some things in that code that don't make sense. For example, why do you have these lines

   if ( $row[2] != $members_id )
      continue;

Don't query querying all records when you only want the records where the credit_member_id matches that value. Use a WHERE clause to only return the records you want. You are already using the DB variable @rows - so no need to get ALL records

 

Second, you are grouping by credit_members_id, but calculating view on id - that doesn't seem right.

 

Third, your logic to add the ordinal suffixes ("st', 'nd' and 'rd') has a problem. The values 11, 12, and 13 will be given the wrong value. There are some simple functions you can use if you just google.

 

Lastly, use the "names' of your values from the query instead of referring to them by their index. It makes your code easier to read and less error prone (i.e. adding/removing fields from the SELECT statement could break your code).

 

Give this a try

function ordinal_suffix($n)
{
     $n_last = $n % 100;
     if (($n_last > 10 && $n_last < 14)){ return "{$n}th"; }
     switch(substr($n, -1))
     {
          case '1':    return "{$n}st";
          case '2':    return "{$n}nd";
          case '3':    return "{$n}rd";
          default:     return "{$n}th";
     }
}

mysql_query("SET @rank = 0;");
$query = "SELECT rank, views
          FROM (SELECT @rank:=@rank+1 AS rank, COUNT(credit_members_id) AS views
                FROM vtp_tracking
                GROUP BY credit_members_id
                ORDER BY views DESC)
          WHERE credit_members_id = '$members_id'"
$result = mysql_query($query);
if(!$result)
{
   echo "No result found.";
}
else
{
    $row = mysql_fetch_assoc($result);
   $rank =  ordinal_suffix($row['rank']);
   echo " You are in {$rank} place with {$row['views']} views.";
}