Jon J. Jon J. - 3 months ago 9
MySQL Question

Inner loop query not getting intended results

I have an issue with my cold fusion / mysql script. It goes like this... i have two tables of info.

I have two tables; tbl_property and tbl_prop data. The latter has data sheets relating to the properties that are in the other table... Below is my query and hopefully someone can tell me what I am doing wrong!

<cfquery name="getInfoByStreet" datasource="#application.dsn#">
SELECT
tbl_property.prp_street_name,
tbl_property.prp_street_suffix,
tbl_property.prp_street_number,
tbl_property.prp_street_direction,
tbl_prop_data.pd_cat,
tbl_prop_data.pd_ref,
tbl_prop_data.pd_action,
tbl_prop_data.pd_date,
tbl_prop_data.pd_pdfFile,
tbl_prop_data.pd_activity,
tbl_prop_data.prp_ID,
tbl_prop_data.pd_ID,
tbl_prop_data.company_ID,
tbl_prop_data.pd_status

FROM tbl_property

INNER JOIN tbl_prop_data ON tbl_property.prp_ID = tbl_prop_data.prp_ID

WHERE pd_active = 1
AND pd_date >= <cfqueryparam value="#begin#" cfsqltype="cf_sql_date">
AND pd_date <= <cfqueryparam value="#end#" cfsqltype="cf_sql_date">
<cfif activityS1 neq 0>AND pd_activity = '#activityS1#'</cfif>
<cfif referenceS1 neq 0>AND pd_ref = '#referenceS1#'</cfif>
<cfif actionS1 neq 0>AND pd_action = '#actionS1#'</cfif>
<cfif statusS1 neq 0>AND pd_status = '#statusS1#'</cfif>
AND pd_cat = '#form.cat#'

GROUP BY prp_street_name ASC

</cfquery>


Here is a screenshot of the results it's giving me - and also the display code. What I want is to loop the records that are a match under the grouped streets...

<cfoutput query="getInfoByStreet" group="prp_street_name">

<table width="100%" border="0">
<tr>
<th class="display">#prp_street_name# #prp_street_suffix#</th>
<th class="display">Date</th>
<th class="display">Reference</th>
<th class="display">Action</th>
<th class="display">PDF</th>
</tr>
<cfloop query="getInfoByStreet">
<tr>
<td height="41">#prp_street_number# #prp_street_direction# #prp_street_name# #prp_street_suffix#</td>
<td>#DateFormat(pd_date, "mm/dd/yyyy")#</td>
<td><span class="smallBlack">#getActionNow.name#</span></td>
<td><span class="smallBlack">#getRefNow.name#</span></td>
<td>#pd_pdfFile#</td>
</tr>
</cfloop>
</table>
</cfoutput>


Front-end display of my posted code

Answer

When using the cfoutput to loop through a group query you would just use a nested cfoutput to loop through the group.

<cfoutput query="getInfoByStreet" group="prp_street_name"> <table width="100%" border="0"> <tr> <th class="display">#prp_street_name# #prp_street_suffix#</th> <th class="display">Date</th> <th class="display">Reference</th> <th class="display">Action</th> <th class="display">PDF</th> </tr> <cfoutput> <tr> <td height="41">#prp_street_number# #prp_street_direction# #prp_street_name# #prp_street_suffix#</td> <td>#DateFormat(pd_date, "mm/dd/yyyy")#</td> <td><span class="smallBlack">#getActionNow.name#</span></td> <td><span class="smallBlack">#getRefNow.name#</span></td> <td>#pd_pdfFile#</td> </tr> </cfoutput> </table> </cfoutput>

Comments