• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Populate 2 Columns from Query

Explorer ,
Jun 03, 2007 Jun 03, 2007

Copy link to clipboard

Copied

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!
TOPICS
Advanced techniques

Views

337

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 03, 2007 Jun 03, 2007

Copy link to clipboard

Copied

Normalize your database and you won't have problems like this.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 05, 2007 Jun 05, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 05, 2007 Jun 05, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 06, 2007 Jun 06, 2007

Copy link to clipboard

Copied

LATEST
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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation