Solved

Query help: How to display all categories associated with a business?

Posted on 2011-02-15
24
800 Views
Last Modified: 2012-06-27
My question is similar to this one: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_26795843.html

My query getBusinesses does not display all categories to which a given business belongs. I use LEFT OUTER JOIN to select all rows, in all related tables, associated with a BusinessID. But since I use this WHERE clause ...

WHERE cat.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">

... my output displays only the BusinessCategoryID in scope URL. So this test page:

http://ascassociation.org/businessdirectory/index_test2.cfm?BusinessCategoryID=20

displays businesses that are wholesalers, but does not display the other categories in which each business belongs.

How can I modify my query, below, to display the complete list of categories for each business?

Hope this makes sense. I am going for concision here. =) Thank you as always. Eric B
Query:

<cfquery name="getBusinesses" datasource="ascassociation">
SELECT *
FROM Businesses bs
LEFT OUTER JOIN BizHasCat bhc
ON bs.BusinessID = bhc.BusinessID
LEFT OUTER JOIN Categories cat
ON cat.BusinessCategoryID = bhc.BusinessCategoryID
WHERE cat.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">
ORDER BY BusinessTitle ASC
</cfquery>


HTML:
<cfloop query="getBusinesses" startRow="#URL.StartRow#" endRow="#EndRow#">
 
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Category:</strong><br />
<cfoutput group="BusinessCategory" groupcasesensitive="no">
#getBusinesses.businesscategory#<br />
</cfoutput>
</div>


<cfoutput group="BusinessTitle" groupcasesensitive="no">
<p><span class="bold blue00467f">#BusinessTitle#</span><br />
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />
<strong>Voice:</strong> #BusinessTelephone#<br />
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif> 

<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>


<hr class="clear-both" />

</cfoutput>

</cfloop>

Open in new window

0
Comment
Question by:Eric Bourland
  • 12
  • 8
  • 4
24 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 34902559
WHERE cat.BusinessCategoryID

I always get thrown off by the naming convention ;-)  Refresh my memory, is BusinessCategoryID a unique ID for distinct categories

       ie   BusinessCategoryID #1 => "Wholesalers and Distributors"  

.... OR for the combination of business and category

      ie    BusinessCategoryID #1 => 3M Health Care + "Wholesalers and Distributors"  
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34902602
=)

BusinessCategoryID is a unique ID for distinct categories.

My three tables are:
Businesses (contains business data -- title, address, contact information, description, etc.)
Categories (contains list of twenty categories by which businesses are categorized)
BizHasCat (relates table Businesses to table Categories -- each business is assigned to one <em>or more</em> categories)

"Wholesalers and Distributors" is BusinessCategoryID # 20, for example.

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 125 total points
ID: 34902677
Oh, wait I see it.  I think you'll need a slightly fancier JOIN for this one.  You don't need OUTER joins because you don't care about business that *don't* have a category assigned.  

This query would return all businessID's assigned to the selected category.  

SELECT  BusinessID
FROM     BizHasCat sel
WHERE  sel.BusinessCategoryID = BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">

Open in new window



Add it back to your original query, joining on "BusinessID" it should produce the results you want.

SELECT *  
FROM   Businesses bs 
            INNER JOIN BizHasCat bhc ON bs.BusinessID = bhc.BusinessID
            INNER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID
            INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID
WHERE  sel.BusinessCategoryID = BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">

ORDER BY BusinessTitle ASC

Open in new window



Btw: You're only interested in businesses that DO have a category assigned. So you don't need outer joins here.  
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34902695
Add it back to your original query, joining on "BusinessID" it should produce the results you want.

Hopefully I didn't confuse things.  You only need the *last* query. The 1st one was just to demonstrate.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34902859
Got it! I will try it out. More soon. EB
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34903038
( no points please )

 small type-o in agx's where clause

 WHERE  sel.BusinessCategoryID = BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">

remove the extra = column =

WHERE  sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">


 Also, I am anticipating here.. but I am guessing that you want this query to work even if you don't pass in a categoryID.  If you don't pass in the cat ID, you want to show the businesses like before (in your previous posts/questions).   That means you need to keep the outer joins EXCEPT for this one when you are looking for this particular category.

 Something like this?.....


<cfquery name="getBusinesses" datasource="ascassociation">
  SELECT *
   FROM Businesses bs
    LEFT OUTER JOIN BizHasCat bhc  ON bs.BusinessID = bhc.BusinessID
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID
   <cfif val(url.BusinessCategoryID)>
    INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID
                            AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">
   </cfif>
  ORDER BY BusinessTitle ASC
</cfquery>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34903058
small type-o in agx's where clause

Good catch, thanks :)

Also, I am anticipating here.. but I am guessing that you want this query to work even if you don't pass in a categoryID

Oh, I didn't realize that category might be optional.  If so, gdemaria's right.  You do need a different query. His should do the trick.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34903269
Hmm. When I do:

<cfquery name="getBusinesses" datasource="ascassociation">
  SELECT *  
   FROM Businesses bs  
    LEFT OUTER JOIN BizHasCat bhc
    ON bs.BusinessID = bhc.BusinessID  
    LEFT OUTER JOIN Categories cat
    ON cat.BusinessCategoryID = bhc.BusinessCategoryID

   <cfif val(url.BusinessCategoryID)>  
    INNER JOIN BizHasCat sel
    ON sel.BusinessID = bs.BusinessID  
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">  
   </cfif>  

  ORDER BY BusinessTitle ASC  
</cfquery>

Open in new window


I get a strange result: if I point the browser at ...
http://ascassociation.org/businessdirectory/index_test2.cfm?BusinessCategoryID=20

... I should get a page for only Wholesalers and Distributors (BusinessCategoryID=20); instead I get Equipment/Instrument Supplies (BusinessCategoryID=8).

Same thing happens if I use this query:

<cfquery name="getBusinesses" datasource="ascassociation">
SELECT *    
FROM   Businesses bs   
            INNER JOIN BizHasCat bhc ON bs.BusinessID = bhc.BusinessID  
            INNER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID  
            INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID  
WHERE  sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#" maxlength="2">  
  
ORDER BY BusinessTitle ASC 

</cfquery>

Open in new window


I get Equipment/Instrument Supplies (BusinessCategoryID=8). I am taking another look at the query.
Table "Categories":

BusinessCategoryID / BusinessCategory
2	Accountants
3	Accreditation Assistance
4	Accreditation Organizations
5	Architectural/Design Firms
6	Attorneys
7	Development Consultants
8	Equipment/Instrument Supplies
9	Financial Capital Appraisers
10	Group Purchasing Organizations
11	Human Resources Firms
12	Insurance Providers
13	Management Consultants
14	Pathology/Laboratory/Anesthesia Services
15	Patient Financial (Billing/Coding) Services
16	Publications
17	Refurbished/Pre-Owned Medical Equipment
18	Satisfaction Assistance
19	Software Companies
20	Wholesalers and Distributors

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 375 total points
ID: 34903300
Hey Eric,

You're seeing the other categories because you asked to above.  I believe you said if the business is in associated with cat ID 20, then show it AND show all categories associated with that business.

....
But, I would like to redirect you for the moment.  Not long ago we helped you with a page that looks very similar to this.  Is there a difference between this page and that page?   Is the only difference that you want to limit which businesses are displayed?   If the answer is Yes, then we should just tweak that page, there is no reason to build a new one.   If there are significant differences, you could COPY that page and modify it.  

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34903349

Remember how we added a group on your cfoutput query tag to group by the business name, then you add an extra cfoutput around the category name...

that's what you're missing here.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34903387
gdemaria,

Thanks for your note. I'll try to explain.

Yep. The page we are working on is pretty similar to the first page. I'll paste the query to that original page, below. This is the query I was trying to modify. I've been trying different ideas using that original query.

>>>You're seeing the other categories because you asked to above.  I believe you said if the business is in associated with cat ID 20, then show it AND show all categories associated with that business.

I'm sorry; I communicated poorly. I'll try to explain.

* there are businesses, and categories of businesses

* each business is associated with a number of categories; as many as five or six categories; or as few as zero

* I need to display all businesses that are in category Wholesalers and Distributors (BusinessCategoryID=20); so, the title at the top of the page should say: Wholesalers and Distributors

* but, in the little boxes floating to the right of each business, I need to list not just Wholesalers and Distributors (BusinessCategoryID=20), but also all of the other categories under which the business is listed

I hope that makes sense.



Query for page: http://ascassociation.org/businessdirectory/searchquery.cfm?search=Yes

<cfquery name="search" datasource="ascassociation">
SELECT
bs.BusinessID
,BusinessTitle
,BusinessContactName
,BusinessAddress1
,BusinessAddress2
,BusinessAddress3
,BusinessCity
,BusinessState
,BusinessPostalCode
,BusinessTelephone
,BusinessFax
,BusinessURL
,BusinessEmail
,BusinessDescription
,bsc.BusinessCategoryID
,BusinessCategory
FROM Businesses bs
LEFT OUTER JOIN BizHasCat bhc
ON bs.BusinessID = bhc.BusinessID
LEFT OUTER JOIN Categories bsc
ON bsc.BusinessCategoryID = bhc.BusinessCategoryID
WHERE 1 = 1

<cfif BusinessCategory IS NOT "">
AND BusinessCategory = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.BusinessCategory#" maxlength="50">
</cfif>

<cfif BusinessState IS NOT "">
AND BusinessState = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.BusinessState#" maxlength="2">
</cfif>

<cfif BusinessTitle IS NOT "">
AND BusinessTitle LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.BusinessTitle#%" maxlength="50">
</cfif>

<cfif keyword IS NOT "">
AND (
 BusinessDescription LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="50">
OR BusinessTitle LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="50">
)
</cfif> 

ORDER BY BusinessTitle ASC
</cfquery>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34903475
>>>Remember how we added a group on your cfoutput query tag to group by the business name, then you add an extra cfoutput around the category name...

Got it. I added the CFOUTPUTs; see code below.

I think we are close to a solution. If you take a look at this page:

http://ascassociation.org/businessdirectory/index_test2.cfm?BusinessCategoryID=20

You will see that business titles are displayed multiple times.

I think I see how to fix that....

Making progress. Thank you for your patience.

E
Query:
<cfparam name="url.Letter" default="">
<cfparam name="url.BusinessCategoryID" default="1">

<cfquery name="getBusinesses" datasource="ascassociation">  
  SELECT *  
   FROM Businesses bs  
    LEFT OUTER JOIN BizHasCat bhc  ON bs.BusinessID = bhc.BusinessID  
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID  
   <cfif val(url.BusinessCategoryID)>  
    INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID  
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">  
   </cfif>  
  ORDER BY BusinessTitle ASC  
</cfquery> 

HTML:
<cfloop query="getBusinesses" startRow="#URL.StartRow#" endRow="#EndRow#">
 
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Category:</strong><br />
<cfoutput group="BusinessCategory" groupcasesensitive="no">

<cfoutput>#getBusinesses.businesscategory#<br /></cfoutput>

</cfoutput>
</div>


<cfoutput group="BusinessTitle" groupcasesensitive="no">
<p><span class="bold blue00467f">#BusinessTitle#</span><br />
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />
<strong>Voice:</strong> #BusinessTelephone#<br />
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif> 

<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>


<hr class="clear-both" />

</cfoutput>

</cfloop>

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 375 total points
ID: 34903541

I understood everything you said already, but thanks.   Your explanation was very clear and I recall the situation.

Your had a lot of LOOPS and CFOUTPUT.   I suggest referring back to the other page as it works as intended.   I've tried modifying the code provided.   There is a simple CFOUTPUT loop with a GROUP and then one extra CFOUTPUT inside to loop around the categories.

