DB Search

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

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. 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... On "`Next >>`" in HTML page, you may submit value for `$start` as `$start+$count`. On "`<< Prev` in HTML page, you may submit value for `$start` as `$start-$count`. Make sure that `$start-$count` won't go negative Make sure that `$start+$count` wont go above `COUNT()` of that SQL without limit. Cheers ! --VC Updates :* Refer MySQL Documentation for syntax of `LIMIT` in `SELECT` statement. 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]


The stupid question is the question not asked
	PerlMonks