Search code examples
mysqlperliterationrecordset

Recordset Iterating

I want to iterate through records returned from a MySQL database using Perl, but only ten records at a time. The reason is that the server component can only handle 10 items per request.

For example:

If the query returned 35 records then I have to send the data in 4 requests:

Request #         # of Records
--------             --------
   1                    10
   2                    10
   3                    10
   4                     5

What is the best way to accomplish the task?

Solution

  • You can adjust the query to select 10 rows:

    select * 
from yourtable
order by idcolumn
limit 10;

    When iterating over the rows, store the ID of the row you process. After you've processed 10 rows, fetch the next 10:

    select * 
from yourtable
where idcolumn > stored_id
order by idcolumn
limit 10;

    Continue the last query until it returns less than 10 rows.