Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have a web application that displays registrations for various events. When there are more than 100 registrations for an event I would like to break the display into pages of 100 registrations each rather than list all of the registrations on one page. In such cases I want to have a summary page that has links to each of registration pages along with the range of names covered by that page. For example, a summary page might say:

1-100 (Adams-Clark)
101-200 (Collins-Easton)
201-300 (Edwards-Granger)


and so on. I know I can do this relatively easily in Perl by (pseudo-code):
1. SELECTing the total number of registrations and dividing by 100 to 
+get number of iterations for loop
2.  for (index=1; index < num_iterations; index++){
      SELECT RegistrantLastName FROM registrations ORDER BY Registrant
+LastName LIMIT (100 * (i-1)) + 1,1 AS RangeStart;
      SELECT RegistrantLastName FROM registrations ORDER BY Registrant
+LastName LIMIT (100 * i),1 AS RangeEnd;
    }

[download]

If possible, though, I would like to stick this all in a single query so that the DBMS is doing as much of the work as possible (and simplifying the logic in the application code).
Thoughts?

Replies are listed 'Best First'.
Re: Can this be done entirely in MySQL?
by ChemBoy (Priest) on Apr 21, 2005 at 20:01 UTC

    If your rows are numbered, you can do this:

    select floor(regno/100) "groupby", min(regno),max(regno),min(name),max
+(name) 
from registrations 
group by `groupby`;

    [download]

    But I presume they're not, in which case you have to roll it on the fly (and I hope you're using 4.1 or better, or the subselect will come crashing down):

    set @rownum := 0;
select floor(@rownum  := @rownum + 0.01) "set_num", min(lname),max(lna
+me) 
from (select * from registrants order by lname) reg 
group by "set_num";

    [download]

    Then of course you do the paged query using LIMIT $set_num*100, 100, which is of course subject to quirks if somebody named Jones registers when you're about to look at the page that used to start with Kerrigan, but that's a separate issue. :-)

    Hope this helps!


    If God had meant us to fly, he would *never* have given us the railroads.
        --Michael Flanders

[reply]
[d/l]
[select]
      If you have an index you can do this without the subselect.

      You can use ignore index if you want the natural order, but the you can't currently create a descending index. 

      create index registrants_lname_asc on registrants (lname ASC);

SET @rownum := 0
SELECT CONCAT(min(@rownum := @rownum + 1), '-',
              max(@rownum), ' (', 
              min(lname), '-', max(lname), ')')
  from registrants use index (registrants_lname_asc)
 group by floor(@rownum/100)

      [download]
      -- gam3
      A picture is worth a thousand words, but takes 200K.
[reply]
[d/l]
Re: Can this be done entirely in MySQL?
by davidrw (Prior) on Apr 21, 2005 at 16:58 UTC
    Obviously requires a little reworking to Class::DBI (which could also greatly simplify the application code), but check out Class:DBI::Pager.

    My other suggestion would be to just bring back all the rows to perl, but only access them through some custom "paging" methods. (basically recreating what the Class::DBI::Pager module does for you)
[reply]
Re: Can this be done entirely in MySQL?
by dragonchild (Archbishop) on Apr 21, 2005 at 18:14 UTC
[reply]

Back to Seekers of Perl Wisdom