display times

[Guest]

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

[Guest]

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.

[Guest]

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

[cf_dev2]

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

[Guest]

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>

[cf_dev2]

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]

[Guest]

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>

[cf_dev2]

Its probably the DateAdd function. The syntax can vary depending on the database. Check your database documentation.

[cf_dev2]

My bad. The MySQL syntax is different
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[Guest]

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>

[cf_dev2]

What are the types of the DiaryDate and DiaryTime columns?
What is a sample of the values in those columns?

[Guest]

DiaryDate = 2007-03-24

DiaryTime = 11:00:00

[cf_dev2]

What are the types of the DiaryDate and DiaryTime columns: Date, Timestamp.. ? Same for Getevents.EndTime

[Guest]

DiaryDate = DATE

DiaryTime = time

Getevents.EndTime = time

[cf_dev2]

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.

[Guest]

ok i have changed the DiaryTime to a DATETIME

but it still is not displaying?

[efecto747]

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.

[Guest]

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>

[efecto747]

ok, thats the "getClients" query but where does "getevents" come from?

also see my note above re: cfdump.

cheers.

[Guest]

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>

[cf_dev2]

> 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

[Guest]

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

[cf_dev2]

If you won't have empty values, try the ValueList() function. Use a line break as the delimiter.