I believe that's all that's needed, but again, you can refer to your other page.

... by the way.. if the two pages are the same, why can't you just use the same page for both purposes?  Was it necessary to make a second page?   Just trying to help you with re-usable code.



<cfoutput query="getBusinesses" startRow="#URL.StartRow#" endRow="#EndRow#" group="BusinessTitle">
 
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Category:</strong><br />
<cfoutput>#getBusinesses.businesscategory#<br /></cfoutput>
</div>


<p><span class="bold blue00467f">#BusinessTitle#</span><br />
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />
<strong>Voice:</strong> #BusinessTelephone#<br />
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif> 

<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>


<hr class="clear-both" />

</cfoutput>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34903699
>>> if the two pages are the same, why can't you just use the same page for both purposes?  Was it necessary to make a second page?   Just trying to help you with re-usable code.

I agree. I was confused because the Category pages use a CFLOOP and the RecordCount function; the SearchQuery.cfm page does not use either of those. So I did not see how to re-use that first SearchQuery.cfm page. That page works great, however, and I have been studying it to see how to make the Category pages work.

My problems now:

* The test page has title Equipment/Instrument Supplies, but the title should be Wholesalers and Distributors. (This baffles me. The URL scope says CategoryID=20, but Equipment/Instrument Supplies is CategoryID=8.)

* The grouping by business title is not working; business names repeat if you click Next 20 Records.

* There are not 114 businesses in Wholesalers; there are fewer. I think RecordCount is counting too many businesses.

I think there is a grouping problem. I told ColdFusion to group the output by BusinessTitle, but I am getting repeat BusinessTitles.

http://ascassociation.org/businessdirectory/index_test2.cfm?BusinessCategoryID=20

I will keep looking at this. Thank you again for your help.

p.s. this is not an urgent problem. I am looking again at the original page we created, searchquery.cfm. That page did not use Previous or Next 20 records, or RecordCount, which are required on this page. I believe that is why I need a new page?
<!-----
Name:        index_test2.cfm
Author:      Eric B, _agx_, gdemaria
Description: Display ASCA businesses by category; display 20 businesses per page; use StartRow and EndRow to display next 20 and previous 20 records 
Created:     February 2011
ColdFusion Version 8
MS SQL Server 2005
----->

<cfparam name="url.Letter" default="">
<cfparam name="url.BusinessCategoryID" default="1">

<cfquery name="getBusinesses" datasource="ascassociation">  
  SELECT *  
   FROM Businesses bs  
    LEFT OUTER JOIN BizHasCat bhc ON bs.BusinessID = bhc.BusinessID  
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID  
   <cfif val(url.BusinessCategoryID)>  
    INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID  
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">  
   </cfif>  
  ORDER BY BusinessTitle ASC, BusinessCategory ASC  
</cfquery> 


 
<!--- site header --->
<cfinclude template="/SiteHeader.cfm">
 
 
<!--- configure for next N records --->
<!--- Number of rows to display per Next/Back page --->
<cfset RowsPerPage = 20> <!--- this can be modified to any value --->
 
<!--- At which row shall we start? Assume first row by default --->
<cfparam name="URL.StartRow" default="1" type="numeric">
 
<!--- Allow for Show All parameter in the URL --->
<cfparam name="URL.ShowAll" type="boolean" default="No">
 
<!--- We know the total number of rows from query getBusinesses --->
<cfset TotalRows = getBusinesses.RecordCount>
 
 
<!--- Last row is 10 rows past the starting row, or --->
<!--- total number of query rows, whichever is less --->
 
<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Next button goes to 1 past current end row  --->
 
<cfset StartRowNext = EndRow + 1>
<!--- Back button goes back N rows from start row --->
 
<cfset StartRowBack = URL.StartRow - RowsPerPage> 
 
<p>[ <a href="/businessdirectory/">Return to ASC Supporter Business Directory</a> ]</p>
 
 
<h1>ASC Supporter Business Directory</h1>
 
