Mysql – How to select the next available row id of varchar type in MySQL

MySQLPHP

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

Best Answer

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?

EXAMPLE OF STABLE DELETE

Suppose you want to delete the first 10 employees in a company table (layoffs do hurt, this is only an example)

CREATE TABLE emp
(
    id INT NOT NULL AUTO_INCREMENT,
    empid INT NOT NULL,
    ...
    PRIMARY KEY (id),
    UNIQUE KEY (empid)
);

You can safely delete the 10 employees as follows:

CREATE TABLE empids_to_delete
SELECT empid FROM emp ORDER BY empid LIMIT 10;
ALTER TABLE empids_to_delete ADD PRIMARY KEY (empid);
DELETE B.* FROM empids_to_delete A INNER JOIN emp B USING (empid);
DROP TABLE empids_to_delete;

This would be great as long as you create the empid outside this table. This would not present a Replication Slave with a problem even if the auto_increment sequence on a Master is completely different from that of the Slave.

Best Answer

Related Solutions

Mysql database design with XML

Mysql – how to use thesql to delete only the first ten table records

EXAMPLE OF STABLE DELETE

Related Question