1

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!

  • 1
    Next and Previous imply your data is ordered. If you're relying on the default order with something like `SELECT * FROM fruits` then technically-speaking you have no order (barring a default order set on the table) thus no previous or next. – Mike B May 25 '12 at 20:17
  • 2
    next of what ? if you have no order then "next" does not have any meaning. besides, How do you sort data? – John May 25 '12 at 20:19
  • @john I think he means the "default order" last insert is the last row – jcho360 May 25 '12 at 20:25
  • @jcho360 "Default Order", or Orderless, in mysql is unreliable and shouldn't have functionality based on it. There's a lot of questions on it: http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order http://stackoverflow.com/questions/1949641/mysql-row-order-for-select-from-table-name – Mike B May 25 '12 at 20:36
  • @MikeB I'm gonna make some test and research a little bit more, but I'm pretty sure if you don't identify any index the order is the last insert the last showed ( I think this could vary on engines and Dbs), this is interesting. – jcho360 May 26 '12 at 16:17
  • @jcho360 Read, specifically how he shows that newly inserted rows take up space from deleted rows first. http://stackoverflow.com/a/1949663/46675 – Mike B May 26 '12 at 16:52
  • @MikeB thanks a lot for the info you are right 100%, take a look to this link, http://forums.mysql.com/read.php?21,239471,239688#msg-239688 – jcho360 May 26 '12 at 19:54

4 Answers4

1

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.

Amy McCrobie
  • 1,019
  • 7
  • 17
  • 1
    Good answer...I got all caught up in the autoincrement, and went down the path of last insert id. – GDP May 25 '12 at 20:18
0

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....

GDP
  • 8,109
  • 6
  • 45
  • 82
  • Yep it's unique. But what if I'm not able to add another column? –  May 25 '12 at 20:13
  • Pretty sure that last id has to be an auto increment. You may have to traverse the data sequentially, do some research on MySQL cursors – GDP May 25 '12 at 20:15
  • 2
    Your table doesn't have an implicit order to it. It is just a bunch of records, so there is no concept of next or previous record, because there is no order. So you first need a column to sort on, that will give you an order. And then from there you can select * from table where id > current ORDER BY id TOP 1 or select * from table where id < current ORDER DESCENDING BY id TOP 1 (something like that, my SQL is rusty) – Steve May 25 '12 at 20:16
0

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.

Jeshurun
  • 22,940
  • 6
  • 79
  • 92
0

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

Community
  • 1
  • 1
jcho360
  • 3,724
  • 1
  • 15
  • 24