RSS Forums RSS
Please support our ColdFusion advertiser: Programming Forums
Views: 5064 | Replies: 8
Reply
•
•
•
•
•
•
Join Date: Jun 2005
Location: Douglas, Ga. USA
Posts: 226
Reputation: Lightninghawk is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Cold Fusion Loop problem

  #1  
Nov 7th, 2005
The output that I'm looking for is:

---Site
------Session
---------Class ........... students in class
---------Class ........... students in class
------Session
---------Class ........... students in class

---Site
ect ect....

~~~~~~~~~~~
The current outcome :

---Site
---------Class ........... students in class

or sometimes

---Site
------Session
---Site...
~~~~~~~~~~~~
It's almost like it has a mind of it's own.

The Query code is:
<div align="center">
<cfquery name="get_sites" datasource="books">
select distinct site
	from enr_report
	order by site
</cfquery>

<table border="0" width="81%" bgcolor="#000000" cellspacing="1" cellpadding="0" col="4">
<cfloop query="get_sites">
  <tr>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="red"> &nbsp <cfoutput>#get_sites.site#</cfoutput></font></p></td>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp New</font></p></td>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp Returning</font></p></td>
  </tr>
<cfquery name="get_session" datasource="books">
select distinct sess
	from enr_report
	where site='#get_sites.site#'
	order by sess
</cfquery>
<cfloop query="get_session">

  <tr>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="yellow"> &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <cfoutput>#get_session.sess#</cfoutput></font></p></td>
<td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="#FFFFFF"></font></p></td>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="#FFFFFF"></font></p></td>
  </tr>
<cfquery name="get_count" datasource="books">
select count(a.id) as number_students, a.type, a.majr
	from enr_report a, enr_report b
	where a.id = b.id
	and b.sess='#get_session.sess#'
	and b.site='#get_sites.site#'
	group by a.type, a.majr
</cfquery>
<cfloop query="get_count">
  <tr>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="green"> &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <cfoutput>#majr#</cfoutput></font></p></td>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp <cfoutput>#type#</cfoutput></font></p></td>
    <td bgcolor="#003194"><b>
<p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp <cfoutput>#number_students#</cfoutput></font></p></td>
  </tr>
</cfloop>
</cfloop>
</cfloop>


To view the results please view this page:
http://students.eastcentraltech.edu/...day_report.cfm

My instructor was having problems with it. So he asked me to search around and try to find the answer.

Any help here will be appreciated.
I know the format of the page is strange, but we did that so we could get it seperated a little bit until we could get it fixed.

Thanks in advance.
+_-¤ ŦĦễ £ﺄiĢĦŧňĨňĢĦǻщk ¤-_+
South Georgia Computers - Home and Office IT Solutions - Owner/Tech
Need the BEST software for Virus/Spyware Removal? Download - AVG Free -

Computer Problems? Forget the GeeK Squad use something better: DaniWeb
AddThis Social Bookmark Button
Reply With Quote  
•
•
Join Date: Nov 2005
Location: Massachusetts
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

  #2  
Nov 7th, 2005
Without your database I am not certain of exactly what all the data you have looks like. But I think you may be able to simplify the output using one query and using the GROUP attibute on the CFOUPUT tags as seen in the attachment.

First is the query, then the simple output you metioned. Then using the same logic the full code you included has been modified to use the menioned solution. Best of luck.

[HTML]<cfquery name="get_sites" datasource="books">
SELECT DISTINCT *
FROM enr_report
ORDER BY site
, sess
, type
, majr
</cfquery>

<!--- the simple structure you said you were aiming for --->

<!--- loop through the data, making the first itteration each time the site is unique --->
<cfoutput query="get_sites" group="site">
<br><strong>---#site#</strong>

<!--- loop through the subset of data while the site is the same --->
<cfoutput group="sess">
<br>------#sess#

<!--- while session stays the same the following code repeats --->
<cfoutput>
<br>--------#majr# #type# ...... #number_students#
</cfoutput>
<!--- /while session stays the same the following code repeats --->

</cfoutput>
<!--- /loop through the subset of data while the site is the same --->

</cfoutput>
<!--- /loop through the data, making the first itteration each time the site is unique --->

<!--- /the simple structure you said you were aiming for --->



<!--- the structure applied to your table formatting --->

<!--- using font tags is no way to go through life, tags removed, style added --->
<style>
.a3 {
font-family:Arial;
font-size:12pt;
text-align:left;
background-color:#003194;
}
.red {
color:red;
}
.yellow {
color:yellow;
}
.green {
color:green;
}
.white {
color:white;
}
.bold {
font-weight:bold;
}
</style>
<!--- /using font tags is no way to go through life, tags removed, style added --->

<table border="0" width="81%" bgcolor="##000000" cellspacing="1" cellpadding="0" col="4">
<cfoutput query="get_sites" group="site">
<tr>
<td class="a3 bold red">&nbsp <cfoutput>#get_sites.site#</cfoutput></td>
<td class="a3 bold white">&nbsp New</td>
<td class="a3 bold white">&nbsp Returning</td>
</tr>
<!--- loop through the subset of data while the site is the same --->
<cfoutput group="sess">
<tr>
<td class="a3 bold yellow">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <cfoutput>#get_sites.sess#</cfoutput></td>
<td class="a3">&nbsp;</td>
<td class="a3">&nbsp;</td>
</tr>

<!--- while session stays the same the following code repeats --->
<cfoutput>
<tr>
<td class="a3 bold green">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp #majr#</td>
<td class="a3 bold white">&nbsp #type#</td>
<td class="a3 bold white">&nbsp #number_students#</td>
</tr>
</cfoutput>
<!--- /while session stays the same the following code repeats --->

</cfoutput>
<!--- /loop through the subset of data while the site is the same --->

</cfoutput>
</table>
<!--- /the structure applied to your table formatting --->[/HTML]
Reply With Quote  
•
•
Join Date: Jun 2005
Location: Douglas, Ga. USA
Posts: 226
Reputation: Lightninghawk is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

  #3  
Nov 8th, 2005
Thank You, e3computer, I really appreciate it. I am going to implement it as soon as possible. I'll post another reply with the result
+_-¤ ŦĦễ £ﺄiĢĦŧňĨňĢĦǻщk ¤-_+
South Georgia Computers - Home and Office IT Solutions - Owner/Tech
Need the BEST software for Virus/Spyware Removal? Download - AVG Free -

Computer Problems? Forget the GeeK Squad use something better: DaniWeb
Reply With Quote  
•
•
Join Date: Jun 2005
Location: Douglas, Ga. USA
Posts: 226
Reputation: Lightninghawk is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

  #4  
Nov 8th, 2005
It's not working unfortunately.

Here is the outcome
http://students.eastcentraltech.edu/...day_report.cfm
+_-¤ ŦĦễ £ﺄiĢĦŧňĨňĢĦǻщk ¤-_+
South Georgia Computers - Home and Office IT Solutions - Owner/Tech
Need the BEST software for Virus/Spyware Removal? Download - AVG Free -

Computer Problems? Forget the GeeK Squad use something better: DaniWeb
Reply With Quote  
•
•
Join Date: Nov 2005
Location: Massachusetts
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

  #5  
Nov 8th, 2005
I see now, the number_students is a calculation, not a column. Here is the query modified to include the calculation. Hopefully this works for you. The 'AS' clause will not work for all database types. You may need to fit the SQL for your database type.

[HTML]<!--- get the unique data to display,
, subselect the count of duplicate rows
which will be used to determine enrollment
(I think) --->
<cfquery name="get_sites" datasource="books">
SELECT DISTINCT a.site
, a.sess
, a.type
, a.majr
, ( SELECT count(id) AS number_students
FROM enr_report b
GROUP BY b.site
, b.sess
, b.type
, b.majr
HAVING b.site = a.site
AND b.sess = a.sess
AND b.type = a.type
AND b.majr = a.majr
) AS number_students
FROM enr_report a
ORDER BY a.site
, a.sess
, a.type
, a.majr
</cfquery>
<!--- /get the unique data to display,
, subselect the count of duplicate rows
which will be used to determine enrollment
(I think) --->[/HTML]
Reply With Quote  
•
•
Join Date: Jun 2005
Location: Douglas, Ga. USA
Posts: 226
Reputation: Lightninghawk is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

  #6  
Nov 8th, 2005
Oh man that rocks. It's only got a couple of flaws. Which I'm sure I caused.
I've probably way over edited the code trying to figure out what I did wrong, and how to fix it.
I really appreciate your help here.
Think you could have a look at this and give it one more go?

http://students.eastcentraltech.edu/...day_report.cfm

There are a few things repeating. The sites and sessions for the most part. But then down further something else caught my eye. I want it to show the returning and new students on the same line, in two other columns, as the class.

It currently shows all of the classes with new students then runs back through and shows the same class with returning students.

To show you what I mean here is the Text output. No fancy attempts to format.
http://students.eastcentraltech.edu/...mondaytext.cfm

Thanks again for all of this help you are giving me.

The code as it stands ::

<!---   get the unique data to display,
               , subselect the count of duplicate rows
                 which will be used to determine enrollment
                 (I think) --->
