CODEWITHSUNDEEP
mysqlsql
local idiot
local idiot

Reputation: 49

MYSQL Next Previous Record

I need to be able to find the next and previous record going from a non auto incremental id.

The correct sort order from the database uses this order by statement:

The below works only to list all results from a search. ORDER BY length(general.blockid), general.blockid, length(general.plotid), general.plotid

The page that needs to know the next and previous record, holds the unique id/varchar in the URL as simply ?id=100_1A

In the url variable example above,

100_1A is in the db as blockplot

100 is the in the db as blockid

1A is in the datbase as plotid

When I am on the record 100_1A the next record should be 100_1B. The previous record to 100_1a should be 99_6C

My problem is that how do i query the database for the next record when I dont know the order until it is sorted?

The sort order I typed is a working sort for the search results page.

When a user clicks to go into a specific record, the id is passed to the ur on the detail-view.php?id=100_1a

From the view page i need to take 100_1A and use the same sort to see what record is after and before 100_1A

Upvotes: 2

Views: 936

Answers (1)

ajreal
ajreal

Reputation: 47321

You can try this

set @row:=0;
select * from 
(
  select *, (@row:=@row+1) as row_number from your_tables
  order by 
    length(general.blockid), general.blockid, 
    length(general.plotid), general.plotid
) as rowset 
WHERE id='100_A';

the above will give you the position of 100_A with the refer to the column row_number, and you can use that column to determine the next/prev row like,

set @row:=0;
select * from 
(
  select *, (@row:=@row+1) as row_number from your_tables
  order by 
    length(general.blockid), general.blockid, 
    length(general.plotid), general.plotid
) as rowset 
where 
  row_number in(first_query_row+1, first_query_row-1);

Please note that the query cost might very expensive if table is big,
and you can consider to create a view summary table to store the row_number and uniqid

Example of creating summary table

create table ordering_table
(
  row_number int(10) unsigned not null default 0,
  blockid varchar(255),
); /* no index, define yourself later */

 set @row:=0
 insert into ordering_table
   select @row:=@row+1, blockid from your_tables
   order by ...

Upvotes: 1

Related Questions