Determining Position in Query

[Stryves]

I'm trying to determine what place someone is based on a query.

 

<?php 

Query is "SELECT clientname, sales FROM clients ORDER BY sales DESC"

$i=1;
while($query)
{
if($query['clientname']==$_POST['clientname'])
{
  $clientrank=$i;
  }
  i++;
}
?>

 

Is there any easier way to do this?

[Ken2k7]

Can you add the check for clientname = $_POST['clientname'] in the SQL?

[Stryves]

Yes I could, but then it would just show me how many sales they have.

 

I was hoping to determine where they rank VS the other staff.

[Ken2k7]

And what determines that? You didn't exactly tell us how to determine that.

[Philip]

This would work, although I dunno if I'd recommend it for super large tables:

SELECT clientname, sales, (SELECT count(*) + 1 FROM clients WHERE sales > c.sales) as position FROM clients as c ORDER BY sales DESC

 

Run that in a query, and then when looping through the results it will tell you the position of each person

 

Also, you can add in a WHERE `clientname`='NAMEHERE' to show one result, with position:

SELECT clientname, sales, (SELECT count(*) + 1 FROM clients WHERE sales > c.sales) as position FROM clients as c WHERE clientname = 'bob' ORDER BY sales DESC

[Stryves]

The query is is running, from top sales down.

 

$i=1

 

when the while is running, if the client name is not the current result, i increments, and then the while runs again.

 

If the clientname is matched during the 5th run through the while, the $clientrank = 5.

 

This way I'd know they are 5th best in sales.

 

[Stryves]

This would work, although I dunno if I'd recommend it for super large tables:

 

 

Haha, that was my concern as well... I was using the i++ increment to determine the position, but it's always better to make the DB do the work.

 

My other solution was creating a table for results, and cron job it to run/refresh every night... Just have static results per day.

 

I wasn't sure if there was an easier way to do this... Guess not

[Stryves]

"Also, you can add in a WHERE `clientname`='NAMEHERE' to show one result, with position"

 

Oooo nice, I didn't know if the where clientname=clientname was used, it would still show the position.

 

I'll give it a whirl, thanks a lot!

[Philip]

Take a look at my post above - with the query. For large tables, I mean like hundreds of thousands of rows. I doubt you have that many rows in there. I have a few DB's that have over 900k rows and can still run pretty complex queries quickly. Its just a matter of testing and tweaking it.

[radi8]

push the results into an array:

 

<?php

$arr=array();
Query is "SELECT clientname, sales FROM clients ORDER BY sales DESC"

$i=1;
while($query)
{
  $clientrank=$i;
  $arr[$i][0]=$res[0];
  $arr[$i][1]=$res[1];
  i++;
}
print_r($arr);

?>