CODEWITHSUNDEEP
coldfusioncoldfusion-10
Jack
Jack

Reputation: 863

CFOUTPUT looping

I am trying to display and process 26 rows of records from the database and process the rows like this:

<cfquery datasource="mydata" username="myuser" password="mypass" name="mylist" cachedWithin="#createTimeSpan( 0, 0, 5, 0 )#">
    SELECT field1,field2
    WHERE product = "myproduct"
    ORDER BY idfield DESC LIMIT 26
</cfquery>

<!--- process them --->
<cfoutput query="mylist" maxRows=10>
    <!--- get the latest 6 record --->
    <cfif mylist.CurrentRow lte 6>
        <!--- do something special with the latest record --->
        <cfif mylist.CurrentRow eq 1> 
            do something special
            <cfoutput>
                mylist.CurrentRow - #mylist.CurrentRow#<br> 
            </cfoutput>
        <cfelse> 
            do something else
        </cfif>
        <cfif mylist.CurrentRow lte 3>
            do something   
        </cfif>
    </cfif>
    do something
</cfoutput>

I was expecting to see one line of output like this:

mylist.CurrentRow - 1

But instead I got this:

mylist.CurrentRow - 1
mylist.CurrentRow - 2
mylist.CurrentRow - 3
mylist.CurrentRow - 4
mylist.CurrentRow - 5
mylist.CurrentRow - 6
mylist.CurrentRow - 7
mylist.CurrentRow - 8
mylist.CurrentRow - 9
mylist.CurrentRow - 10
mylist.CurrentRow - 11
mylist.CurrentRow - 12
mylist.CurrentRow - 13
mylist.CurrentRow - 14
mylist.CurrentRow - 15
mylist.CurrentRow - 16
mylist.CurrentRow - 17
mylist.CurrentRow - 18
mylist.CurrentRow - 19
mylist.CurrentRow - 20
mylist.CurrentRow - 21
mylist.CurrentRow - 22
mylist.CurrentRow - 23
mylist.CurrentRow - 24
mylist.CurrentRow - 25
mylist.CurrentRow - 26

This is really puzzling. Because it is supposed to display only if the row is equal to 1. Why am I seeing all these rows?

Secondly, why am I seeing 26 rows, when I set the maxrows to 10?

Hope someone can enlighten me on this. Thanks in advance.

Upvotes: 1

Views: 87

Answers (1)

Subhi
Subhi

Reputation: 81

First of all, the maxrows limits the number of results in the query variable after its returned from the database.

If you are only ever returning a single result then this is not going to have any impact at all on the performance of your query.

ColdFusion does allow you to pass bind parameters using the CFQUERYPARAM tag.

For example:

<cfquery name="q">
    SELECT property1, property2, property3 
    FROM yourTable 
    WHERE RowID = <cfqueryparam value="#NumericVariable#" cfsqltype="CF_SQL_INTEGER" />
</cfquery>

You can hope to improve the speed on your database by providing a bind parameter and specifying the properties to return. This may allow for better query caching and performance improvements depending on the database engine you are using.

I'd add its generally more secure to use CFQUERYPARAM than to leave variables unqualified and potentially open to SQL injection attacks.

Second of all, the reason why you're getting all the results is because the inner cfoutput

<cfoutput>
 mylist.CurrentRow - #mylist.CurrentRow#<br> 
</cfoutput>

What i guess would be a better solution is to have your query like this:

<cfquery datasource="ECS360"  name ="mylist">
  select *
  from employee.employee
  ORDER BY employeenumber DESC
</cfquery>

<cfoutput query="mylist" maxRows="26">
<br/>
<!--- get the latest 6 record --->
    <cfif #mylist.CurrentRow# lte 6>
        <!--- do something special with the latest record --->
        currnet row #mylist.currentrow#
        <cfif #mylist.CurrentRow# eq 1> 
            do something special
                #mylist.CurrentRow# - #mylist.CurrentRow#<br> 
        <cfelse> 
        currnet row #mylist.currentrow#
            do something else
        </cfif>
        <cfif #mylist.CurrentRow# lte 3>
            do something 123  
        </cfif>
    </cfif>
</cfoutput>

Upvotes: 1

Related Questions