0 Replies Latest reply on Nov 30, 2006 2:54 PM by SQLSquirrel

    Feel up to a challenge?

    SQLSquirrel
      Hi there,

      I am trying to creat a TV guide of sorts, a table that has a Monady through Friday dynamically generated dependant on current day of week and a list of session times down the left (12 slots in total) pulled from a DB as these will change dependant on timezone (Functionality yet to be built) I have got it working but can only manage to pull 1 record. Any help/suggestions will be greatly appreciated. Thanks in advance

      CODE BELOW

      <cfquery name="all_updates" datasource="btv">
      SELECT *
      FROM tbl_btv

      </cfquery>
      <cfquery name="slot" datasource="btv">
      SELECT *
      FROM slot
      </cfquery>

      <cfset session_slot = slot.session_slot_nsw>
      <cfset session = all_updates.session>
      <cfset bcst_date = all_updates.bcst_date>
      <cfset sdate = #bcst_date#>
      <h1>Business Television / Centrelink Education Network Schedule</h1><p><cfset current_day = dayOfWeek(now())>
      <table width="100%" border="1" cellspacing="0" cellpadding="0">
      <tr><td><strong>SESSION/DATE</strong></td>
      <!--- display dates --->
      <cfloop from="2" to="6" index="d">
      <cfset temp = d - current_day>
      <cfset temp = dateAdd("d", #temp#, now())>
      <cfoutput><td><strong>#dateFormat(temp, "dddd dd, mmmm yyyy")#</strong></td></cfoutput>
      </cfloop>
      </tr>
      <cfset begin = '2006-11-27 00:00:01'>
      <cfset end = '2006-12-01 23:59:59'>
      <cfset variables.start_date = #dateFormat(#begin#, "yyyy/mm/dd")#>
      <cfset variables.end_date = #dateFormat(#end#, "yyyy/mm/dd")#>
      <cfset sdate = #dateFormat(#sdate#, "yyyy/mm/dd")#>
      <!--- order by time, then date - and only select the records you want to display --->

      <!--- loop through diplay records --->

      <!--- loop through slots --->
      <cfloop query="slot">
      <tr>
      <cfoutput><td><strong>#slot.session_slot_nsw#</strong></td></cfoutput>

      <!--- didn't really look at this bit too much, but should basically be the same --->
      <cfloop from="2" to="6" index="d">
      <cfset temp = d - current_day>
      <cfset temp = dateAdd("d", #temp#, now())>
      <cfquery dbtype="query" name="updates">
      SELECT *
      FROM all_updates
      ORDER BY act_time
      </cfquery>
      <cfoutput><cfif #dateFormat(temp, "dd/mm/yyyy")# eq #updates.bcst_date# and #updates.session# eq #slot.session_slot_nsw#>
      <td><strong>#updates.update_type#: <cfif #updates.update_type# eq 'CEN'>(#updates.ad_type#)</cfif></strong> <a href="##">#updates.update_title#</a> #updates.bcst_date# #updates.session# </td>
      <cfelse>
      <td><em>NO MATCH</em></td>
      </cfif></cfoutput>
      </cfloop>
      </tr>
      </cfloop>


      </table>