CODEWITHSUNDEEP
phpmysql
user1309158
user1309158

Reputation:

How do I get the previous and next record when there is no auto incrementing ID column?

Imagine I have a table named fruits laid out like this below.

  |   id    |    fruit     |   colour    |
  |--------------------------------------|
1 |  ptiru  |    Grape     |  Green/Red  |
2 |  wlk3b  |    Banana    |   Yellow    |
3 |  i9nuc  |  Strawberry  |     Red     |

Lets focus on the ID column. You can see that it is not a auto incremented integer, going up +1 each time. But instead it's a random combination of 5 numbers/letters.

If I have the 2nd record selected (banana) how would I then select the previous record (grape) and the next record (strawberry)?

Can't wait for an answer ;) Thanks!

Upvotes: 1

Views: 544

Answers (4)

jcho360
jcho360

Reputation: 3759

After some reading, you won't be able to do that, because the order won't necessary be the same.

When you create a table with an Index, or PK, your rows will have an order and you didn't specify if you have one and neither in which row, so probably you don't have one.

When you create a table without index and start inserts, the last insert will appear as last, but if you make some modification the rows will change position, please take a look to this link

So If you want to Know what is before of "banana" add an index and it will order by, or if you want to order by Random and get the value before the best way would be with a store procedure or function with, cursors and variables.

regards

Upvotes: 0

Jeshurun
Jeshurun

Reputation: 23186

SELECT @rownum := @rownum AS position, f.* from fruits f, (select @rownum:=0 r ) r

This query will give you a resultset like:

+----------+-------+------------+
| position | id    | fruit      |
+----------+-------+------------+
|        1 | ptiru | Grape      |
|        2 | wlk3b | Banana     |
|        3 | i9nuc | Strawberry |
+----------+-------+------------+

You could then load these up in an array in your code, and use the index to find the next / previous fruit as you may require.

Upvotes: 0

Amy McCrobie
Amy McCrobie

Reputation: 1029

Set a variable to 0 ($i = 0) before your loop that will be incremented inside your loop ($i++). After you increment the value, use mysql_data_seek($result,$i) to get the next record, or mysql_data_seek($result,($i - 2)) for the previous record.

Upvotes: 1

GDP
GDP

Reputation: 8178

Going out on a limb here, as I've never seen anything like this, but I'd say that "id" column isn't a true id...its a unique key, perhaps, but not really an ID. I'd add a column that IS an autoincrement and go from there....

Upvotes: 0

Related Questions