0

I have a table with primary key 'Id' of varchar type. I want to select the next row when user clicks next record, in php, for example 1st row id= 'a' 2nd row id= 'b' 3rd row id='h' 4th row id='z'

Im currently on page with get variable id='a' I want to get the next which is 'b', then 'h' then 'z'

The first record is easy as I send page.php?id='a' to display table records.

The next record, Select id from table where id > 'a' But I dont want 'h' or 'z' because 'b' is next. I dont have a clue, any help will be appreciated

1
SELECT id AS next_id FROM your_table WHERE id > @current_id ORDER BY id LIMIT 1

Breaking that down:

SELECT id AS next_id FROM your_table

that bit is hopefully obvious

WHERE id > @current_id 

you don't want the current value or anything before it

ORDER BY id 

in the output, list earlier ID values first

LIMIT 1

we only want one of them so no point going further once one has been ready from the table.

As an aside: is there a any particular reason why you are using a character field as the ID instead of a numeric one which would be more efficient?

  • SELECT next_id = id can return NULL, if next_id and/or id value IS NULL, 1 if they are equal or 0 otherwise. No another variants. So the result is wrong. And ever while correcting the technique is inapplicable if id values order do not matches the alphabet. – Akina Oct 18 '18 at 16:38
  • I've used slightly non-standard aliasing syntax there, I'll fix that to avoid confusion. I'm assuming that the next record to read is what is being looked for in which case it returning NULL when the current item is the last one would be correct. – David Spillett Oct 18 '18 at 17:06
  • The question is tagged as MySQL-relative, where slightly non-standard aliasing syntax not exists. And again - what if the order will be altered to a->b->z->h? – Akina Oct 18 '18 at 18:18
  • 1
    @Akina - that is why I edited the text to use the more standard syntax. I'm assuming the order is intended to be alphabetical, nothing in the question says to me that it might be different. Unless the question raiser comes back with more detail, I'm not going to re-/over-think it. – David Spillett Oct 19 '18 at 10:37
0
mysql> SET @order := 'abhz';
Query OK, 0 rows affected (0.04 sec)

mysql> SET @current_id := 'a';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @current_id := SUBSTRING(@order FROM 1 + LOCATE(@current_id, @order) MOD LENGTH(@order) FOR 1) next_id;
+---------+
| next_id |
+---------+
| b       |
+---------+
1 row in set (0.00 sec)

mysql> SELECT @current_id := SUBSTRING(@order FROM 1 + LOCATE(@current_id, @order) MOD LENGTH(@order) FOR 1) next_id;
+---------+
| next_id |
+---------+
| h       |
+---------+
1 row in set (0.00 sec)

mysql> SELECT @current_id := SUBSTRING(@order FROM 1 + LOCATE(@current_id, @order) MOD LENGTH(@order) FOR 1) next_id;
+---------+
| next_id |
+---------+
| z       |
+---------+
1 row in set (0.00 sec)

mysql> SELECT @current_id := SUBSTRING(@order FROM 1 + LOCATE(@current_id, @order) MOD LENGTH(@order) FOR 1) next_id;
+---------+
| next_id |
+---------+
| a       |
+---------+
1 row in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.