How to select records one by one without repeating

Question

select id, name from customer order by random() limit 5;

The above query select random records. However it repeats rows. I just want to select a single row each time without repeating. Say i have id 1 to 5. For first time i want to select 1, second time the query displays 2 then 3,4 & 5. Once the sequence is completed it starts again with 1.

Answer 1

$query= "select id, name from customer order by id limit ".rand(1,5).",1"

Read more about using limit here: http://dev.mysql.com/doc/refman/5.0/en/select.html

Answer 2

Since SELECT is an atomic operation, I don't think, that there is an easy solution. I also see two different problems that need to be solved:

selecting a single row without repeating a shown one. Basically you need to store the last line showed somewhere. If you have some application, that stores the current state, it might be an option, to save it as an external variable. The sketched solution is done in MySQL 5.0+:

First prepare a statement that will be executed later on and set a initial position for the query:

PREPARE STMT FROM 'SELECT id, name FROM customer LIMIT ?, 1';
SET @pos = 0;

You might want to add a defautl ordering to get a reliable result. Each time you want to get a single row, execute the statement by

EXECUTE STMT USING @pos; 
SELECT count(*) FROM customer INTO @max; 
SET @pos=(@pos+1)%@max;

The other thing is, that you want to read a random position. The only solution that I see right now, is to create a temporary table that either holds the content from customer ordered by random or that you create a temporary table holding all numbers from 0..@max and read the current position in the main table from the temporary table. If you have an application outside MySQL there might be more elegant ways to solve this.