counting rows...

[xwishmasterx]

Hello

 

I have a table that inserts a new row with data when a member views a page.

 

I wish to count all the rows for each member, and then be able to show what the cuurent members "position" is eg. what members has the highest row count.

 

Example. counting the rows for member_A returns 1000 rows, the number of rows for member_B returns 1500 rows.

 

How can I display for member_A that he is in "second" place?

[Andy-H]

SELECT COUNT(*) AS views FROM views_table GROUP BY user_id ORDER BY views DESC

 

 

//get and loop query
$i = 1;
$n = array(1 => 'st', 2 => 'nd', 3 => 'rd');
while($row = mysql_fetch_row($var))
{
   if ( substr($i, -1) < 4 )
   {
      $i .= $n[$i];
   }
   else
   {
      $i .= 'th';
   }
//etc.
}

[xwishmasterx]

Thanks for your reply Andy, but not sure that is what I'm looking for.

 

I am looking to show the specific member his place in the rankings, based on the count of the rows.

 

 

[Andy-H]

mysql_query("SET @rows = 0;");
$res = mysql_query("SELECT @rows:=@rows+1 AS view_rank, COUNT(*) AS views, user_id FROM views_table GROUP BY user_id ORDER BY views DESC");
$n   = array(1 => 'st', 2 => 'nd', 3 => 'rd');
while($row = mysql_fetch_row($res))
{
   if ( $row[2] == $user_id )
   {
      if ( substr($row[0], -1) < 4 )
      {
         $row[0] .= $n[$row[0]];
      }
      else
      {
         $row[0] .= 'th';
      }
      echo $row[0] . ' with ' . number_format($row[1], 2) . ' views.';
   }
}

[Andy-H]

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

 

Would be better actually, probably not the best way to do it tho.

[xwishmasterx]

I don't know if it's the right or wrong way to do it, but works exactly like I want it. Thanks a ton Andy!

 

Btw, do PM me, as I am looking for a regular go-to guy with vairous things..including paid projects:)

[xwishmasterx]

Seems I was a little to quick here. I seems it puts out the result the way I want it, however the result is not correct, as it is not displying the right number..