Solved

order by date asc but keep "like" records in one <tr>

Posted on 2003-11-04
18
163 Views
Last Modified: 2013-12-24
my problem with the following code is that IF i have 2 different events that are happening on the same date my grouped together events get broken up into seperate table rows to accomodate for the Order By CalendarDate Asc in my query.  

as you can see below i've tried to use
<cfoutput query="timesprices" group="Eventname">
but the order by in the query supercedes this.  Is there any way to order by CalendarDate Asc but also keep all of my similar events in one table row.

you can see what i'm talking about here:
http://redcatweb2.org/calendar/timesprices.cfm



i'm using this query:

<cfquery name="timesprices" datasource="#dsn#">
select distinct Eventname, CalendarDate, Calendar_ID, StartTime, STAMPM, PriceGA, PriceEA, PriceSt from calendar
ORDER BY CalendarDate ASC</cfquery>

and this to output my data:

<cfoutput query="timesprices" group="Eventname">
                        <cfset allcolors="##cccccc,##d4d590,##c2bb9c,##e4eae9,##c4ae5a">
                        
            
            

            <tr style="background-color:#listgetat(allcolors, (timesprices.currentrow mod listlen(allcolors))+1, ',')#;">


          <td align="left" valign="top" width="160" ><span class="frontcopy">
              <cfset myUrl = urlencodedformat(#Eventname#)>
                            <A
                  href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</a></span></td>
          <td align="left" valign="top" width="10" ></td>
          <td width="70" align="left" valign="top" ><span class="frontcopy">

<cfoutput>#dateformat(timesprices.CalendarDate,'m.d.yyyy')#<br></cfoutput>.....
0
Comment
Question by:phillystyle123
18 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9683962
could you do something like..

<cfquery name="timesprices" datasource="#dsn#">
select distinct Eventname, CalendarDate, Calendar_ID, StartTime, STAMPM, PriceGA, PriceEA, PriceSt from calendar
ORDER BY CalendarDate ASC, Eventname
</cfquery>

<cfoutput query="timesprices" group="CalendarDate">
      <cfoutput query="timesprices" group="Eventname">
            <cfset allcolors="##cccccc,##d4d590,##c2bb9c,##e4eae9,##c4ae5a">
            <tr style="background-color:#listgetat(allcolors, (timesprices.currentrow mod listlen(allcolors))+1, ',')#;">
                  <td align="left" valign="top" width="160" ><span class="frontcopy">
                        <cfset myUrl = urlencodedformat(#Eventname#)>
                        <A href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</a></span></td>
                  <td align="left" valign="top" width="10" ></td>
                  <td width="70" align="left" valign="top" ><span class="frontcopy">
                        
                        <cfoutput>#dateformat(timesprices.CalendarDate,'m.d.yyyy')#<br></cfoutput>.....
                        
      </cfoutput>
</cfoutput>
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9684221
Do u mean to say that there shld be only one record for each of ur "EVENT" ???
0
 
LVL 11

Expert Comment

by:hart
ID: 9684468
not so clear what u want buddy ???

Regards
Hart
0
 

Author Comment

by:phillystyle123
ID: 9685610
jyokum

i'll get an error with your code because i'd have a query driven cfoutput nested inside another.

i tried it with

<cfoutput query="timesprices" group="CalendarDate">
     <cfoutput group="Eventname">.....

and it just put each event in it's own table row.
0
 

Author Comment

by:phillystyle123
ID: 9685629
hart - if you go to:

http://redcatweb2.org/calendar/timesprices.cfm

you'll see what i'm talking about.  see how Order by CalendarDate is splitting my similar Eventnames up into seperate colored rows. ie - Eventnames: "new recurr" and "recur weekly" (see "11/17/2003")
0
 
LVL 11

Expert Comment

by:hart
ID: 9692080
i am not sure wether this will work or not

try this query instead of grouping in cfoutput query

select distinct Eventname, CalendarDate, Calendar_ID, StartTime, STAMPM, PriceGA, PriceEA, PriceSt  from calendar group by Eventname ORDER BY CalendarDate ASC

and check if the currentrow of the query is as per a group...
if not then the logic for showing different colors will have to be changed..

Regards
Hart


0
 

Author Comment

by:phillystyle123
ID: 9693154
this is what i got with hart's code:

http://redcatweb2.org/calendar/timesprices2.cfm

the color logic doesn't totally work anyway (and i'm not sure whether or not this can be helped) but i'd love it to change per Eventname.

here's the current color logic i'm using:

<cfset allcolors="##cccccc,##d4d590,##c2bb9c,##e4eae9,##c4ae5a">
                        
            
            

            <tr style="background-color:#listgetat(allcolors, (timesprices.currentrow mod listlen(allcolors))+1, ',')#;">
0
 
LVL 11

Expert Comment

by:hart
ID: 9693239
also i hope u removed the <cfoutput query="timesprices" group="Eventname">
and made it just <cfoutput query="timesprices">

because the query will be returning records that are already grouped..

don't check the colors for now. just see wether the otput of the query is proper o not..

Regards
Hart

0
 

Author Comment

by:phillystyle123
ID: 9693300
i did remove group=Eventname.  this is what i'm currently using (for this page: http://redcatweb2.org/calendar/timesprices2.cfm)

<cfquery name="timesprices" datasource="#dsn#">
select  Eventname, CalendarDate, Calendar_ID, StartTime, STAMPM, PriceGA, PriceEA, PriceSt  from calendar group by Eventname ORDER BY CalendarDate ASC
</cfquery>

and then

<cfoutput query="timesprices">
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Accepted Solution

by:
hart earned 125 total points
ID: 9693341
ok is this what u want...
u only want to show the event name once and its corresponding dates till it gets over.

then if the next event name has the same date as the above one then the tr color should be same..

i hope i have understood ur requirement correctly till now...
so the problem for u is just the display part, right...

ok lets try this out
<CFQUERY name="timesprices" datasource="#dsn#">
      select Eventname, CalendarDate, Calendar_ID, StartTime, STAMPM, PriceGA, PriceEA, PriceSt from calendar
      ORDER BY CalendarDate ASC
</CFQUERY>
<CFSET sEventName = ''>
<CFSET dCalendarDate = ''>
<CFSET Ctr = 0>
<CFOUTPUT query="timesprices">
      <CFIF CompareNoCase(sEventName,Eventname) NEQ 0>
            <CFSET sEventName = Eventname>
            <CFSET dCalendarDate = CalendarDate>
            <CFIF dCalendarDate neq CalendarDate>
                  <CFSET Ctr = IncrementValue(Ctr)>
            </CFIF>
            <CFSET ShowFlag = 'Y'>
      <CFELSE>
            <CFSET ShowFlag = 'N'>
            <CFSET dCalendarDate = CalendarDate>
      </CFIF>                        
      <TR style="background-color:#listgetat(allcolors,(Ctr mod listlen(allcolors))+1,',')#;">
            <TD align="left" valign="top" width="160">
                  <CFIF CompareNoCase(ShowFlag,'Y') EQ 0>
                        <SPAN class="frontcopy"><CFSET myUrl = urlencodedformat(#Eventname#)><A href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</A></SPAN>
                  <CFELSE>
                        &nbsp;
                  </CFIF>
            </TD>
            <TD align="left" valign="top" width="10" >&nbsp;</TD>
            <TD width="70" align="left" valign="top" ><SPAN class="frontcopy">#dateformat(timesprices.CalendarDate,'m.d.yyyy')#<BR>.....
</CFOUTPUT>

Regards
Hart




0
 

Author Comment

by:phillystyle123
ID: 9693401
i think we're getting somewhere: check out the result of your code:

http://redcatweb2.org/calendar/timesprices2.cfm

just to be sure this is what it looks like in the page:

<CFQUERY name="timesprices" datasource="#dsn#">
     select Eventname, CalendarDate, Calendar_ID, StartTime, STAMPM, PriceGA, PriceEA, PriceSt from calendar
     ORDER BY CalendarDate ASC
</CFQUERY>

and then:

<CFSET sEventName = ''>
<CFSET dCalendarDate = ''>
<CFSET Ctr = 0>
<CFOUTPUT query="timesprices">

                        <CFIF CompareNoCase(sEventName,Eventname) NEQ 0>
          <CFSET sEventName = Eventname>
          <CFSET dCalendarDate = CalendarDate>
          <CFIF dCalendarDate neq CalendarDate>
               <CFSET Ctr = IncrementValue(Ctr)>
          </CFIF>
          <CFSET ShowFlag = 'Y'>
     <CFELSE>
          <CFSET ShowFlag = 'N'>
          <CFSET dCalendarDate = CalendarDate>
     </CFIF>    
       <cfset allcolors="##cccccc,##d4d590,##c2bb9c,##e4eae9,##c4ae5a">              
     <TR style="background-color:#listgetat(allcolors,(Ctr mod listlen(allcolors))+1,',')#;">



          <td align="left" valign="top" width="160" ><CFIF CompareNoCase(ShowFlag,'Y') EQ 0><span class="frontcopy">
              <cfset myUrl = urlencodedformat(#Eventname#)>
                            <A
                  href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</a></span><CFELSE>
                    &nbsp;
               </CFIF>
</td>
0
 
LVL 11

Assisted Solution

by:hart
hart earned 125 total points
ID: 9693537
ok now the only problem is the color... right :-)

just rerrange this part and see

         <CFSET dCalendarDate = CalendarDate>
          <CFIF dCalendarDate neq CalendarDate>
               <CFSET Ctr = IncrementValue(Ctr)>
          </CFIF>

to
          <CFIF dCalendarDate neq CalendarDate>
               <CFSET Ctr = IncrementValue(Ctr)>
          </CFIF>
          <CFSET dCalendarDate = CalendarDate>


Regards
Hart




0
 
LVL 11

Assisted Solution

by:hart
hart earned 125 total points
ID: 9693547
and also try this
background-color:#listgetat(allcolors,RandRange(1,ListLen(allcolors)))#;">


Regards
Hart
0
 
LVL 11

Assisted Solution

by:hart
hart earned 125 total points
ID: 9693578
sorry forget my last tr post
and try this

         <CFIF dCalendarDate neq CalendarDate>
               <CFSET Ctr = IncrementValue(Ctr)>
                <cfif Ctr gt 5>
                      <CFSET Ctr = 1>
                </cfif>
          </CFIF>
          <CFSET dCalendarDate = CalendarDate>

and
background-color:#listgetat(allcolors,Ctr)#;">


Regards
Hart
0
 

Author Comment

by:phillystyle123
ID: 9694000
hart - just about there!!!!

check out the latest:
http://www.redcatweb2.org/calendar/timesprices2.cfm

notice how "new recurr" 11.18.2003 is knocked out of it's <tr> by "recur test" 11.17.2003.

here's how i'm using your code:

<CFSET sEventName = ''>
<CFSET dCalendarDate = ''>
<CFSET Ctr = 0>
<CFOUTPUT query="timesprices">

                        <CFIF CompareNoCase(sEventName,Eventname) NEQ 0>
          <CFSET sEventName = Eventname>
          <CFIF dCalendarDate neq CalendarDate>
               <CFSET Ctr = IncrementValue(Ctr)>
                <cfif Ctr gt 5>
                      <CFSET Ctr = 1>
                </cfif>
          </CFIF>
          <CFSET dCalendarDate = CalendarDate>


          <CFSET ShowFlag = 'Y'>
     <CFELSE>
          <CFSET ShowFlag = 'N'>
          <CFSET dCalendarDate = CalendarDate>
     </CFIF>    
       <cfset allcolors="##cccccc,##d4d590,##c2bb9c,##e4eae9,##c4ae5a">              
     <TR style="background-color:#listgetat(allcolors,Ctr)#;">
0
 
LVL 11

Expert Comment

by:hart
ID: 9699141
the problem is i can't order by event name, then the dates will have a problem...

see in this scenario ur new recurr is on 18th but what if it was on 21st or say some other later date.
then it cannot be shown with the earlier new recurr as u already ordering it by date..

hope u understand what i mean.
my solution will always get events with same date in one row.

but i don' think it will ever get a same event on a very later date in the same row color..

hope u understand ur requirement will make me go bonkers then :-)


Regards
Hart

0
 

Author Comment

by:phillystyle123
ID: 9699993
i totally understand and i think the client will live with it because i think there will very rarely be 2 events on one date so, your solution totally works.

thanks again for everything hart - you've totally hooked me up and i truly appreciate it.

:-)
craig
0
 
LVL 11

Expert Comment

by:hart
ID: 9700033
u r welcome :-)

and may be my username might change to harish or something like that, so don't get
surprised if u see comments from harish instead of hart in your further queries

Regards
Hart [Harish Nair]
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now