CFLOOP Duplication

Question

I have a drop down list that is generated by two loops. The inner loop generates a series of numbers ie from 0 to 23. The outer loop, is a query loop that selects the correct value from the 23 numbers based on the values stored in my database.

My issue here is that those two loops conflict that results in displaying the numbers from 0 to 23 twice. How can keep both loops but avoiding this issue? This problem also cause issues when the form is submitted by trying to submit the form twice and deleting the user's input.

This is my code:

<select id="openHours#CountVar#" name="openHours#CountVar#">
 <cfloop query="doctorHours" >                
   <cfloop from="0" to="23" index="OpenHours"> 
   <option value="#openHours#"
      <cfif TimeFormat(doctorHours.openTime,'HH') EQ OpenHours AND CountVar EQ doctorHours.day > selected="selected"</cfif>>#OpenHours#</option>
   </cfloop>
  </cfloop>
</select>

This is my CFDUMP for that query

query
RESULTSET   
query
    CLOSETIME           DAY DOCTORID            OPENTIME
1   1970-01-01 16:00:00.0   4   2011041516290025478779  1970-01-01 10:00:00.0
2   1970-01-01 16:00:00.0   1   2011041516290025478779  1970-01-01 13:00:00.0
3   1970-01-01 16:00:00.0   2   2011041516290025478779  1970-01-01 13:00:00.0
CACHED  false
EXECUTIONTIME   0
SQL select doctorID, opentime, closetime, day from doctorBusinessHours where doctorID='2011041516290025478779'

*Edit* 1) That suggests your query contains more records than you think it should. If so, you need to investigate *why* 2) Second, using a query loop generally means you *want* the code inside it to run once for each record in the query. If that is not the desired result, maybe you should not be using a query loop ;) For a simple comparison, you could use the shortcut #queryName.columnName# to grab the value in the first row of the query. — Leigh, Apr 10 '12 at 20:22

score 2 · Accepted Answer · edited Apr 10 '12 at 22:54

2

You should return only the hours you need and then loop for the dropdownlist creation:

DATEPART(hh,yourdate) will return the hours for your datetime value:

<cfquery name="doctorHours" datasource="#ds#">
    SELECT doctorID,DATEPART(hh,openTime) As OpenHours, DATEPART(hh,closetime) As CloseHours 
    FROM   doctorHours 
    WHERE  day = #CountVar#
    AND    doctorID='#docID#'
</cfquery>

ValueList will transform your query results into a list:

<cfset openTimesList = ValueList(doctorHours.OpenHours) />
<cfset closeTimesList = ValueList(doctorHours.CloseHours ) />

ListContains will return the index of the value within your list:

<select id="openHours#CountVar#" name="openHours#CountVar#">
    <cfloop from="0" to="23" index="OpenHours"> 
       <option value="#openHours#"
           <cfif ListContains(openTimesList,OpenHours) NEQ 0 > 
              selected="selected"
           </cfif>
       >#OpenHours#</option>
   </cfloop>
</select>

You can use the same strategy for the closeTimesList.

edited Apr 10 '12 at 22:54

Leigh

28,765
10
55
103

answered Apr 10 '12 at 20:30

Francis P

13,377
3
27
51

This raises a good question. @Crematorio, in your earlier questions `doctorHours` seemed to contain only one record. Is it *supposed to* to contain one record -or- multiple records? – Leigh Apr 10 '12 at 20:37
1

