Displaying date field in the value of a datefield form

coldfusiondatetime

I am using CF8 and MySQL 5.

I have a form with several date fields (one for each day and the form may have 10+ days of data on it) that the user can select different dates for and they all have different var names within a loop.

The default values for these date fields is null in the DB. I can insert a date into a DATE column in MySQL with no issues and have verified that the data is inserted correctly (using cfqueryparam DATE also).

I have the 'value' of the form datefield set to the variable name and can not get the value to show up.

The date updates to the DB fine every time it is entered, but when the form posts back to itself the date fields are blank (other non-date fields work fine and changes show up).

Then when I submit it with the blank date fields the value is set back to null in the DB (empty string in the form) since the form field does not pull the value from the DB.

The field name (ses#i#Date) shows up correctly (ses1Date, ses2Date, etc…) in the form with the right value when I dump it. 

<cfloop from="1" to="#form.days#" index="i"> <cfinput type="datefield" name="ses#i#Date" value="#DateFormat(qGetUWHeader["ses#i#Date"],"yyyy-mm-dd")#" /> ....

Thanks for your time and help.

Best Answer

To reference dynamic column names, you can use array notation. But as I mentioned, you must supply a row number. 

 #queryName["columnName"][rowNumber]#

If you know the query contains one (1) record, only, you could use the query object's "recordCount" property as the row number. Alternatively, you could hard code the row number "1". (Personally, I dislike hard-coding). But any one of these should work. 

<!--- pick ONE option ---> 
<cfloop from="1" to="#form.days#" index="i">
   <!--- syntax option 1 ---> 
   <cfinput type="datefield" name="ses#i#Date" value="#DateFormat(qGetUWHeader['ses#i#Date'][qGetUWHeader.recordCount], 'yyyy-mm-dd')#" />
   <!--- syntax option 2 ---> 
   <cfinput type="datefield" name="ses#i#Date" value="#DateFormat(qGetUWHeader['ses'& i &'Date'][qGetUWHeader.recordCount],'yyyy-mm-dd')#" />
   <!--- syntax option 3 ---> 
   <cfinput type="datefield" name="ses#i#Date" value="#DateFormat(qGetUWHeader['ses#i#Date'][1], 'yyyy-mm-dd')#" />
</cfloop>

If however, you are looping through multiple records in the qGetUWHeader query, you can use the query object's "currentRow" property as the row number. But based on the field naming convention, I am guessing the query only contains one (1) record.

EDIT: I forgot about the initial nulls. You could apply a simple if condition, and only call DateFormat() if the query value is a valid date. 

<cfloop from="1" to="#form.days#" index="i">
    <cfset dateValue = qGetUWHeader["ses#i#Date"][qGetUWHeader.recordCount]>
    <!--- if this is a valid date, format the value --->
    <cfif IsDate(dateValue)>
      <cfset dateValue = dateFormat(dateValue, "yyyy-mm-dd")>
    </cfif> 
    <cfinput type="datefield" name="ses#i#Date" value="#dateValue#" /><hr>
</cfloop>

Another option is to format the dates in your SQL. Then you would not need to use CF's DateFormat() function. Just be aware that the new result would be a string, not a datetime object. 

SELECT DATE_FORMAT(ses1Date, '%Y-%m-%d') AS ses1Date, ....
Related Topic