I'm trying to output some products on my webpage. Is there anyway to have a header that has the company and then list the products under that company header and so on. There would be mutiple companies with mutiple products under each company. Is there a way to do all of this with one query?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Grouping results 1
- Thread starter fiber0pti
- Start date
Usually this can be done with Joins.
I don't know if you've tried them or what you know about them but you can join tables ON a particular field and thus gather more information with one query.
They often look something like this:
<CFQUERY name="TEST" datasource="#DSN#">
Select table1.*, table2.*
From table1 INNER JOIN table2
ON table1.id = table2.ref_id
</CFQUERY>
so if your product has a field that holds the ID of the company it comes from, Inner Join on that field and you'll have it. IF this isn't working for you , post some code and let us know what the fields are and we'll try to help more.
I don't know if you've tried them or what you know about them but you can join tables ON a particular field and thus gather more information with one query.
They often look something like this:
<CFQUERY name="TEST" datasource="#DSN#">
Select table1.*, table2.*
From table1 INNER JOIN table2
ON table1.id = table2.ref_id
</CFQUERY>
so if your product has a field that holds the ID of the company it comes from, Inner Join on that field and you'll have it. IF this isn't working for you , post some code and let us know what the fields are and we'll try to help more.
-
1
- #3
Hey fiber,
You want to use the group attribute with <cfoutput> but you have to make sure to include the "grouped" field in your sql "order by clause". Here's an example:
<cfquery name="q1" ...>
select companyName,productName from table1
order by companyName asc, productName asc
</cfquery>
<cfoutput query="q1" group="companyName">
#companyName#
<hr>
<cfoutput>
#productName#<br>
</cfoutput>
<hr>
</cfoutput>
Everything within the inner <cfoutput> tags is repeated for each record and everything outside of them is repeated for every new value in the field set in the "group" attribute.
Let me know if you have any trouble with it,
GJ
You want to use the group attribute with <cfoutput> but you have to make sure to include the "grouped" field in your sql "order by clause". Here's an example:
<cfquery name="q1" ...>
select companyName,productName from table1
order by companyName asc, productName asc
</cfquery>
<cfoutput query="q1" group="companyName">
#companyName#
<hr>
<cfoutput>
#productName#<br>
</cfoutput>
<hr>
</cfoutput>
Everything within the inner <cfoutput> tags is repeated for each record and everything outside of them is repeated for every new value in the field set in the "group" attribute.
Let me know if you have any trouble with it,
GJ
- Thread starter
- #6
- Status