22 Replies Latest reply on Mar 26, 2007 9:08 AM by cf_dev2

    display times

    JohnGree Level 1
      i have a diary system, which shows times on a page ie

      08:00
      09:00
      10:00 ect

      these are displayed from my table as users.starttime to a users.endtime using a increment column = 60

      what i need to do is if a diary entry is inserted into my diary table as

      DiaryDate = 2007-04-12
      DiaryTime = 08:00:00
      DiaryuserID = 1
      DiaryWith = paul
      DiaryPeriod = 120

      i need to display the record above in 2 time slots, as the diaryperiod is 120mins ie

      08:00 paul
      09:00 paul
      10:00

      but this could change if the increment in my users table gets altered by the user.

      any ideas
        • 1. Re: display times
          jdeline Level 1
          One way to do this is to have a database table that contains the columns the fields you describe above. Query the database, sorting on DiaryDate, DiaryTime.

          If you get a data/time match when displaying the time slots, output the DiaryWith value.
          • 2. Re: display times
            JohnGree Level 1
            yes thats exactly what i have now but the problem is, i have

            08:00
            08:30
            09:00 etc displayed on the page,

            a user enters a new appt using these form fields, which gets put into my database.

            DiaryDate = 2007-04-12
            DiaryTime = 08:00:00
            DiaryuserID = 1
            DiaryWith = paul
            Duration = 90

            the duration is 90 mins so i need to display it against 3 time slots
            08:00 paul
            08:30 paul
            09:00 paul
            • 3. Re: display times
              cf_dev2 Level 1
              JohnGree,

              If you know the duration and the interval, then you can calculate the number of time slots used for each appointment.

              Duration / Interval = Number of time slots
              90 / 30 => Number of time slots => 3

              Can't you simply use that number in your output loop to display the name "Paul" 3 times (ie number of time slots)?

              08:00 paul
              08:30 paul
              09:00 paul
              • 4. Re: display times
                JohnGree Level 1
                ok thanks that sounds good, how would i add that loop to my current code below ie

                if i cfset display = Duration / Interval

                how would i add #display# to the loop?

                <cfset steptime = getevents.starttime>

                <cfloop condition="DateCompare(StepTime, getevents.EndTime) LT 1">
                <cfquery name="GetClients" datasource="#application.ds#">
                SELECT *
                FROM Diary_Table C
                INNER JOIN USERS_Table f
                ON C.DiaryPlayerID = f.PlayerID
                WHERE C.DiaryDate = '#session.SHORTDATE#' AND C.DiaryTime = <cfqueryparam value="#steptime#" cfsqltype="CF_SQL_TIMESTAMP">
                </cfquery>

                <cfoutput>
                <tr bordercolor="##000066">
                <td>#TimeFormat(StepTime, "hh:mm")#<cfset StepTime = dateadd("n", getevents.Increment, StepTime)></td>
                <td><cfoutput>#GetClients.name#</cfoutput></td>
                <td>#GetClients.DiaryWith#</td>
                </tr></cfoutput>
                </cfloop>
                • 5. display times
                  cf_dev2 Level 1
                  With that loop, the first suggestion might be simpler coding. But try a QoQ inside the loop to reduce database hits. I confess I'd do it differently, but thats probably more complex than what you need here.

                  [UPDATE] Fixed query typo: DateAdd(n, Duration-1, DiaryTime) AS EndTime [/UPDATE]
                  • 6. Re: display times
                    JohnGree Level 1
                    hi i have tried that but i get this error below i am using mysql

                    Error Executing Database Query.
                    Column not found: Unknown column 'n' in 'field list'

                    The error occurred in D:\inetpub\vhosts\golfprodiary.com\httpdocs\CalendarCurrent.cfm: line 30

                    <cfquery name="GetClients" datasource="#application.ds#">
                    SELECT PlayerFirstName, PlayerSurname, DiaryWith, DiaryTime AS StartTime,
                    DateAdd(n, Duration, DiaryTime) AS EndTime
                    FROM Diary_Table C
                    INNER JOIN users f ON C.DiaryPlayerID = f.PlayerID
                    WHERE C.DiaryDate = <cfqueryparam value="#session.SHORTDATE#" cfsqltype="CF_SQL_TIMESTAMP">
                    </cfquery>


                    • 7. display times
                      cf_dev2 Level 1
                      Its probably the DateAdd function. The syntax can vary depending on the database. Check your database documentation.
                      • 9. display times
                        JohnGree Level 1
                        ok i have thr code below but nothing displays against the times ie

                        the page displys fine but no records get attached to the time slots

                        <cfquery name="GetClients" datasource="#application.ds#">
                        SELECT PlayerFirstName, PlayerSurname, DiaryWith, DiaryTime AS StartTime,
                        DATE_ADD('DiaryTime',INTERVAL Duration MINUTE) AS EndTime

                        FROM Diary_Table C
                        INNER JOIN SMS_Players_Table f ON C.DiaryPlayerID = f.PlayerID
                        WHERE C.DiaryDate = <cfqueryparam value="#session.SHORTDATE#" cfsqltype="CF_SQL_TIMESTAMP">
                        </cfquery>

                        <cfset steptime = getevents.starttime>

                        <cfloop condition="DateCompare(StepTime, getevents.EndTime) LT 1">


                        <cfquery name="GetAppointment" dbtype="query">
                        SELECT PlayerFirstName, PlayerSurname, DiaryWith
                        FROM GetClients
                        WHERE StartTime <= <cfqueryparam value="#steptime#" cfsqltype="CF_SQL_TIMESTAMP">
                        AND EndTime >= <cfqueryparam value="#steptime#" cfsqltype="CF_SQL_TIMESTAMP">
                        </cfquery>
                        <cfoutput>
                        <tr bordercolor="##000066">
                        <td bgcolor="##CCCCCC" class="StandBlack">#TimeFormat(StepTime, "hh:mm")#</td>
                        <td bgcolor="##CCCCCC" class="StandBlack">#GetAppointment.PlayerFirstName# #GetAppointment.PlayerSurname#</td>
                        <td bgcolor="##CCCCCC" class="StandBlack">#GetAppointment.DiaryWith#</td>
                        <td bgcolor="##CCCCCC"><div align="center"><cfif GetClients.PlayerSurname is not ''>
                        <a href="SMS_Recipients.cfm" onclick="newWin(this.href,350,250,'no','yes'); return false;"><img src="images/dtick.gif" width="15" height="17" border="0"></a>
                        </cfif></div></td>
                        <td bgcolor="##CCCCCC"><div align="center"> <cfif GetClients.PlayerSurname is not ''>
                        <a href="SMS_Schedule_Delete.cfm"><img src="images/dx.gif" width="15" height="17" border="0"></a>
                        </cfif></div></td>
                        </tr>
                        </cfoutput>
                        <cfset StepTime = dateadd("n", getevents.Increment, StepTime)>
                        </cfloop>
                        • 10. Re: display times
                          cf_dev2 Level 1
                          What are the types of the DiaryDate and DiaryTime columns?
                          What is a sample of the values in those columns?
                          • 11. Re: display times
                            JohnGree Level 1
                            DiaryDate = 2007-03-24

                            DiaryTime = 11:00:00
                            • 12. Re: display times
                              cf_dev2 Level 1
                              What are the types of the DiaryDate and DiaryTime columns: Date, Timestamp.. ? Same for Getevents.EndTime
                              • 13. display times
                                JohnGree Level 1
                                DiaryDate = DATE

                                DiaryTime = time

                                Getevents.EndTime = time

                                • 14. Re: display times
                                  cf_dev2 Level 1
                                  I'm not that familiar with MySQL functions. The documentation says Date_Add accepts a date or datetime value. I suspect passing in a "time" value is causing problems.

                                  Without changing the column types, I could only get it to work using an ugly sequence of nested functions. I'm certain there is a cleaner way. You might post your question in the database forum. Someone more experienced with MySQL could provide a better answer to your question.
                                  • 15. display times
                                    JohnGree Level 1
                                    ok i have changed the DiaryTime to a DATETIME

                                    but it still is not displaying?
                                    • 16. display times
                                      efecto747 Level 1
                                      Hi, your code references a query called 'getevents' - can you post that query?

                                      Also, have you checked the GetClients query is actually picking up data? try adding a cfdump of the GetClients query to confirm there is data there for the QofQ.

                                      ie. add <cfdump var="#GetClients#"> after the GetClients query.
                                      • 17. Re: display times
                                        JohnGree Level 1
                                        hi the query is below, i have tried all different dateadd functions from the mysql website but still no luck


                                        <cfquery name="GetClients" datasource="#application.ds#">
                                        SELECT PlayerFirstName, PlayerSurname, DiaryWith, DiaryTime AS StartTime,
                                        DATE_ADD('DiaryTime',INTERVAL Duration MINUTE) AS EndTime

                                        FROM Diary_Table C
                                        INNER JOIN SMS_Players_Table f ON C.DiaryPlayerID = f.PlayerID
                                        WHERE C.DiaryDate = <cfqueryparam value="#session.SHORTDATE#" cfsqltype="CF_SQL_TIMESTAMP">
                                        </cfquery>
                                        • 18. Re: display times
                                          efecto747 Level 1
                                          ok, thats the "getClients" query but where does "getevents" come from?

                                          also see my note above re: cfdump.

                                          cheers.
                                          • 19. Re: display times
                                            cf_dev2 Level 1
                                            > could i change them all to datetime would that work

                                            Since I'm not familiar with the nuances of time and date fields in MySQL I can only offer gueswork. You're better off getting advice from someone that uses MySQL.

                                            Playing around with the original column types, the best I could come up with is

                                            SELECT Name, DiaryWith, DiaryTime AS StartTime,
                                            SEC_TO_TIME(TIME_TO_SEC(DiaryTime) + ((Duration-1) * 60)) AS EndTime
                                            FROM Diary_Table C INNER JOIN USERS_Table f ON C.DiaryPlayerID = f.PlayerID
                                            WHERE C.DiaryDate = '#session.SHORTDATE#'

                                            Good Luck
                                            • 20. display times
                                              JohnGree Level 1
                                              ok yes the cfdump works fine but i get

                                              2007-03-24 11:30:00.0 as the ENDTIME is that correct

                                              <CFQUERY datasource="#application.ds#" Name="getevents">
                                              SELECT *
                                              FROM SMS_Clubs_Table
                                              WHERE ClubID = '#session.ClubLogin#'
                                              </cfquery>
                                              • 21. Re: display times
                                                JohnGree Level 1
                                                ok cf_dev2

                                                that works, only one thing though if i have two entrys in my table for the same time but different appointments, i need to show them like this

                                                11:00 paul
                                                John
                                                12:00 jeff

                                                at the min it only shows paul, do you know how i can do this
                                                • 22. Re: display times
                                                  cf_dev2 Level 1
                                                  If you won't have empty values, try the ValueList() function. Use a line break as the delimiter.