Get selected record position in limit-table browser?

[RbgoWeb]

In a table browse control with [first] [prev] [next] [last] buttons and a LIMIT setting, the records in a db table are devided and browsed in virtual pages.

In the GUI for these pages a record can be selected, and columns can be sorted.

When I sort with a record selected, changes are big that the record ends op on a page that is currently not displayed. And I like to switch to that page automatically so the selected record remains in view.

My question:
How can I find out what the selected record position or number would be, after sorting the table's total data?
Then I can calculate on what page the record is after sorting en adust this in the table browse control.

 

[jpadie]

typically the id of each record is stored in the table row as a uniqueid in a hidden field or link or whatever.

if you want us to help more, please provide the code you are using to generate the table.

 

[RbgoWeb]

See a table with 1000 entries in front of you. The table contains columns 'id', 'first_name' and 'last_name'.

Record with id=435, is selected/highlighted in the gui, and has...
first_name='Albert'
last_name='Zucchini'

The html gui of the table has column headers labeled 'first_name' and 'last_name', which are links to set the table sort order to that column.

This makes record with id=435 after sorting on first_name, record number 6 in the result set, and after sorting on last_name record number 978 in the result set that mysql returns.

Now the gui of the table has a limit browser implemented with [first] [prev] [next] [last] buttons, while displaying 25 records at a time. So the table records are spread over 40 virtual pages for the user to browse.

When being at page 1, sorted on first_name, selected record with id=435 is visible, but after sorting on last_name, it suddenly is at page 40, and so, not visible.

What I need is an efficient way to determine the record number in a full result set, for any selected record after sorting, so I can calculate the page number to automatically switch to that page, so that selected record remains visible.

I would like to make a function with signature:
int recid_to_recnum(
string tablename,
int record_id,
string order_by,
string mysqlconn
)

//record_number == 978
record_number = recid_to_recnum(435, 'last_name');

//remember...
//records_per_page == 25

page_number = (int)(record_number / records_per_page);
//page_number == 978 / 25 == 39

page_offset = record_number % records_per_page;
//page_offset == 978 % 25 == 3

So record with id=435 is on page 39 on the 3rd row after sorting on 'last_name'.

My question is: How to best implement the function 'recid_to_recnum' ?

 

[jpadie]

can i try to rephrase so that i understand your question?

let's say that a certain record is on page 10 of a page showing 25 records a time, ordered by last name. so essentially the record is 250 (ish) in the table order.

that record has an ID of 127 (say).

whilst on this page the user presses "sort by first name".

you then want the whole recordset ordered by first name and the relevant page that contains record 127 being that which is displayed to the user?

 

[RbgoWeb]

yes, that's what I mean.

 

[jpadie]

there is no truly neat way of doing this. for a pure php solution try this logic:

1. retrieve just the id with the right order by.
2. iterate the recordset to determine the position of the ID
3. calculate the 'page number'
4. redo the query retrieving all relevant data using the page number as the appropriate offset.

clearly, this is not hugely efficient as you have to iterate the recordset. the latency depends on where the record is within the recordset.

it occurs to me that there may be a better solution based on mysql views.

1. create a 'view' for each order by type.
2. the view should be a select * with an additional auto_increment column.

these views can then be reused as you wish

for the logic, first do a sql query against the view just to retrieve the id and position id. then follow items 3 and 4 above.

for help on the myql part see the mysql forum (and indeed to check whether this construct is even possible)

if it is not possible, then you could do something like the above using temporary tables.

 

[Tarwn]

A variant of jpadie's first option would also be viable, if MySQL won't let you put an auto-incrementing field in a view. Instead of selecting all of the records back and searching through for your record you could use a COUNT statement to determine the number of records. Basically something along the lines of:

SELECT COUNT(T2.id) 
FROM YourTable T1 INNER JOIN YourTable T2 ON T1.LastName > T2.LastName
WHERE T1.id = [selectedId]

Basically a simple count of all records whose last name is previous to the record in question. This same basic structure could be used for many different types of ordering by changing the field names or equivalency checks.
This count statement would remove jpadie's 2nd step in the first solution above, leaving you with a SQL query and a little bit of math for steps 1 and 3.

Again, this is really only a suggestion if the incrementing column in views idea doesn't work out. I don't use MySQL often enough to know whether that is viable or not (which would also be a reason to check the syntax of my above sample statement).

-T

 

[jpadie]

that's nice.

could you also use a sub-select?

select count(*) from tbl_name where last_name < (select last_name from tbl_name where id='".mysql_escape_string(trim($_POST['id']))."'")

as tarwn says, this gives you the position to calculate the page number.

 

[RbgoWeb]

Thanks a lot Tarwn and Jpadie. The idea about finding out the row count before/smaller that current value is nice and very usefull, thanks. I was not formiliar with an inner join query. It worked... but I could not modify it to get it to work when sorted of over multiple columns.

Finally got an algorithm together last night. It is tested, but it is still young, so if you find bugs or make enhancements please let anyone know through this page. I spend a day searching on the net for this, found same questions but no answers.

[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, 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 128MB 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]