Skip to main content
A
Anonymous
July 16, 2007
Question

Selecting content only rows

  • July 16, 2007
  • 5 replies
  • 668 views
Is there a way to select only rows that have data instead of using a cfif tag after the query? I want to query a table and before returning results I want to only do the select statement if the row has data. Right now there are rows deleted from my db and I am selecting rows based on id numbers so if I select a deleted row I have to return a "No record found" message using cfif.recordcount eq 0. Is there a better way to do this?
    This topic has been closed for replies.

    5 replies

    A
    Anonymous
    July 20, 2007
    Thanks Dan. Glad to see there are awesome people like you answering legitimate questions. GFY.
    D
    Dan_Bracuk
    Inspiring
    July 17, 2007
    quote:
    Originally posted by: idesdema
    Is there a way to select only rows that have data

    Yes. It's called a Where Clause.
    E
    efecto747
    Participating Frequently
    July 17, 2007
    quote:
    Originally posted by: Dan Bracuk
    Yes. It's called a Where Clause.

    Great answer Dan, I'm sure that will inspire the OP to "Please come back and answer somebody else's question."

    A
    Anonymous
    July 16, 2007
    Be sure to add an order by on the id number.
    C
    cf_dev2
    Inspiring
    July 17, 2007
    draves is correct. Modify your query to grab the first record with an ID that is > #TheLastID# using the TOP operator (ms sql server) or LIMIT (mysql). The correct operator and syntax is db specific.

    --- ms sql server untested
    SELECT TOP 1 ID, OtherColumns
    FROM YourTable
    WHERE ID > <cfqueryparam value="#TheLastID#" cfsqltype="...">
    ORDER BY ID ASC

    --- mysql untested
    SELECT ID, OtherColumns
    FROM YourTable
    WHERE ID > <cfqueryparam value="#LastID#" cfsqltype="...">
    ORDER BY ID ASC
    LIMIT 1

    Or use a subquery, assuming your db supports them

    --- untested
    SELECT ID, OtherColumns
    FROM YourTable
    WHERE ID = ( SELECT MIN(ID) FROM YourTable
    WHERE ID > <cfqueryparam value="#LastID#" cfsqltype="...">
    )



    A
    Anonymous
    July 16, 2007
    Change the the where clause to look for all value greater or equal to the last Id plus one and set the maximum number of returned rows to one.
    C
    cf_dev2
    Inspiring
    July 16, 2007
    You won't know if a record exists unless you run a query. So I don't see how else you could do it, aside from caching which may or may not be feasible. Is there a reason you don't want to use <cfif yourQuery.recordCount eq 0>...?
    A
    Anonymous
    July 16, 2007
    I am using the query for a slideshow so all I do is say +1 to the last id queried for the next row. Sometimes when there is a deleted row it returns a blank page and I hate to do that for a slideshow. One solution I tried is to write an if statement that adds another +1 if there is a recordcount of 0 but that only works when one row is missing. If there are consecutive rows deleted it barfs on me and I get a page not found error.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices