Professional Web Applications Themes

How to limit # of rows returned from query - Macromedia ColdFusion

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...

  1. #1

    Default How to limit # of rows returned from query

    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
    Steve Grosz Guest

  2. #2

    Default Re: How to limit # of rows returned from query

    use the maxrows attribute of the cfquery tag, not cfoutput
    Cole Guest

  3. #3

    Default Re: How to limit # of rows returned from query

    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
    Steve Grosz Guest

  4. #4

    Default Re: How to limit # of rows returned from query

    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

    Cole Guest

  5. #5

    Default Re: How to limit # of rows returned from query

    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]

    Latino Guest

  6. #6

    Default Re: How to limit # of rows returned from query

    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">

    sillyworm Guest

  7. #7

    Default Re: How to limit # of rows returned from query

    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">
    >
    Steve Grosz Guest

  8. #8

    Default Re: How to limit # of rows returned from query


    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

    david_h Guest

Similar Threads

  1. Limit the Number of Characters Returned
    By d088 in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 17th, 08:37 PM
  2. Deleting specific rows from a returned query result
    By madgett in forum Coldfusion Database Access
    Replies: 3
    Last Post: June 30th, 08:49 PM
  3. Limit Data returned to table
    By sjmyles in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 8th, 04:35 PM
  4. limiting rows returned in a sql select
    By Lennart Jonsson in forum IBM DB2
    Replies: 2
    Last Post: August 26th, 03:41 AM
  5. Replies: 1
    Last Post: August 1st, 03:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139