MySQL remember last position

Question

I have made a small app where a table of data is presented to the user. The data can be sorted by different column headers and filter using inputs.

When the user clicks on a row it opens a small popup which contains two arrows for going to the next and previous record, in the same order as they appear in the table.

Originally I had (for "previous" as an e.g.):

SELECT ed.id
FROM entity_details AS ed, users
WHERE ed.id > ?
AND ed.typeRef = ?
AND ed.ownerRef = users.id
$filter
$SQLOrder LIMIT 1

Which works fine if the table is sorted by ed.id, but will not work properly if sorted by another column e.g. ed.name, because the next alphabetical name might have a much higher or lower id.

FYI $filter could be something like:

AND branchRef = 2

and $SQLOrder could be:

ORDER BY ed.name DESC

What do I need to do to make it cycle through the records properly, respecting current order and record position?

All the sorting and filtering parameters come through over AJAX, e.g:

$JSON->selectedbranch ;

I've come to the conclusion that all I need to know is how to start the query from a row with column X containing value Y, is that possible?

Why is that your $SQLOrder is functioned as filtering and not sorting? — sephoy08, Oct 17 '12 at 07:27
I'm not sure but, can you tell me where is your code for sorting? coz, as stated above, your $SQLOrder is just another filtering for `ed.name` — sephoy08, Oct 17 '12 at 07:29

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

You should store the number of the row you displayed, not the ID. Then just do the ordering in SQL as your application requirements imply, then apply the knowledge contained here:

Skipping first n results in MySQL

To simplify the job, and to make this answer usable for future SO dwellers:

SELECT ed.id 
FROM entity_details AS ed, users 
WHERE ed.typeRef = ? 
AND ed.ownerRef = users.id 
$filter 
$SQLOrder 
LIMIT $currentRowNum,1

This scheme smells however: using this to navigate your rows implies a SQL query for each navigation action. That might have an bad effect on your response time...

edited May 23 '17 at 12:04

Community

1
1

answered Oct 17 '12 at 07:24

ppeterka

20,583
6
63
78

I believe the limit clause would be: `LIMIT $currentRowNum, 1`. It is defined as `LIMIT [offset,] row_count` – Dennis Haarbrink Oct 17 '12 at 07:31
How do I get the row number as opposed to anything else like ID etc? – imperium2335 Oct 17 '12 at 07:32
@imperium2335 You should 'keep it in mind' on your side... I am not familiar with your application, but I think I would employ a JavaScript variable to store it, and supply it as a parameter to the PHP script executing the query. Given that Ajax is used, there is a chance that it should work without hassle. If you'd want to preserve its state between 'real' navigation actions, you'd have to take care of that though. – ppeterka Oct 17 '12 at 07:35

score 0 · Answer 2 · answered Oct 17 '12 at 07:46

0

PHP's mysql_data_seek function may helps.

mysql_data_seek

answered Oct 17 '12 at 07:46

Mahdi

9,247
9
53
74

score 0 · Answer 3 · answered Oct 17 '12 at 09:10

I found it, seeing that other guys answer gave me an idea, but his answer has disappeared :(

For the next button I have:

$result = $dbh->prepare("SELECT ed.id
FROM entity_details AS ed, users
WHERE $WHERE < ?
AND ed.typeRef = ?
AND ed.ownerRef = users.id
$filter
ORDER BY ed.name DESC LIMIT 1") ;

$WHERE is just the column name "ed.name".

I just have to sort out the dynamics for $where and the ORDER BY clause and it'll be good to go.

Thanks for every ones input!

MySQL remember last position

3 Answers3