How can I loop over a query for a specific number of times that may be greater than the result?

Question

I need to loop over a query exactly 12 times to complete rows in a form but rarely will the query return 12 rows. The cfquery endRow attribute doesn't force the loop to keep going if the result is < 12. If it did that would be ideal to use something like cfloop query="myQuery" endRow="12"... The 2 options that I have now are to skip the loop and write out all 12 rows but that results in a lot of duplicate code (there are 20 columns), or do a query of queries for each row which seems like a lot of wasted processing. Thanks for any ideas.

Answer 1

You can simply use

maxrows="12"

Although, I think there might be something wrong with your logic for having to do that. perhaps if you post some of our code, i can take a look a suggest a better approach.

maxRows will do the trick for now though

UPDATE

Forgot to mention, maxrows have to be used with "cfoutput query", as cfloop query won't support it.

In this case you'll do somethig like:

<cfoutput query="myQuery" maxRows="12">

UPDATE of UPDATE

After understanding exactly what you wanted, I wrote the following code which is pretty much what I reckon you need:

<cfscript>
    qryTest = QueryNew("name,email");
    newRows = QueryAddRow(qryTest,5);

    tmp = querySetCell(qryTest, 'name', 'John', 1);
    tmp = querySetCell(qryTest, 'email', '[email protected]', 1);

    tmp = querySetCell(qryTest, 'name', 'Paul', 2);
    tmp = querySetCell(qryTest, 'email', '[email protected]', 2);

    tmp = querySetCell(qryTest, 'name', 'George', 3);
    tmp = querySetCell(qryTest, 'email', '[email protected]', 3);

    tmp = querySetCell(qryTest, 'name', 'Ringo', 4);
    tmp = querySetCell(qryTest, 'email', '[email protected]', 4);

    tmp = querySetCell(qryTest, 'name', 'Yoko', 5);
    tmp = querySetCell(qryTest, 'email', '[email protected]', 5);
</cfscript>

<cfdump var="#qryTest#">

<form name="test">
    <cfoutput>
        <cfloop from="1" to="12" index="ii">
            <cfif ii GT qryTest.recordCount>
                <cfset tmp = QueryAddRow( qryTest, ii)>
            </cfif>
            Name: <input type="text" name="name_#ii#" value="#qryTest.name[ii]#"><br />
            Wmail: <input type="text" name="email_#ii#" value="#qryTest.email[ii]#"><br /><br />
        </cfloop>
    </cfoutput>
</form>

<cfdump var="#qryTest#">

This will add new rows to your query dynamically should it be necessary (i.e. in case you don't have 12 rows on your recordset)

It's mimicking a recordset just so you can copy and paste the code and see the results.

hope it helps ;-)

Answer 2

If you don't care about column values you could try something like this...

 <cfquery NAME="testQuery" datasource="#DB#" >
    SELECT 
       SOMETHING 
    FROM
      SOMETHING
 </cfquery>

 <cfif testQuery.recordcount LT 12>

   <cfset temp = QueryAddRow( testQuery, 12- testQuery.recordcount)>

 </cfif>