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

Hi all, I have a MySQL DB with ~1100 entries, 13 Fields per entry.

I have a search feature on the page, but when someone searches, they get all of the matches at one shot. What I would like to do is display, say 5 matches, at a time. Then have a next and previous at the bottom of the page along with page numbers.

I am completely lost on where to even start on this, so if anyone could help out, it would be greatly appreciated.

Thanks!
Jim

Replies are listed 'Best First'.
Re: DB Search
by atemon (Chaplain) on Aug 08, 2007 at 10:45 UTC

    Hi,

    To get pagination, you need to do following.

    1. Format your SQL as something like $SQL = "SELECT * FROM xyz ..... LIMIT $start, $count". Where $start is the count to start with and $count is No of records to be displayed. 0,10 will display 1st 10 records, 10,10 gives items 10 to 19 etc...
    2. On "Next >>" in HTML page, you may submit value for $start as $start+$count.
    3. On "<< Prev in HTML page, you may submit value for $start as $start-$count.
    4. Make sure that $start-$count won't go negative
    5. Make sure that $start+$count wont go above COUNT(*) of that SQL without limit.

    Cheers !

    --VC

    Updates :

    1. Refer MySQL Documentation for syntax of  LIMIT in SELECT statement.
    2. 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]
Re: DB Search
by wfsp (Abbot) on Aug 08, 2007 at 10:50 UTC
    Have a look at HTML::Pager, it can do all the work for you.

    update: fixed typo

[reply]
Result pages in DB Search
by moritz (Cardinal) on Aug 08, 2007 at 10:44 UTC
    You could provide links to multiple result pages.

    In your SQL you can use something like (rest of query) LIMIT 5 OFFSET $offset, and $offset can be calculated from the number of the result page.

    Probably you need $offest = 5 * ($page_number - 1).

    Perl 6 in German

[reply]
[d/l]
[select]
Re: DB Search
by oha (Friar) on Aug 08, 2007 at 12:53 UTC
    (with the risk of getting boring) i remember that issuing the same query on a different offset could lead to strange behaviour if data get changed between every request.

    there isn't a perfect way for fix this, but you could think about fetching all the data and put it on a session, then show pages of this "snapshot".

    Oha

[reply]
Re: DB Search
by Anonymous Monk on Aug 10, 2007 at 17:45 UTC
    I would say look at your SQL SELECT() statement. You can use the LIMIT clause to bring in a range of rows. LIMIT 5 will bring the first 5 rows. LIMIT 0,5 will do the same as the first number is the row offset with the first row being 0. You could save the offset into your search form and increment the offset by the range each time the page is called.

    Here is the MYSQL4 SELECT documentation.

    Enjoy

    Keith
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom