ColdFusion query get current row values?

Question

I have a query that can return multiple records. I have two columns in my query, one column outputs the date values and second column is the type. I want to check the type of the each row and output the dates in the list. My current code for some reason outputs all date values in the same input field and that's not what I want. Here is my code:

<cfquery name="getUserRec" datasource="MyDBone">
    SELECT CONVERT(VARCHAR(10), u_begDt, 101) AS u_begDt, u_type    
    FROM Users WITH (NOLOCK)
    WHERE u_uid = <cfqueryparam value="#uid#" cfsqltype="cf_sql_char" maxlength="15">
        AND u_type IN ('A','C','M','S')
</cfquery>

Query will produce records like this:

u_begDt     u_type
03/16/2017    A 
03/01/2017    C 
03/01/2017    S
03/16/2017    M
02/01/2013    S
07/16/2015    A

Now I would like to output these record in 4 separate input fields:

<cfoutput>
   <input type="hidden" name="begDtA" id="begDtA" value="<cfif trim(getUserRec.u_type) EQ 'A'>#ValueList(getUserRec.u_begDt,",")#</cfif>" readonly="readonly" />
   <input type="hidden" name="begDtC" id="begDtC" value="<cfif trim(getUserRec.u_type) EQ 'C'>#ValueList(getUserRec.u_begDt,",")#</cfif>" readonly="readonly" />
   <input type="hidden" name="begDtM" id="begDtM" value="<cfif trim(getUserRec.u_type) EQ 'M'>#ValueList(getUserRec.u_begDt,",")#</cfif>" readonly="readonly" />
   <input type="hidden" name="begDtS" id="begDtS" value="<cfif trim(getUserRec.u_type) EQ 'S'>#ValueList(getUserRec.u_begDt,",")#</cfif>" readonly="readonly" />
</cfoutput>

My current code will output all date values in the same hidden field, looks that my cfif statements are ignored/incorrect. If anyone see where is my problem or different way to approach this problem please let me know.

Answer 1

Do you actually need to pre-populate the fields with a list of values or just produce that result on the action page?

If you just need to produce that result, then no need to do anything special. Simply create multiple fields with the same name. The result will be a csv list for each type on the action page.

<cfoutput query="getUserRec">
    <input type="text" name="begDt#getUserRec.u_type#"  
         value="#dateFormat(getUserRec.u_begDt, 'mm/dd/yyyy')#" />
</cfoutput> 

If you really do need to pre-populate the fields with a list of values, use a grouped cfoutput. Modify your database query to order by u_type. (No need to format the dates in SQL. Leave that to the front end code). Then use a grouped cfoutput to build a list of values for each u_type.

<cfoutput query="getUserRec" group="u_type">
    <cfset dates = []>
    <cfoutput>
        <cfset arrayAppend(dates, dateFormat(getUserRec.u_begDt, "mm/dd/yyyy"))>
    </cfoutput> 
    <input type="text" name="begDt#getUserRec.u_type#" value="#arrayToList(dates)#" />
</cfoutput> 

Result:

BEGDTA  03/01/2015,03/16/2017
BEGDTC  03/01/2017
BEGDTM  03/16/2017
BEGDTS  02/01/2013,03/01/2017