4 Replies Latest reply on Jun 6, 2007 9:00 AM by insuractive

    Populate 2 Columns from Query

    IraMSN Level 1
      I have a query that selects all performers in the current week. There are many rooms that a performer can be scheduled in and sometimes there is no performer scheduled in a particular room or at a particular time.

      The query looks like this:

      <cfquery datasource="#application.ds#" name="qrySchedule" maxrows="1">
      select *
      from SHOW_SCHEDULE
      WHERE calend >= #createodbcdate(now())#
      order by calend asc
      </cfquery>

      The table row has about 20 fields ie; room1earlyperformer1, room1lateperformer1, room1earlyperformer2, room1lateperformer2, room2earlyperformer1, room2lateperformer1, room2earlyperformer2, room2lateperformer2 ...etc.

      I would like to be able to display this query output in a 2 column table while not leaving any table cells blank if there is no performer scheduled in a particular room at a particluar time.

      Can someone please help with a code example? Thank you!
        • 1. Re: Populate 2 Columns from Query
          Dan Bracuk Level 5
          Normalize your database and you won't have problems like this.
          • 2. Re: Populate 2 Columns from Query
            insuractive Level 3
            Just out of curiousity, if not blank, what would you like to see in the table cell when there is no performer scheduled?
            • 3. Re: Populate 2 Columns from Query
              IraMSN Level 1
              quote:

              Originally posted by: insuractive
              Just out of curiousity, if not blank, what would you like to see in the table cell when there is no performer scheduled?
              Michael,

              If the performer record is empty, just skip it and populate the cell with the next record that contains data.
              • 4. Re: Populate 2 Columns from Query
                insuractive Level 3
                Its pretty unclear from your description what exactly your 2 column table consists of and how it relates to your 20+ fields. So are you trying to do something like this:

                Room: Performer:
                ============================
                1 Joe (Early)
                1 Pete (Late)
                2 Simon (Early)

                If so, I think you really do need to follow Dan's advice and normalize your database. Instead of 1 table with 20+ fields, you probably should have 3 tables

                Table 1: Rooms
                ================
                Room ID

                Table 2: Performers
                ================
                Performer ID
                Performer Name

                Table 3: Schedule
                ================
                Schedule ID
                Room ID
                Performer ID
                Performer Order

                This structure would give you a lot more flexibility over how to control and retrieve your data.