<h2><cfoutput>#getBusinesses.BusinessCategory#</cfoutput></h2>

         <!-- include Next N records -->
        <!---provides next/back arrows for navigation through records--->
         
        <div class="align-center">
        <cfoutput>
            <p><em>Displaying records #URL.StartRow# to #EndRow# of #TotalRows# total.</em>
            
            <br />   
         
            <!---show links for Previous if necessary--->
           <cfif StartRowBack GT 0>
              <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#&BusinessCategoryID=#url.BusinessCategoryID#">&laquo; previous #RowsPerPage# records</a>
           </cfif>
            
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            
            <!---show links for Next if necessary--->
            <cfif StartRowNext LTE TotalRows>
             <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#&BusinessCategoryID=#url.BusinessCategoryID#">next #RowsPerPage# records &raquo;</a>
             </cfif>
             
             </p>
            
        </cfoutput>
        </div>


 <cfoutput query="getBusinesses" startRow="#URL.StartRow#" group="BusinessTitle" groupcasesensitive="no">  
   
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Category:</strong><br />  
<cfoutput group="BusinessCategory" groupcasesensitive="no">#getBusinesses.businesscategory#<br /></cfoutput>  
</div>  
  
  
<p><span class="bold blue00467f">#BusinessTitle#</span><br />  
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>  
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>  
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>  
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>  
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />  
<strong>Voice:</strong> #BusinessTelephone#<br />  
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>  
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>  
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif>   
  
<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>  
  
  
<hr class="clear-both" />  
  
</cfoutput> 

 
 
        <!--- include Next N records --->
        <!--- provides next/back arrows for navigation through records --->
         
        <div class="align-center">
        <cfoutput>
            <p><em>Displaying records #URL.StartRow# to #EndRow# of #TotalRows# total.</em>
            
            <br />   
         
            <!---show links for Previous if necessary--->
           <cfif StartRowBack GT 0>
              <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#&BusinessCategoryID=#url.BusinessCategoryID#">&laquo; previous #RowsPerPage# records</a>
           </cfif>
            
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            
            <!---show links for Next if necessary--->
            <cfif StartRowNext LTE TotalRows>
             <a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#&BusinessCategoryID=#url.BusinessCategoryID#">next #RowsPerPage# records &raquo;</a>
             </cfif>
             
             </p>
            
        </cfoutput>
        </div>

 
<!--- site footer --->
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34908785
To simplify the problem, I created a test page that omits the RecordCount and Next / Previous 20 Records function:

http://ascassociation.org/businessdirectory/index_test3.cfm?BusinessCategoryID=20

It works almost perfectly.

This CFOUTPUT:

<h2><cfoutput>#getBusinesses.BusinessCategory#</cfoutput></h2>

still calls Equipment/Instrument Supplies (BusinessCategoryID=8) rather than Wholesalers (BusinessCategoryID=20). Which is strange.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34908960
Can you show the code for that page?

Are you saying that you deleted the Record Count/ next page code?  You could just put a CFIF around it when you don't need it.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34909224
>>>Are you saying that you deleted the Record Count/ next page code?  You could just put a CFIF around it when you don't need it.

For now, yes. It was confusing me and confusing my communication with you and _agx_. I'll put it back in later. I have looked at that code for a long time and feel like I understand it. I am working out how to make RecordCount work for me, rather than me for it.


code for http://ascassociation.org/businessdirectory/index_test3.cfm?BusinessCategoryID=20 :
<!-----
Name:        index_test2.cfm
Author:      Eric B, _agx_, gdemaria
Description: Display ASCA businesses by category; display 20 businesses per page; use StartRow and EndRow to display next 20 and previous 20 records 
Created:     February 2011
ColdFusion Version 8
MS SQL Server 2005
----->

<cfparam name="url.Letter" default="">
<cfparam name="url.BusinessCategoryID" default="1">

