asked on

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

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" >
 <cfset myUrl = urlencodedformat(#Eventname#)>
<A
href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</a></td>
<td align="left" valign="top" width="10" ></td>
<td width="70" align="left" valign="top" >

<cfoutput>#dateformat(timesprices.CalendarDate,'m.d.yyyy')# </cfoutput>.....

jyokum

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" >
 <cfset myUrl = urlencodedformat(#Eventname#)>
 <A href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</a></td>
 <td align="left" valign="top" width="10" ></td>
 <td width="70" align="left" valign="top" >

 <cfoutput>#dateformat(timesprices.CalendarDate,'m.d.yyyy')# </cfoutput>.....

 </cfoutput>
</cfoutput>

anandkp

Do u mean to say that there shld be only one record for each of ur "EVENT" ???

hart

not so clear what u want buddy ???

Regards
Hart

phillystyle123

ASKER

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.

phillystyle123

ASKER

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")

hart

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

phillystyle123

ASKER

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, ',')#;">

hart

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

phillystyle123

ASKER

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">

ASKER CERTIFIED SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phillystyle123

ASKER

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>
 <cfset myUrl = urlencodedformat(#Eventname#)>
<A
href="http://www.redcatweb2.org/calendar/event.cfm?Eventname=#myUrl#">#timesprices.Eventname#</a><CFELSE>
 
</CFIF>
</td>

SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phillystyle123

ASKER

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)#;">

hart

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

phillystyle123

ASKER

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

hart

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]

order by date asc but keep &quot;like&quot; records in one &lt;tr&gt;

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