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

I'm trying to put links on the bottom of my pages on my image gallery to flip back and forth between pages. I'm showing 15 images per page which is sucked from MySQL. I'd like to say I'm going to be pulling them out with just the ID field but I think I'll have it sortable by other fields (date, name, etc) and I'm not sure how that'll affect anything.

How can I print the number of pages in links ("script.pl?page=1&orderby=id" page 1 | "script.pl?page=2&orderby=id", etc) and then have a reference point from where I left off previously?

My initial idea was to sort by the field, do a counter and ignore the first 15 per page (so if it were page 2, it'd ignore the first 30). I know there's probably a better way and I'm looking forward to seeing how you guys would solve this problem.

  • Comment on how to put links on the bottom of an image gallery

Replies are listed 'Best First'.
Re: how to put links on the bottom of an image gallery
by atemon (Chaplain) on Aug 27, 2007 at 01:59 UTC

    Hi,

    Why dont you try  LIMIT in MySQL?

    you can have your SQL as 

    SELECT * FROM your_table ORDER BY id LIMIT 0, 15

    [download]
    here limit statement will limit the number of records returned. In limit statement, 1st numeric value is the first record to be returned (offset) and 2nd numeric value is the number of records (row count). i.e. LIMIT 0, 15 will return 1st 15 records ( 0th to 14th)LIMIT 15, 15will return next 15 records ( 15th to 29th) and so on. For more details pllease refer to SELECT Syntax

    Your link can be like "script.pl?page=0&orderby=id" page 1 | "script.pl?page=15&orderby=id" to enable pagination.

    A step-by-step procedure to get pagination explained at Re: DB Search.

    Cheers !

    --VC

    Updates :
    Have a look at PerlMonks FAQ, The Perl Monks Guide to the Monastery for more information on how the Monastery works. How (Not) To Ask A Question will be a good guide. There are hundreds of thousands of posts here, Super Search is your friend. There are more than one postings available in the monastery regarding pagination


    There are three sides to any argument.....
    your side, my side and the right side.

[reply]
[d/l]
[select]
      I like the idea but page=15 for the second page is a little misleading. Either use a next=## instead of page=## or add some equations to produce accurate page descriptions 1,2,3,etc.


      "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

      sulfericacid
[reply]

        Hi,

        Giving page=15 is a "direct" method by which you can use the argument "page" directly with LIMIT. If you are passing pages as 0, 1, 2, 3 you can multiply it with 15. If its starting with 1, multiply page-1 with 15. Your perl script may look like 

        
my $q       = new CGI;
my $page    = $q->param("page") || '0'; #default 0
my $count   = $q->param("count") || 15; # more flexibility. you can pa
+ss the No of records as count :), defaults to 15

$offset = $page * count; # assumes that page no starts with 0.
#$offset = ($page-1) * count; # assumes that page no starts with 1. Co
+mment previous line and uncomment this if you wish to start page from
+ 1

$SQL = "SELECT * FROM your_table ORDER BY id LIMIT $offset, $count";

...
<rest of your code>
...

        [download]
        Hope this helps :)

        Cheers !

        --VC


        There are three sides to any argument.....
        your side, my side and the right side.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom