ColdFusion RSS ColdFusion RSS

Cold Fusion Loop problem

Reply

•
•
Join Date: Jun 2005
Posts: 238
Reputation: Lightninghawk is an unknown quantity at this point 
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Cold Fusion Loop problem

 
0
  #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:
ColdFusion Syntax (Toggle Plain Text) 
  1. <div align="center">
  2. <cfquery name="get_sites" datasource="books">
  3. select distinct site
  4. 	from enr_report
  5. 	order by site
  6. </cfquery>
  7.  
  8. <table border="0" width="81%" bgcolor="#000000" cellspacing="1" cellpadding="0" col="4">
  9. <cfloop query="get_sites">
  10.   <tr>
  11.     <td bgcolor="#003194"><b>
  12. <p align="left"><font face="Arial" size="3" color="red"> &nbsp <cfoutput>#get_sites.site#</cfoutput></font></p></td>
  13.     <td bgcolor="#003194"><b>
  14. <p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp New</font></p></td>
  15.     <td bgcolor="#003194"><b>
  16. <p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp Returning</font></p></td>
  17.   </tr>
  18. <cfquery name="get_session" datasource="books">
  19. select distinct sess
  20. 	from enr_report
  21. 	where site='#get_sites.site#'
  22. 	order by sess
  23. </cfquery>
  24. <cfloop query="get_session">
  25.  
  26.   <tr>
  27.     <td bgcolor="#003194"><b>
  28. <p align="left"><font face="Arial" size="3" color="yellow"> &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <cfoutput>#get_session.sess#</cfoutput></font></p></td>
  29. <td bgcolor="#003194"><b>
  30. <p align="left"><font face="Arial" size="3" color="#FFFFFF"></font></p></td>
  31.     <td bgcolor="#003194"><b>
  32. <p align="left"><font face="Arial" size="3" color="#FFFFFF"></font></p></td>
  33.   </tr>
  34. <cfquery name="get_count" datasource="books">
  35. select count(a.id) as number_students, a.type, a.majr
  36. 	from enr_report a, enr_report b
  37. 	where a.id = b.id
  38. 	and b.sess='#get_session.sess#'
  39. 	and b.site='#get_sites.site#'
  40. 	group by a.type, a.majr
  41. </cfquery>
  42. <cfloop query="get_count">
  43.   <tr>
  44.     <td bgcolor="#003194"><b>
  45. <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>
  46.     <td bgcolor="#003194"><b>
  47. <p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp <cfoutput>#type#</cfoutput></font></p></td>
  48.     <td bgcolor="#003194"><b>
  49. <p align="left"><font face="Arial" size="3" color="#FFFFFF"> &nbsp <cfoutput>#number_students#</cfoutput></font></p></td>
  50.   </tr>
  51. </cfloop>
  52. </cfloop>
  53. </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
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2005
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

 
0
  #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 Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 238
Reputation: Lightninghawk is an unknown quantity at this point 
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

 
0
  #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 Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 238
Reputation: Lightninghawk is an unknown quantity at this point 
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

 
0
  #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 Quick reply to this message  
•
•
Join Date: Nov 2005
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

 
1
  #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 Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 238
Reputation: Lightninghawk is an unknown quantity at this point 
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

 
0
  #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 ::

ColdFusion Syntax (Toggle Plain Text) 
  1. <!---   get the unique data to display,
  2.                , subselect the count of duplicate rows
  3.                  which will be used to determine enrollment
  4.                  (I think) --->
  5. <cfquery name="get_sites" datasource="books">
  6.        SELECT DISTINCT a.site
  7.                                                , a.sess
  8.                                                , a.type
  9.                                                , a.majr
  10.                , ( SELECT count(id) AS number_students
  11.                        FROM    enr_report      b
  12.                        GROUP BY        b.site
  13.                                                , b.sess
  14.                                                , b.type
  15.                                                , b.majr
  16.                        HAVING  b.site = a.site
  17.                                                AND b.sess = a.sess
  18.                                                AND b.type = a.type
  19.                                                AND b.majr = a.majr
  20.                        ) AS number_students
  21.        FROM    enr_report      a
  22.        ORDER BY        a.site
  23.                                , a.sess
  24.                                , a.type
  25.                                , a.majr
  26. </cfquery>
  27. <!---   /get the unique data to display,
  28.                , subselect the count of duplicate rows
  29.                  which will be used to determine enrollment
  30.                  (I think) --->
  31.  
  32.  
  33.  
  34.  
  35. <!---   the structure applied to your table formatting  --->
  36.  
  37. <!---   using font tags is no way to go through life, tags removed, style added --->
  38. <style>
  39.        .a3 {
  40.                font-family:Arial;
  41.                font-size:12pt;
  42.                text-align:left;
  43.                background-color:#003194;
  44.        }
  45.        .red {
  46.                color:red;
  47.        }
  48.        .yellow {
  49.                color:yellow;
  50.        }
  51.        .green {
  52.                color:green;
  53.        }
  54.        .white {
  55.                color:white;
  56.        }
  57.        .bold {
  58.                font-weight:bold;
  59.        }
  60. </style>
  61. <!---   /using font tags is no way to go through life, tags removed, style added        --->
  62.  
  63. <table border="0" width="81%" bgcolor="# #000000" cellspacing="1" cellpadding="0" col="4">
  64. <cfoutput query="get_sites" group="site">
  65.  <tr>
  66.    <td class="a3 bold red">&nbsp <cfoutput>#get_sites.site#</cfoutput></td>
  67.    <td class="a3 bold white">&nbsp New</td>
  68.    <td class="a3 bold white">&nbsp Returning</td>
  69.  </tr>
  70.        <!--- loop through the subset of data while the site is the same        --->
  71.        <cfoutput group="sess">
  72.           <tr>
  73.            <td class="a3 bold yellow">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <cfoutput>#get_sites.sess#</cfoutput></td>
  74.                <td class="a3">&nbsp;</td>
  75.            <td class="a3">&nbsp;</td>
  76.          </tr>
  77.  
  78.                <!---   while session stays the same the following code repeats --->
  79.                <cfoutput>
  80.                        <tr>
  81.                        <td class="a3 bold green">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp #majr#</td>
  82.                        <td class="a3 bold white">&nbsp #type#</td>
  83.                        <td class="a3 bold white">&nbsp #number_students#</td>
  84.                        </tr>
  85.                </cfoutput>
  86.                <!---   /while session stays the same the following code repeats        --->
  87.  
  88.        </cfoutput>
  89.        <!--- /loop through the subset of data while the site is the same       --->
  90.  
  91. </cfoutput>
  92. </table>
  93. <!---   /the structure applied to your table formatting --->
  94. </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 Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 238
Reputation: Lightninghawk is an unknown quantity at this point 
Solved Threads: 6
Lightninghawk's Avatar
Lightninghawk Lightninghawk is offline Offline
Posting Whiz in Training

Re: Cold Fusion Loop problem

 
0
  #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 Quick reply to this message  
•
•
Join Date: Nov 2005
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

 
0
  #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 Quick reply to this message  
•
•
Join Date: Nov 2005
Posts: 9
Reputation: e3computer is an unknown quantity at this point 
Solved Threads: 0
e3computer e3computer is offline Offline
Newbie Poster

Re: Cold Fusion Loop problem

 
0
  #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 Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ColdFusion Forum
Thread Tools Search this Thread



adobe adobelivecyclees alfresco amazon cfquery checkbox coldfusion contentmanagement dreamweaver ebooks insert kindle multiple opensource openstandards pdf select sony sonyreader
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC