2

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
local idiot
  • 49
  • 8

1 Answers1

1

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 ...
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • the table is huge around 14,000 records. and what I am working on now, i would just like the next record after and the one previous. I will give you explanation some time and see if i can get it to work. – local idiot Dec 14 '10 at 19:46
  • When you say create a view to store the row_number what do you mean? – local idiot Dec 14 '10 at 19:47
  • Wiew has some restrictions such as does not allow variable in select query. Proper replacement will be create a summary table that contains row number + the non-integer ID – ajreal Dec 14 '10 at 19:55
  • When you say proper replacement. Do you mean create a view with a row number and non-integer id? – local idiot Dec 14 '10 at 19:59
  • Yes, **BUT** creating view does not allow variables, so use a summary table instead. – ajreal Dec 14 '10 at 20:01
  • Is a summary table just a temp table with data? – local idiot Dec 14 '10 at 20:20
  • Yes, but whether is a temporary table or not, if up to you to define – ajreal Dec 14 '10 at 20:23
  • If i need to create a new question I will. Lets say I want to create this table. How can i take my existing table, sort it like i need it and then take a value from it and create an integer row key – local idiot Dec 14 '10 at 20:37
  • I posted some example, you can take a look, there are couple of ways to achieve this, so, this is just one of the possible ways – ajreal Dec 14 '10 at 20:42
  • thanks for your time. was this it? http://stackoverflow.com/questions/4358730/mysql-conditional-insert/4359156#4359156 – local idiot Dec 14 '10 at 20:49
  • What a great day to learn a new trick. I created a key index table to do what i need and i can run this after an insert transaction of this table and re-create the index as needed. Insert INTO test3 (blockplotid) Select blockplotid from general ORDER BY length(general.blockid), general.blockid, length(general.plotid), general.plotid – local idiot Dec 15 '10 at 13:49