<cfquery name="get_sites" datasource="books">
       SELECT DISTINCT a.site
                                               , a.sess
                                               , a.type
                                               , a.majr
               , ( SELECT count(id) AS number_students
                       FROM    enr_report      b
                       GROUP BY        b.site
                                               , b.sess
                                               , b.type
                                               , b.majr
                       HAVING  b.site = a.site
                                               AND b.sess = a.sess
                                               AND b.type = a.type
                                               AND b.majr = a.majr
                       ) AS number_students
       FROM    enr_report      a
       ORDER BY        a.site
                               , a.sess
                               , a.type
                               , a.majr
</cfquery>
<!---   /get the unique data to display,
               , subselect the count of duplicate rows
                 which will be used to determine enrollment
                 (I think) --->

 


<!---   the structure applied to your table formatting  --->

<!---   using font tags is no way to go through life, tags removed, style added --->
<style>
       .a3 {
               font-family:Arial;
               font-size:12pt;
               text-align:left;
               background-color:#003194;
       }
       .red {
               color:red;
       }
       .yellow {
               color:yellow;
       }
       .green {
               color:green;
       }
       .white {
               color:white;
       }
       .bold {
               font-weight:bold;
       }
</style>
<!---   /using font tags is no way to go through life, tags removed, style added        --->

<table border="0" width="81%" bgcolor="##000000" cellspacing="1" cellpadding="0" col="4">
<cfoutput query="get_sites" group="site">
 <tr>
   <td class="a3 bold red">&nbsp <cfoutput>#get_sites.site#</cfoutput></td>
   <td class="a3 bold white">&nbsp New</td>
   <td class="a3 bold white">&nbsp Returning</td>
 </tr>
       <!--- loop through the subset of data while the site is the same        --->
       <cfoutput group="sess">
          <tr>
           <td class="a3 bold yellow">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <cfoutput>#get_sites.sess#</cfoutput></td>
               <td class="a3">&nbsp;</td>
           <td class="a3">&nbsp;</td>
         </tr>

               <!---   while session stays the same the following code repeats --->
               <cfoutput>
                       <tr>
                       <td class="a3 bold green">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp #majr#</td>
                       <td class="a3 bold white">&nbsp #type#</td>
                       <td class="a3 bold white">&nbsp #number_students#</td>
                       </tr>
               </cfoutput>
               <!---   /while session stays the same the following code repeats        --->

       </cfoutput>
       <!--- /loop through the subset of data while the site is the same       --->

</cfoutput>
</table>
<!---   /the structure applied to your table formatting --->
</table>
+_-¤ ŦĦễ £ﺄiĢĦŧňĨňĢĦǻщk ¤-_+
South Georgia Computers - Home and Office IT Solutions - Owner/Tech
Need the BEST software for Virus/Spyware Removal? Download - AVG Free -

Computer Problems? Forget the GeeK Squad use something better: DaniWeb
Reply With Quote  
•
•
Join Date: Jun 2005
Location: Douglas, Ga. USA
Posts: 226
Reputation: Lightninghawk is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

  #7  
Nov 8th, 2005
Just checked out your website too. Awesome

Oh and It just occurred to me to tell you that the database the file is pulling from is an MS Access database.
+_-¤ ŦĦễ £ﺄiĢĦŧňĨňĢĦǻщk ¤-_+
South Georgia Computers - Home and Office IT Solutions - Owner/Tech
Need the BEST software for Virus/Spyware Removal? Download - AVG Free -

Computer Problems? Forget the GeeK Squad use something better: DaniWeb
Reply With Quote  
•
•
Join Date: Nov 2005
Location: Massachusetts
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

  #8  
Nov 8th, 2005
Would you export the data as xml or txt for me? There is something about your data that I am sure will be obvious by seeing the raw data. Either that or make a page that does a CFDUMP of the data so that I can see it that way. I am guessing the 'type' column is being used for new vs returning.?. If that is the case a few mods are in order. Luckily ColdFusion is quick and easy to modify.
Last edited by e3computer : Nov 8th, 2005 at 4:11 pm. Reason: syntax
Reply With Quote  
•
•
Join Date: Nov 2005
Location: Massachusetts
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

  #9  
Nov 8th, 2005
•
•
•
•
Originally Posted by Lightninghawk
Just checked out your website too. Awesome

Oh and It just occurred to me to tell you that the database the file is pulling from is an MS Access database.

Thank you! I appreciate that!
Reply With Quote  
Reply

Quick Reply to Thread
•
•
•
•

Only community members can participate in forum threads. You must register or log in to contribute.

Register
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

•
•
•
•
Thread Tools Display Modes
Hybrid Mode Hybrid Mode
Forums | Blogs | Tutorials | Code Snippets | Whitepapers | RSS Feeds | Advertising
All times are GMT -4. The time now is 10:40 am.
Newsletter Archive - Sitemap - Privacy Statement - Acceptable Use Policy - Contact Us
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC