I've seen an example of MySQL's limit, and I gave an example of MS's TOP
earlier. There is a more universal way, but this is very inefficient
compared to a LIMIT or TOP or some other method if you have them available.
However, just to give you another possibility:
Almost every DBMS has some sort of IDENTITY (auto-increment) column or
datatype. If you create tables with these columns for sorting, you can use a
count on these columns to limit the return. One example of this is a query
like this:
select * from orders o where
(select count(*) from orders i
where i.Orderid < o.orderid) between 101 and 110
That will return rows 101-110. If you were just doing a straight query with
no more where clause than that, and it was not possible to delete the rows,
you could just call on the identity columns directly. However, if you wanted
a better where clause, or if rows could be deleted along the way, you would
have to use the count with that method to get correct number or rows. You
might note that if you add a where condition, it needs to be added to both
the outer query and the sub-query to get the right results.
As I said, that is much less efficient than the built in ways most DBMS's
have, but will work if you do not have such a method available, and is
usually better than trying to pull an entire result set across a network,
then limit it after it has already arrived to your script.
Have fun,
Steve H.
-----Original Message-----
From: Michael Fowler [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 07, 2001 11:08 PM
To: Daniel Falkenberg
Cc: Beginners (E-mail)
Subject: Re: DBI Perl and rows...
On Wed, Aug 08, 2001 at 09:27:34AM +0930, Daniel Falkenberg wrote:
> Now what I really want to be able to do is only display 10 of the rows in
> the database then create a hyperlink to to the next 10 and so on.
Pass along an offset with each request, and structure your SQL query to
use this offset as a starting point. For example, with mysql you'd say
something like:
SELECT * FROM table LIMIT $offset, 10;
This will give you at most 10 rows, starting at $offset. When you output
the HTML put $offset + 10 in a hidden field, or in the query parameters for
the "next" link. This also gives you the ability for a "previous" button or
link, by passing in $offset - 10.
I'll leave edge case checking (such as when you get back less than 10 rows,
indicating you're at the end) for the reader.
Michael
--
Administrator www.shoebox.net
Programmer, System Administrator www.gallanttech.com
--
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
