how to get a previous/next record

_SuperString

Hi folks,

Anyone know how to do this ?

I am building an ordering system for records in a MySQL table (I have a field called orderby), and I would like to move a record either up on or down one by ID field. (so if I move a record up one, I need to move the previous record down also).
I need a command that can get the previous or next record ID in the database, Im sure ive seen it in the past but cant find it.

Cheers in advance

gOloVasTicK

If you use for ID type autoincrement and rows dont moving into table, may be:

for prev:
SELECT ....... id < your_now_row_id ORDER BY id LIMIT 1

for next:
SELECT ....... id > your_now_row_id ORDER BY id DESC LIMIT 1

_SuperString

I think you misunderstood the question.
What I am trying to do is UPDATE a table, I need to update a field named ' orderby ' based on the supplied field ' id ' (identifier). I need to either set the ' orderby ' field to orderby+1 (if moving record up) or orderby-1 (if moving record down. The problem is trying to get the ' id' field of the record that is not supplied.

example of fields:

id orderby

5 1
6 2
7 3

Say I need to move the record with the id of 6 up - I would write the SQL like this 'UPDATE table SET orderby = orderby-1' fine. But I also need to move the previuos record down (as I dont want dupplicate orderby fields) How do I get the 'id' of the previous record.

HYU

Rodders

You need to do two separate updates.

If you already know the ORDERBY value of the one you are moving up then you can use that, otherwise you need to do a SELECT to get that. Miss out step 2 if you've already got ORDERBY.

1) orderby + 1

UPDATE table SET orderby = orderby+1 WHERE ID = '$yourid'

2) get orderby (returns 2 rows after 1st UPDATE)

SELECT orderby FROM table WHERE ID = '$yourid'

3) orderby - 1

UPDATE table SET orderby = orderby - 1 WHERE orderby = '$yourorderby' AND ID != '$yourid'

gOloVasTicK

May be i dont understand you =) My English really bad.

This is right answer?

get ID & prev ID (ID1, ID2)
save orderby from rows (ID1, ID2) in temp vars
put in rows ID1 orderby2 & ID2 orderby1 from temp vars

_SuperString

OK, well here is what I needed to do to get the previous ID:

// select all the records
// $oid is the value of the known record
$get_sql = "SELECT id FROM employees";
	$qry = mysql_query($get_sql);

$g = 0;
while ($a = mysql_fetch_array($qry)) {
	if($a[0] == $oid) {
		$g=1;
	}
	if($g==0){
		$previous_id = $a[0];
	}
}

now I have both id's.

thanks anyway folks.

Weedpacket

You can also SELECT for the maximum id which is less than the current $id, (and the minimum id which is greater). Obviously, the result will be a null if you're at the end.
Because of the "obviously", you probably won't want to combine them into a single query, viz:

select
	min(e1.id), max(e2.id)
from
	employees e1,
	employees e2
where
	e2.id<$oid
	and e1.id>$oid;

Because if $oid really is the maximum or minimum in the table, you won't get any results.

_SuperString

Thats exactly what I was looking for weedpacket. I never thought about combining 'max()' with 'where <', im not getting much sleep these days 🙂 Cheers