query/display results with a foreign key 1

[PTmon]

I have two tables:
Categories and Brochures. Each added brochure gets a foreign key from the catagory table. I want to output a list of brochures, with title of the catagory. I'm having trouble figuring out a good way to do that. What I have so far:

<cfquery name="qryListBrochures" datasource="application.dsn">
SELECT intBrochureIDPK,strTitle,dtDateModified,strCategoryFK
FROM tableBrochures
ORDER BY strCategoryFK, dtDateModified DESC
</cfquery>
<cfquery name="qryGetCategories" datasource="application.dsn">
SELECT intCategoryPK,strCategory,intSortOrder
FROM tableCategory
</cfquery>

And to display them:

<table border="0" cellspacing="2" cellpadding="2" align="center">
	<tr>
		<td class="bigblack" bgcolor="#c8e2d0" width="100">Date:</td>
		<td class="bigblack" bgcolor="#c8e2d0" width="368">Title</td>
		<td class="bigblack" bgcolor="#c8e2d0" width="127">Category</td>
		<td width="36"></td>
		<td width="36"></td>
	</tr>
	<cfoutput query="qryListBrochures">
	<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#">
		<td width="100" class="copy">#DateFormat(dtDateModified, "MM-DD-YYYY")#</td>
			<td width="368" class="copy">#strTitle#</td>
			<td width="127" class="copy">

Right here I want to output the catagory name, #qryGetCategories.strCatagory#
based on the foreign key #qryListBrochures.strCaregoryFK#

</td>
			<td align="center" valign="middle" bgcolor="red" class="copy" width="36"><a href="BrochureDelete.cfm?varBrochurePK=#intBrochurePK#" onClick="return confirm('Are you sure?');">delete</a></td>
			<td class="copy" align="center" valign="middle" width="36"><a href="BrochureEdit.cfm?varNewsIDPK=#intBrochurePK#">edit</a></td>
	</tr></cfoutput>
</table>

I'm looking for the best way to do that. Any help is appreciated!

putting the "new" in "newb".....

 

[hatton]

You don't need two queries, only one ... you need to make use of a join to grab the data from the two tables. Something along the lines of this:

SELECT tableBrochures.intBrochureIDPK, tableBrochures.strTitle, tableBrochures.dtDateModified, tableCategory.strCategory, tableCategory.intSortOrder
FROM tableBrochures INNER JOIN tableCategory ON
   tableBrochures.intCategoryIDFK = tableCategory.intCategoryIDPK
ORDER BY tableCategory.intSortOrder, tableCategory.strCategory, tableBrochures.strTitle

That will give you a single recordset that contains all of the data that you need... then you simply loop over the recordset as usual and output the data.

This is, of course, assuming that both tables contain intCategoryID

Hatton

 

[PTmon]

bleh, as usual I was working too hard, trying to figure a way with loops and groups. Thanks for pointing me in the right direction!

putting the "new" in "newb".....

 

[r937]

use nested CFOUTPUTs utilizing the GROUP= parameter:

<cfoutput query="hatton" group="strCategory">
<h2>#strCategory#</h2>
<p>
<cfoutput>#strTitle#<br></cfoutput>
</p>
</cfoutput>

r937.com | rudy.ca