调用数据库的子集，如何在db结束时循环记录1

Question

Sorry, the title was hard to phrase.

Using PDO, I have a database with 100 records. A random number of records are chosen at a time and the ID of the last used is saved. Each time it grabs that random number of records it picks up where it left off last time starting with the saved ID. Easy so far.

My problem is if the last ID use was 94 (of 100) and the number of records is 10. I get the next 6 OK. But then need to restart at ID 1 and keep looping.

I could do it at the end of my loop by determining I am at last record ID and doing a new query starting at ID 1. But it seems a bit clunky, any thoughts on a better method?

To head off a duplicate post close, How to get a random range of 10 records from a database without overlapping the end boundary? is about getting a random subset that happens to start near the end.

Answer 1

Assuming $lastID is the last used id and $myLimit is the number of records that you want to get.

$qry = "SELECT id, field_01, field_02
    FROM (
        SELECT id, field_01, field_02
        FROM my_table
        WHERE id >= {$lastID}
        LIMIT {$myLimit}
        UNION 
        SELECT id, field_01, field_02
        FROM my_table
        WHERE id < {$lastID}
        LIMIT {$myLimit}
    ) tab
    LIMIT {$myLimit}";

The ideas of this query are to do the following :

1. Get data with id more or equal to $lastID, limited to $myLimit rows.
2. Get data with id less than $lastID, limited to $myLimit rows.
3. Union both queries above.
4. Get the first $myLimit rows.

Hopefully this help.