Determine selected record offset for any ORDER BY sequence.

<?php
[color #AAAAAA]/*
DESC:
Calculates the zero based offset of a record in a table, using
any given ORDER BY subquery

PARAM:
+ $rec        (IN, ARRA, PBRS=REQ)
associative array representing the selected record that at least contains all the columns in $ob

+ $tn        (IN, STR, PBRS=YES)
the table name

+ $ob        (IN, STR, PBRS=YES)
the (same) ORDER BY part that is being applied to the table GUI
format: 'colname1 ASC, colname2 DESC...'

+ $conn        (IN, RES, PBRS=YES)
Connection to the database where the table is

RETURNS:
+ INT: Zero based record offset

NOTES:
+ assert: lock tables READ if multiple users table
+ assert: colnames used in $ob exist in $rec as keys
+ assert: to have a reliable offsets returned,
always include in $ob the keys that eventually make a record unique
+ warning: on larger tables it is probably best to have indexes on the columns that participate in ORDER BY.
+ reason: see bottom
+ example: see bottom
*/[/color]
function mysqlRecordOffset(&$rec, $tn, $ob, $conn)
{
    $offset = 0;
    $qryb .= 'SELECT COUNT(*) FROM '.$tn.' WHERE ';
    $scope = "";
    $aob = explode(',', $ob);
    $len = count($aob);
    $i = 0;
    while($i < $len){
        [color #AAAAAA]//(1)[/color]
        list($cn, $dir) = explode(' ', trim($aob[$i]));
        [color #AAAAAA]//(2)[/color]
        $val =& $rec[$cn];
        $qt = (is_numeric($val)) ? '' : "'";
        $ltgt = ($dir=='ASC') ? '<' : '>';
        $and = ($i) ? ' AND ' : '';
        [color #AAAAAA]//(3)[/color]
        $qry = $qryb.$scope.$and.$cn.$ltgt.$qt.$val.$qt;
        $res = mysql_query($qry, $conn);
        $row = mysql_fetch_row($res);
        $offset += $row[0];
        [color #AAAAAA]//(4)[/color]
        $scope .= $and.$cn.'='.$qt.$val.$qt;

        $i++;
    }

    return $offset;
}
[color #AAAAAA]/*
COMMENTS:
(1) get colname and sort direction 'ASC' or 'DESC'
(2) get value, need quotes?, compare lt or gt?, expand the query with 'AND'?
(3) query record count (ltgt (within current scope)) and increase offset with it
(4) push last ltgt query to query scope

---
REASON:
problem...
With a db table GUI, that shows records spread over pages in a LIMIT set,
a particular ORDER BY subquery may be set.
With a record highlighted in the GUI, upon ORDER BY change -the user clicks on
a column to sort on that column - the LIMIT subquery stays the same and that
highlighted record often disappears to another page.
But which one? It sould be handy for end users to see that record inside the
context of the new sort order immediately.

solution...
This function calculates the zero based offset of a record in a table, using
any given ORDER BY subquery. With offset of the selected record known,
the LIMIT subquery can be adjusted to show the right page with the selected
record in view, after the following calculations...
//show 40 records at a time
limit_length = 40;
//the page number for the table browsing GUI
limit_page = ((int)(record_offset / limit_length)); 
limit_offset = limit_page * limit_length;
//SQL subquery: LIMIT limit_offset,limit_length

---
EXAMPLE:
machine=pIII 500Mhz 256MB lo-end, FreeBSD
tblsize=332
functionspeed=0.016-0.017 seconds...
with the following input...

$rec = array(
    'id'=>162,
    'name'=>'Senna - Have You Ever.mp3',
    'length'=>'3:48',
    'yyyy'=>2005
);
$tn = 'mp3';
$ob = 'length ASC, yyyy DESC, name ASC, id ASC';

...this function generates the following queries...
$qry = SELECT COUNT(*) FROM mp3 WHERE length<'3:48'
$qry = SELECT COUNT(*) FROM mp3 WHERE length='3:48' AND yyyy>2005
$qry = SELECT COUNT(*) FROM mp3 WHERE length='3:48' AND yyyy=2005 AND name<'Senna - Have You Ever.mp3'
$qry = SELECT COUNT(*) FROM mp3 WHERE length='3:48' AND yyyy=2005 AND name='Senna - Have You Ever.mp3' AND id<162
*/[/color]
?>