Cold Fusion Loop problem

Question

Lightninghawk 40 Posting Whiz in Training

19 Years Ago

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/dcarroll/monday_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. :)

coldfusion

2 Contributors
8 Replies
323 Views
1 Day Discussion Span
Latest Post 19 Years Ago Latest Post by e3computer

e3computer 1 Newbie Poster

19 Years Ago

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.

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

e3computer 1 Newbie Poster

19 Years Ago

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.

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

Lightninghawk commented: Awesome Person, He was very patient everytime I messed up the code he just wrote, and he still helped me get it finished +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Awesome Person, He was very patient everytime I messed up the code he just wrote, and he still helped me get it finished

Lightninghawk 40 Posting Whiz in Training · Answer 1 · 2005-11-08T10:44:59+00:00

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

Lightninghawk 40 Posting Whiz in Training · Answer 2 · 2005-11-09T00:23:11+00:00

It's not working unfortunately.

Here is the outcome
http://students.eastcentraltech.edu/dcarroll/monday_report.cfm

Lightninghawk 40 Posting Whiz in Training · Answer 3 · 2005-11-09T01:53:14+00:00

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/dcarroll/monday_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/dcarroll/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>

Lightninghawk 40 Posting Whiz in Training · Answer 4 · 2005-11-09T01:57:19+00:00

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.

e3computer 1 Newbie Poster · Answer 5 · 2005-11-09T02:10:31+00:00

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.

e3computer 1 Newbie Poster · Answer 6 · 2005-11-09T02:12:10+00:00

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!