How to limit # of rows returned from query

[Steve Grosz]

I am looking at setting a limit to the number of rows returned on some
of my queries.

I was looking at doing something like:

<cfoutput query="test" maxrows="5">

But when I tested this, it returned more than 5 rows. How would I
accomplish this? Also, if I wanted to indicate after the last row that
there were 'more' results to view, how can I do that, and then have a
user click the 'more results' or whatever the text will be to see the
other results?

Thanks!

Steve

[Cole]

use the maxrows attribute of the cfquery tag, not cfoutput

[Steve Grosz]

And what about the rest of the question. When there are more than
maxrows, how to either display on a 2nd page, or have a button to push
letting the user know there are more rows to return?

Cole wrote:

> use the maxrows attribute of the cfquery tag, not cfoutput

[Cole]

if you use maxrows, there will never be more rows than what you specify. If
your looking to break up a large dataset, you might investigate one of the many
cf_paging tags available in the coldfusion exchange

[Latino]

If you want to limit the numbers of row returned in your query, you might try
this, asumming you are using mySQL as your database.

<cfquery name='' datasource=''>
select *
from tableName
LIMIT 5
</cfquery>
Then in your cfoutput,
<cfoutput query=''>
Will only return 5 rows...
</cfoutput>

Hope this works for you

[url]http://www.tecito.com[/url]

[sillyworm]

Hi Steve,

The first part should work the way you're doing it using the maxrows
attribute. Post your entire code to see why you might be returning more than 5
results. For paging, you simply want to use the startrow attribute. So in your
query, return all the rows, then, on each page, use something like <cfoutput
startrow="startVariable" maxrows="endVariable">

[Steve Grosz]

I know why I'm returning more than 5 rows, because there are more than 5
entries in the database. I'm trying to make it easier for viewiers of
the page to get to the next group of entries, rather than having to
scroll down a long list of items returned.

sillyworm wrote:

> Hi Steve,
>
> The first part should work the way you're doing it using the maxrows
> attribute. Post your entire code to see why you might be returning more than 5
> results. For paging, you simply want to use the startrow attribute. So in your
> query, return all the rows, then, on each page, use something like <cfoutput
> startrow="startVariable" maxrows="endVariable">
>

[david_h]

Steve, you'll have to use the startrow, maxrows attribute of the cfoutput,
however you may want to cache your query. If it's a heavy query your datbase
will have to re-run the query on every page.

What we do is put a query comment (we have a search id that we place in it)
tell CF to cache it within like 10 mins. So each time they do a new search
they get a new id, but as their paging (yes you need to pass the search id
around) they pull it from CF and there is no need for a trip to the DB again

Originally posted by: Latino
<cfquery name='' datasource=''>
select *
from tableName
LIMIT 5
</cfquery>


[url]http://www.tecito.com[/url]

Just a note...
You should not do it this way (sorry latino) but, when you tell a database to
do this, it gets the first 5 rows, then applies your where clause to it. if
it's a huge table with lots of varying data... you will not get correct results