Help with table

[ivalum21]

I have a table that has two columns, COMPANY NAME and CITY. The company can have locations in many different cities. I want to display the company name once, and the under CITY, have the cities listed...seperated by commas or something.

How can I do this??

 

[keneck]

You can group the output from your query by Company Name.

<cfquery name="getList" datasource="myDB">
SELECT CompanyName, City
FROM myTable
ORDER BY CompanyName
</cfquery>


<cfoutput query="getList" group="CompanyName">
<table>
<tr><td>#getList.CompanyName#</td></tr>
<cfoutput>
<tr><td>#City#</td></tr>
</cfoutput>
</table>
</cfoutput>


This will create a single entry for each company name and separate rows for each city associated with that company. You can arrange the output differently (not in individual table rows), if you want a comma delimited list. Just be sure you have the nested <cfoutput> inside the output that groups the query results.

 

[ivalum21]

That won't work, I need my table to be side by side, this displays the cities underneath the company name.

Here is my code for my table, this displays it the way I want it, it just doesn't group the company name together and display numberous cities for one company name:

<!--- Build table for results --->
<table align="center"
cellspacing="2"
cellpadding="2"
border="8"
frame="border">
<th bgcolor="#FF8000">Company Name</th>
<th bgcolor="#FF8000">City</th>

<cfloop query="GetResults">
<tr><cfif GetResults.CurrentRow MOD 2 EQ 0>
<td bgcolor="#CCCCCC"></td>
<cfelse>
<td bgcolor="white"></td>
</cfif>
</tr>

<cfoutput>
<td align="center">#cl_name#</td>
<td align="center">#cl_city#</td>
</cfoutput>
</cfloop>
</table>

 

[webmigit]