I supposed it would be multiple hours and the control would let multiple selected values.. – Francis P Apr 10 '12 at 20:40
Yep, and you could be right. I just was not sure if the multiple query records was deliberate, or due to an error. @Crematorio - can you clarify? – Leigh Apr 10 '12 at 20:47
The doctor hours contain multiple records. The database looks like this: recID DoctorID Day openTime closeTime. The reason of the query loop is to go in the database and based on selected="selected" to show the end user which hour is currently selected. The second loop generates just a list of 24 numbers (0-23) that the user would use to update a record. – Geo Apr 10 '12 at 20:48
@FrancisP your code stops working at I am currently going through the CF documentation to read about it since I haven't used it before. The ValueList() function has an invalid parameter. That's the error i am getting. – Geo Apr 10 '12 at 20:49
@FrancisP also if i leave dbtype="query" the way it is, it complains about the parenthesis so I changed it to datasource="#ds#". Any thoughts on that? – Geo Apr 10 '12 at 20:54
The best would be to redefine your `doctorHours` query to return the columns you need... – Francis P Apr 10 '12 at 20:55
That's what i have for that part: select doctorID, opentime, closetime, day from doctorBusinessHours where doctorID='#docID#' – Geo Apr 10 '12 at 20:56
1

Ok I'll edit my answer to fit your needs than...gimme 5 seconds – Francis P Apr 10 '12 at 20:57
Thanks a lot for your help @FrancisP !!! The code is working. I suppose I would use the same format when writing the code for closing hours and minutes as well correct? – Geo Apr 10 '12 at 21:03
@Crematorio - I could be wrong, but I am not so sure this will always work as expected. The general concept is right, but if yiu are generating one list, for each day of the week, don't you also want to match the hours on `day`. Can you update your question with a cfdump of the `doctorsquery` query? – Leigh Apr 10 '12 at 21:25
@FrancisP - Typically you only want to find whole elements, so `ListFind/ListFindNoCase` is safer because `ListContains` matches partial elements too. (Though it in this specific case, it probably will not make a difference) – Leigh Apr 10 '12 at 21:33
Thanks. So you are using that query to generate how many select lists: seven (sunday-saturday)? What does your final code look like - just trying to figure out if you are/or aren't properly handling the `day`? :) – Leigh Apr 10 '12 at 21:45
I am generating a table with the seven days. The each day has four fields openHour/openMin and closeHour/closeMin. After I fix this idiotic javascript that im working on at the moment for validation control, I am planning to write a query that either updates a current record or inserts a new one – Geo Apr 10 '12 at 21:50
Understood. But you still did not say whether or not your final code is taking into account the `day` .. hopefully so ;) Btw, next time you might want to post a small query dump up front. It will help give a better sense of what is going on, without a lot of extended discussion. "A data dump is worth a thousand words" ..:) – Leigh Apr 10 '12 at 22:09
I am using day to avoid duplication if a docid/day match is already in the database. – Geo Apr 10 '12 at 22:12
I think you misunderstood. I "got" the structure. I was asking how you were ensuring the code matched up the right hours with the right list. ie Wednesday's hours with the "Wednesday" select list and not some other day of week :) BUT I see from Francis' update it is possible you are now querying by `day` too, not just on `doctorID` (which is different than the query dump you posted). In which case the question is now moot, and you do not even need list functions. – Leigh Apr 10 '12 at 22:59

Mark A Kruger · Answer 2 · 2012-04-10T20:37:36.113

1

Hmmm....the number of values displayed in the code above will be equivelant to the number of records returned by the query X 23. If you're query returns 2 records you will see 46 options and so on. It seems like you believe the query has only 1 record. I would suggest perhaps it has more.

Try LIMIT 1 or TOP 1 in your query - or use Maxrows (as suggested in the comments)... but make sure you know what you are including and what you are excluding. You need to know why your query is not what you expect :)

edited Apr 10 '12 at 20:37

answered Apr 10 '12 at 20:11

Mark A Kruger

7,183
20
21

a 'solution' would be to use `` this would limit your query to output just one row, but I would suggest determining why your query is returning 2 rows instead of just 1 – Matt Busche Apr 10 '12 at 20:14
1

Added a "solution" of sorts... Sometimes these questions engender other questions though :) – Mark A Kruger Apr 10 '12 at 20:38

CFLOOP Duplication

2 Answers2