How can I retrieve records X to Y using DBI?

BBQ has asked for the wisdom of the Perl Monks concerning the following question: ⭐ (database programming)

How does one go about selecting X number of rows on a query (e.g. with MySQL)? The desired effect would be to return results from 0-20, then 20-40, and so on.

Originally posted as a Categorized Question.

Comment on How can I retrieve records X to Y using DBI?

Replies are listed 'Best First'.
Re: How to display from X to Y records using DBI?⭐ by btrott (Parson) on May 02, 2000 at 00:47 UTC
I'll expand on what chromatic wrote, because the LIMIT keyword can do even more than just that. :) In addition to specifying the max number of records to return, you can specify an offset of where to start in the records being returned. The syntax is `[LIMIT [offset,] rows]` [download] So, for example, to view records 10-35, do this: `select log_id from log_id limit 10,25` [download] Pretty neat! Consult the MySQL SELECT docs for more details and tips.	[reply] [d/l] [select]
Re: How to display from X to Y records using DBI?⭐ by chromatic (Archbishop) on May 01, 2000 at 18:58 UTC
You could use the SQL command LIMIT: `SELECT * from presidents WHERE terms > 2 LIMIT 10;` You can also use the DBI method fetchall_arrayref(), which will return a reference to a list of rows. Use an array slice to get at the ones you want: `# prepare and execute statement my $array_ref = $sth->fetchall_arrayref(); # we want 0 through 20 foreach my $row (@$array_ref[0 .. 20]) { # do something }` [download]	[reply] [d/l] [select]