×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

PHP & MySQL

Determine selected record offset for any ORDER BY sequence. by Binaryshi
Posted: 10 Apr 07

For explanation see the bottom comments labeled REASON and EXAMPLE.

CODE

<?php
/*
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
*/

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){
        //(1)
        list($cn, $dir) = explode(' ', trim($aob[$i]));
        //(2)
        $val =& $rec[$cn];
        $qt = (is_numeric($val)) ? '' : "'";
        $ltgt = ($dir=='ASC') ? '<' : '>';
        $and = ($i) ? ' AND ' : '';
        //(3)
        $qry = $qryb.$scope.$and.$cn.$ltgt.$qt.$val.$qt;
        $res = mysql_query($qry, $conn);
        $row = mysql_fetch_row($res);
        $offset += $row[0];
        //(4)
        $scope .= $and.$cn.'='.$qt.$val.$qt;

        $i++;
    }

    return $offset;
}
/*
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
*/

?>

Back to PHP FAQ Index
Back to PHP Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close