MySql Determine A Row Offset From a Query Result

Question

need to determine the numerical value of the offset of a row by using the value of a column entry in a sorted table.

Given, I know the unique username ($username) in the row of interest.
e.g. $username = "SnowWhite";

Given: it is guaranteed that the $username is in the database and will be in the results of my initial query that follows. Here is my initial query (that works):

$query  = "SELECT ALL  username,ticket_number, queue_time  FROM members WHERE queue_time !=0 ORDER BY queue_time";
$result = mysql_query($query);

What is working is a brute force php search of the $result array for the value of $username.

I would like to replace the php search logic with a query (or a recasting of the above query) that stores the integer value of the row offset within $result for the username column's value of $username into a variable, for example, $offset.

For example, if SnowWhite is in the third row of $result I expect the $offset == 2 (assuming a row index offset starting from 0).

Ultimately (this works) I will update the 'ticket_number' value of the selected row to be == $offset+1. by:

$query="update members set ticket_number='$offset+1' where username='$username'";
mysql_query($query); 

Current Working Logic

$query  = "SELECT ALL  username,ticket_number, queue_time  FROM members WHERE queue_time !=0 ORDER BY queue_time";
$result = mysql_query($query);
$i = 0;
while ($row = mysql_fetch_array($result)) {
  if ($row[username] == $userinfo)  //-- compare usernames
     {
   ++$i;
   $query="update members set ticket_number='$i+1' where username='$userinfo'";
   mysql_query($query);
   break;
   }
++$i;
}
mysql_free_result($result);

---

username ticket_number queue_time Doc 0 0 The 1st 3 not in sorted result Grumpy 0 0 Happy 0 0 Sleepy 1 111 The following are ni sorted result Bashful 2 222 Sneezy 0 333 SnowWhite ??? 444 This is the current user (assign ???=4) Dopey 0 555 EvilQueen 0 666

Answer 1

So with your table example:

username    ticket_number   queue_time
Doc             0               0                  The 1st 3 not in sorted result
Grumpy          0               0
Happy           0               0
Sleepy          1               111                The following are in sorted result
Bashful         2               222
Sneezy          0               333
SnowWhite       ???             444               This is the current user (assign ???=4)
Dopey           0               555
EvilQueen       0               666

how to assign the value 4 to the column ticket_number of the username SnowWhite, 4 being the rank of the row in a sorted selection of the initial rows:

first get the sorted result:

$result = mysql_query("SELECT * FROM table WHERE [here the condition for your sorted array]"); 
// don't forget to remove the []. they don't go there.
while ($row = mysql_fetch_assoc($result))
 {$array[] = $row;}

this should get you something like:

[0]   [username]        [Sleepy]
      [ticket_number]   [1]
      [queue_time]      [111]
[1]   [username]        [Bashful]
      [ticket_number]   [2]
      [queue_time]      [222]
[2]   [username]        [Sneezy]
      [ticket_number]   [0]
      [queue_time]      [333]
[3]   [username]        [SnowWhite]
      [ticket_number]   [NULL]
      [queue_time]      [444]

then foreach through your array

    foreach ($array as $number => $row)
     {if ($row[username] == $userinfo)
       {$result = mysql_query('UPDATE table SET ticket_numer=' . ($number+1) . ' WHERE username=' . $userinfo);}}

the row corresponding to username SnowWhite will get ($number + 1) (3 + 1) as ticket_number