<cfquery name="getBusinesses" datasource="ascassociation">  
  SELECT *  
   FROM Businesses bs  
    LEFT OUTER JOIN BizHasCat bhc ON bs.BusinessID = bhc.BusinessID  
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID  
   <cfif val(url.BusinessCategoryID)>  
    INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID  
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">  
   </cfif>  
  ORDER BY BusinessTitle ASC, BusinessCategory ASC  
</cfquery> 


 
<!--- site header --->
<cfinclude template="/SiteHeader.cfm">
  
<p>[ <a href="/businessdirectory/">Return to ASC Supporter Business Directory</a> ]</p>
 
 
<h1>ASC Supporter Business Directory</h1>
 
<h2>#getBusinesses.BusinessCategory#: <cfoutput>#getBusinesses.BusinessCategory#</cfoutput></h2>
<h2>#getBusinesses.BusinessCategoryID#: <cfoutput>#getBusinesses.BusinessCategoryID#</cfoutput></h2>
<h2>#url.BusinessCategoryID#: <cfoutput>#url.BusinessCategoryID#</cfoutput></h2>

<p>(My goal is to display the category title associated with #url.BusinessCategoryID# : Wholesalers and Distributors.)</p>
   
   <hr />
   
 <cfoutput query="getBusinesses" group="BusinessTitle" groupcasesensitive="no">  
   
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Category:</strong><br />  
<cfoutput group="BusinessCategory" groupcasesensitive="no">#getBusinesses.businesscategory#<br /></cfoutput>  
</div>  
  
  
<p><span class="bold blue00467f">#BusinessTitle#</span><br />  
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>  
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>  
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>  
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>  
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />  
<strong>Voice:</strong> #BusinessTelephone#<br />  
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>  
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>  
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif>   
  
<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>  
  
  
<hr class="clear-both" />  
  
</cfoutput> 

 
 

 
<!--- site footer --->
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 375 total points
ID: 34909293
The problem is that you are taking the category name to display from your main query.  Since this query can include other categories (to show in the box), it's not a good place to pull it from.   What is happening is that you are using teh first record's category name, but that record happens to be a different category.

Instead see the code below.  Pull the category name seperately.   If there is not category ID passed on the URL, it will not show, if there is, it will show.  This is how you can use the same code for both purposes.


<!-----
Name:        index_test2.cfm
Author:      Eric B, _agx_, gdemaria
Description: Display ASCA businesses by category; display 20 businesses per page; use StartRow and EndRow to display next 20 and previous 20 records 
Created:     February 2011
ColdFusion Version 8
MS SQL Server 2005
----->

<cfparam name="url.Letter" default="">
<cfparam name="url.BusinessCategoryID" default="1">

<cfquery name="getBusinesses" datasource="ascassociation">  
  SELECT *  
   FROM Businesses bs  
    LEFT OUTER JOIN BizHasCat bhc ON bs.BusinessID = bhc.BusinessID  
    LEFT OUTER JOIN Categories cat ON cat.BusinessCategoryID = bhc.BusinessCategoryID  
   <cfif val(url.BusinessCategoryID)>  
    INNER JOIN BizHasCat sel ON sel.BusinessID = bs.BusinessID  
    AND sel.BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">  
   </cfif>  
  ORDER BY BusinessTitle ASC, BusinessCategory ASC  
</cfquery> 

<cfset activeCategoryName = "">
<cfif val(url.BusinessCategoryID)>
  <cfquery name="getCat" datasource="#request.datasource#">
   select BusinessCategory
    from Categories
   where BusinessCategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.BusinessCategoryID#">  
  </cfquery>
  <cfset activeCategoryName = getCat.BusinessCategory>
</cfif>
 
<!--- site header --->
<cfinclude template="/SiteHeader.cfm">
  
<p>[ <a href="/businessdirectory/">Return to ASC Supporter Business Directory</a> ]</p>
 
 
<h1>ASC Supporter Business Directory</h1>
 
<cfif len(activeCategoryName)>
<h2><cfoutput>#activeCategoryName#</cfoutput></h2>
</cfif>
<hr />
   
   
<cfoutput query="getBusinesses" group="BusinessTitle" groupcasesensitive="no">  
   
<div class="boxf9edd0 float-right-img font11 width250px"><strong>Business Category:</strong><br />  
<cfoutput group="BusinessCategory" groupcasesensitive="no">#getBusinesses.businesscategory#<br /></cfoutput>  
</div>  
  
  
<p><span class="bold blue00467f">#BusinessTitle#</span><br />  
<cfif BusinessContactname IS NOT "">#BusinessContactname#<br /></cfif>  
<cfif BusinessAddress1 IS NOT "">#BusinessAddress1#<br /></cfif>  
<cfif BusinessAddress2 IS NOT "">#BusinessAddress2#<br /></cfif>  
<cfif BusinessAddress3 IS NOT "">#BusinessAddress3#<br /></cfif>  
#BusinessCity# #BusinessState# #BusinessPostalCode#<br />  
<strong>Voice:</strong> #BusinessTelephone#<br />  
<cfif BusinessFax IS NOT ""><strong>Fax:</strong> #BusinessFax#<br /></cfif>  
<cfif BusinessEmail IS NOT ""><strong>E-mail:</strong> <a href="mailto:#BusinessEmail#">#BusinessEmail#</a><br /></cfif>  
<cfif BusinessURL IS NOT ""><strong>Web Address:</strong> <a href="http://#BusinessURL#">#BusinessURL#</a><br /><br /></cfif>   
  
<cfif BusinessDescription IS NOT ""><strong>About #BusinessTitle#:</strong><br /> #BusinessDescription#</cfif></p>  
  
  
<hr class="clear-both" />  
  
</cfoutput> 

 
 

 
<!--- site footer --->
<cfinclude template="/SiteFooter.cfm">

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34909401
That worked like a charm.

I do see that I needed a second query.

http://ascassociation.org/businessdirectory/index_test3.cfm?BusinessCategoryID=20

Next I am putting back in the Previous / Next 20 / RecordCount code.

Thank you again. More in a little while.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34909519

if you remove the BusinessCategoryID from the URL, you should be able to see all records.  I think the only thing stopping you is the default=1, change that to default="" and you will be all set
 
<cfparam name="url.BusinessCategoryID" default="1">
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34911515
>>>  I think the only thing stopping you is the default=1, change that to default="" and you will be all set

I did this, and then I put back the code that includes the Previous 20 / Next 20 records, and the RecordCount code. I'm getting duplicate records on the "Next 20" pages. RecordCount counts 114 records. There are 40 businesses under Wholesalers, and some of those have more than one category, so I think the 114 RecordCount occurs because ColdFusion counts the number of categories, total, from the Wholesalers. I'm trying to count only 40 records -- one record for each business.

But that is another question, which I will think about, and write up and post here.

You and _agx_ answered this question. =) I see what you did, and it makes sense. Thank you as always.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34911565
> think the 114 RecordCount occurs because ColdFusion counts the number of categories

yes, we solved this problem in your other page.  

It seems to me that it would be very easy to add the URL category to your fully working page.

In either case, check out that code and let us know what you need.   If you post another question, feel free to keep it short and post the code.   I think we just added a query of query to count the businesses ?  
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 34911613
Thanks as always to _agx_ and gdemaria.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 34913082
>> think the 114 RecordCount occurs because ColdFusion counts the number of categories

>>yes, we solved this problem in your other page.  

The problem might be slightly different -- I could be wrong. =) In the previous page I simply took away the RecordCount function, and took away the "Show Previous 20 / Show Next 20 Records" code. The page worked then. This time I need to keep the RecordCount function, and the "Show Previous 20 / Show Next 20 Records".

I've been working on this problem much of afternoon and evening. Now I am working on the text of my next question to post here. More in a while. I hope you are well. Also of course, these questions are not urgent and I hope you will reply when it is convenient for you. E
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now