<table align="center"
 cellspacing="2"
 cellpadding="2"
 border="8"
 frame="border">
 <th bgcolor="#FF8000">Company Name</th>
 <th bgcolor="#FF8000">City</th>
 
    <cfoutput query="GetResults" group="cl_name">
    <tr><cfif GetResults.CurrentRow MOD 2 EQ 0>
            <td bgcolor="#CCCCCC"></td>
        <cfelse>
            <td bgcolor="white"></td>
        </cfif>
    </tr>

    <cfset cit="">
        <td align="center">#cl_name#</td>
        <cfoutput group="cl_city">
          <cfset cit=listappend(cit,cl_city)></cfoutput>
        <td align="center">(#cit#)</td>
    </cfoutput>
</table>

You do need to have an order by companyname city clause in your query.. this uses grouping but in a slightly different way.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[ivalum21]

That works...thank you very much. But one last question...

How do I format it so it only displays, let's say, 3 cities per line. Now it'll display 15 or so cities on one line, which makes my CITY column very wide.

 

[webmigit]

<cfoutput group="cl_city" [b]maxrows="3"[/b]>
          <cfset cit=listappend(cit,cl_city)></cfoutput>

Should work but if it does not.. this will work..

<cfoutput group="cl_city">
          <cfif listlen(cit) lt 3><cfset cit=listappend(cit,cl_city)></cfif></cfoutput>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[ivalum21]

Both of those displays 3 cities, but it cuts off the rest of the cities, it doesn't continue display them on the next line(s).

 

[TruthInSatire]

try something like this:

<td align="center">(#cit#<cfif listlen(cit) mod 3 eq 0><br></cfif>)</td>



Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[ivalum21]

That still didn't work. I'm still getting a very wide CITY column. Here is the code I have so far:

<table align="center"
 cellspacing="2"
 cellpadding="2"
 border="8"
 frame="border">
 
 <th bgcolor="#FF8000">Company Name</th>
 <th bgcolor="#FF8000">City</th>
 
    <cfoutput query="GetResults" group="cl_name">
    <tr><cfif GetResults.CurrentRow MOD 2 EQ 0>
            <td bgcolor="ff9900"></td>
        <cfelse>
            <td bgcolor="white"></td>
        </cfif>
    </tr>

    <cfset display="">
        <td align="center">#cl_name#</td>
        <cfoutput group="cl_city">
          <cfset display = listappend(display,cl_city)>
		</cfoutput>
        <td align="center">#display#
			<cfif listlen(display) mod 3 eq 0><br>
			</cfif>
		</td>
    </cfoutput>
</table>

 

[webmigit]

I misunderstood.. try this..

<cfoutput group="cl_city" maxrows="3">
  <cfif listlen(cit) neq 0 and listlen(cit) mod 3 eq 0>
    <cfset cit=listappend(cit,cl_city & "<br>")>
  <cfelse><cfset cit=listappend(cit,cl_city)></cfif>
</cfoutput>
<cfset cit=replace(cit,"<br>,","<br>","ALL")>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[ivalum21]

For some reason, I just cannot get it to work. I get an error message saying that it's cannot convert the city into a number???

Here is my entire code....what am I doing wrong?

<!--- Query for displaying the institutions, city, and state. --->
<cfquery name="GetResults" datasource="IMBA">
	SELECT cl_name, cl_city, cl_web_url
	FROM Corp_Location
	GROUP BY cl_name, cl_city, cl_web_url
	ORDER BY cl_name, cl_city
</cfquery>

<html>
<head>
	<title>IMBA</title>
</head>

<body>

<!--- Build table for results --->
<table align="center"
 cellspacing="2"
 cellpadding="2"
 border="8"
 frame="border">
 
 <th bgcolor="#FF8000">Company Name</th>
 <th bgcolor="#FF8000">City</th>
 
    <cfoutput query="GetResults" group="cl_name">
    <tr><cfif GetResults.CurrentRow MOD 2 EQ 0>
            <td bgcolor="ff9900"></td>
        <cfelse>
            <td bgcolor="white"></td>
        </cfif>
    </tr>

    <cfset display="">
        <td align="center">#cl_name#</td>
        <cfoutput group="cl_city" maxrows="3">
			<cfif listlen(display) neq 0 AND listlen(display mod 3 eq 0)>
        		<cfset display = listappend(display,cl_city & "<br>")>
			<cfelse>
				<cfset display = listappend(display,cl_city)>
			</cfif>
		</cfoutput>
		<cfset display = replace(display, "<br>,","<br>","ALL")>
     </cfoutput>
</table>

</body>
</html>

 

[webmigit]

<cfif listlen(display) neq 0 AND listlen(display) mod 3 eq 0>

try that.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[TruthInSatire]

see how this works
<!--- Query for displaying the institutions, city, and state. --->
<cfquery name="GetResults" datasource="IMBA">
SELECT cl_name, cl_city, cl_web_url
FROM Corp_Location
ORDER BY cl_name, cl_city
</cfquery>

<html>
<head>
<title>IMBA</title>
</head>

<body>

<!--- Build table for results --->
<table align="center"
cellspacing="2"
cellpadding="2"
border="8"
frame="border">

<th bgcolor="#FF8000">Company Name</th>
<th bgcolor="#FF8000">City</th>

<cfoutput query="GetResults" group="cl_name">
<tr>
<td bgcolor="<cfif GetResults.CurrentRow MOD 2 EQ 0>##ff9900<cfelse>##FFFFFF</cfif>">#cl_name#</td>
<td align="center">
<cfset cityNum = 0>
<cfoutput>
<cfset cityNum = cityNum+1>
#cl_city#,
<cfif cityNum Mod 3 eq 0><br></cfif>
</cfoutput>
</tr></cfoutput>

</table>

</body>
</html>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[ivalum21]

Now it doesn't display any cities in the CITY column, all you get are the company names.

 

[TruthInSatire]

to who's response.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[ivalum21]

ha...that was directed to webmigit's response.

But bombboy...you're code works, with a couple of exceptions.

When there is only one city, how can I NOT display a comma after the city??

And what do you think would be causing the alternating colors to sometimes "get off track", like, it'll put the background color on two consecutive rows??

By the way...I really appreciate both of you helping me with this.

 

[TruthInSatire]

I just made a table with the required fields and tested what i posted. It works. the view source is:

<html>
<head>
    <title>IMBA</title>
</head>

<body>


<table align="center"
 cellspacing="2"
 cellpadding="2"
 border="8"
 frame="border">
 
 <th bgcolor="#FF8000">Company Name</th>
 <th bgcolor="#FF8000">City</th>
 
    
    <tr>
            <td bgcolor="#FFFFFF">a</td>
        <td align="center">
	   1,
	    2,
	    3,
	    <br> 
     </tr>
    <tr>
            <td bgcolor="#ff9900">b</td>
        <td align="center">
	   4,
	    5,
	    6,
	    <br> 7,
	    8,
	    
     </tr>
	
</table>

</body>
</html>

the only problem is the trailing "," after the last value

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[ivalum21]

Yeah that's what I get too...that trailing comma after the last value. Any way to prevent that from happening??

 

[TruthInSatire]

the alternating colors aren't working because of the nested <cfoutput> what's happening is inside the second cfoutput the current row is actually incromenting. so if you have 3 cities the current row is three higher than it was when it left that nested cfoutput. it just so happens the current row may or may not be "mod 2 eq 0"
the trailing "," can be done with a list...
lets see what we can do...

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[TruthInSatire]

got it:

<!--- Query for displaying the institutions, city, and state. --->
<cfquery name="GetResults" datasource="#application.dsn#">
    SELECT cl_name, cl_city, cl_web_url
    FROM Corp_Location
    ORDER BY cl_name, cl_city
</cfquery>

<html>
<head>
    <title>IMBA</title>
</head>

<body>

<!--- Build table for results --->
<table align="center"
 cellspacing="2"
 cellpadding="2"
 border="8"
 frame="border">
 
 <th bgcolor="#FF8000">Company Name</th>
 <th bgcolor="#FF8000">City</th>
 <cfset colorRow = 1>
    <cfoutput query="GetResults" group="cl_name">
    
    <tr>
            <td bgcolor="<cfif colorRow MOD 2 EQ 0>##ff9900<cfelse>##FFFFFF</cfif>">#cl_name#</td>
        <td align="center" bgcolor="<cfif colorRow MOD 2 EQ 0>##ff9900<cfelse>##FFFFFF</cfif>">
	   <cfset cityNum = 0>
	   <cfset cityList = "">
	   <cfoutput>
		   <cfset cityList = cityList & cl_city & ",">
	    </cfoutput>
	    <cfset loopLen = listLen(#cityList#)>
	    <cfloop from = "1" to="#loopLen#" index = "thisCity">
	    #listGetAt(cityList, thisCity)#
	    <cfif thisCity neq listLen(#cityList#)>,</cfif>
		    <cfif thisCity Mod 3 eq 0><br></cfif>
        </cfloop>
     </tr>
	<cfset colorRow = colorRow+1>
	</cfoutput>
	
</table>

</body>
</html>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)