displaying date field in the value of a datefield form

Question

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.

Answer 1

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, ....

Answer 2

Where is "qGetUWHeader" defined?

When a form is posted, all values will be posted to the form scope. Therefore in order to display a value from a postback, you should reference the form scope unless you're copying the value into 'qGetUWHeader'. Even though the correct date from the form is being stored in the form scope, you're displaying the default value from qGetUWHeader on post back, unless you're doing something there I'm not aware of. Then the next time you post your form, the default value overrides the prior entered value.

<!--- In order to reference FORM values, you must CFPARAM them first to define the default value. --->
<cfparam name="form.days" default="10">
<cfloop from="1" to="#form.days#" index="i">
    <cfparam name="form['ses#i#Date']" default="">
</cfloop>

<!--- Display the Form --->
<cfform action="#cgi.SCRIPT_NAME#" method="post">
    <cfloop from="1" to="#form.days#" index="i">
      <cfset thisFieldName = "ses" & i & "Date">
      <cfset thisFieldValue = form["ses#i#Date"]>
      <cfoutput>#thisFieldName#</cfoutput> <!--- For Debugging --->
      <cfinput type="datefield" name="#thisFieldName#" value="#thisFieldValue#" /><br /><br />
    </cfloop>
    <input type="submit" name="submit" value="submit" />
</cfform>

<!--- Debug --->
<cfdump var="#form#">