PHP - SQL: fetching results in round robin fashion

Question

I have a table, which consists of 3 fields:

• id
• name
• status

Every time I get the results, it should give me 5 names whose status = 1. Suppose the db contains following:

id name status
1   A    1
2   B    1
3   C    0
4   D    1
5   E    0
6   F    0
7   H    1
8   I    1
9   J    1
10  K    1
11  L    1
12  M    0

1st time, fetch should return: A,B,D,H,I (5 records)
2nd time, fetch should return: J,K,L,A,B (5 records)

UPDATE: I don't want typical pagenation. Consider I have 12 available names from A1 to A12. The first fetch should return A1-A5, second fetch A6-A10 and third fetch A11, A12, A1, A2, A3. So when I reach the end, I need to get records starting from the first to fill the 5 slots.

i am doing it in php with mysql

Answer 1

Keep track of the ids of the records returned, and for the following queries do:

select top 5 *
from (
    select top 5 * 
    from MyTable
    where status = 1 
        and id not in (1,2,4,7,8)
    order by name
    union 
    select top 5 * 
    from MyTable
    where status = 1 
    order by name
) a

Answer 2

$q = mysql_query("SELECT name FROM table WHERE status = 1 LIMIT 5);
while ($row = mysql_fetch_row($q))
{
  .... //first 5
}
$q = mysql_query("SELECT name FROM table WHERE status = 1 LIMIT 5,5);
while ($row = mysql_fetch_row($q))
{
  .... //second 5
}

this uses the offset functionality of mysql- think of it as pagination for your results.

Answer 3

This looks like some sort of job allocation script?

You need 2 things:

• the highest ID returned last time the script was run (lastID)
• a number larger than the maximum ID in the table (bigNum)

Then you can write your query as

SELECT
  id, name
FROM
  table
WHERE
  status=1
ORDER BY
  (bignum + id) MOD (bigNum + lastID + 1)
LIMIT 5

Shazaam!