Solved

Inaccurate search results when searching related tables; need to refine XML Path?

Posted on 2011-09-29
43
328 Views
Last Modified: 2012-05-12
Hi. I'm working on a search interface for careplanners.net, but I'm not getting the accurate search results for Degrees or Certifications.

I think I need to refine the query in which I request search values for Degrees or Certifications.

The search interface is here: http://careplanners.net/search.cfm

And if you search on any degree -- MS, Ph.d, etc. -- you will see that the results display all careplanners records, instead of only records that contain MS, Ph.d, etc.

I've been puzzling over this for much of today. Do I need to modify the XML paths in my query?

Thanks as always for advice.

The XML paths begin around line 130 of the code below.

Eric

search.cfm:
<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeTitle
        FROM   careplanners_degrees
</cfquery>

<!--- this query selects certificationTitle from lookup table careplanners_certifications --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetCertifications">
        SELECT  certificationTitle
        FROM   careplanners_certifications
</cfquery>


 <!--- this query selects practice from table lookup careplanners_practice --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetPractice">
        SELECT  practice
        FROM    careplanners_practice
        ORDER BY practice ASC
</cfquery>

<!--- this query selects StateName values from lookup table USCA_States --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>


<h2>Search Life Careplanners</h2>

<div class="boxf9edd0 width400px">

<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchLCP" style="margin-top:1px;">

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Degree" /> Degree:
<cfselect size="1" name="DegreeTitle" value="DegreeTitle" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect> 
</p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Certification:
<cfselect size="1" name="certificationTitle" value="certificationTitle" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
          </cfselect> 
</p>

       
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Primary Practice:
<cfselect size="1" name="practice" value="practice" display="practice" multiple="no" query="GetPractice" queryPosition="below">
     <option value="">Search by Practice:</option>
     </cfselect></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Keyword" /> Keyword:
<cfinput type="text" name="keyword" size="30" /></p>

              
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Last Name" /> Careplanner Last Name:
<cfinput type="text" name="LastName" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by City" /> Careplanner City:
<cfinput type="text" name="city1" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by State" /> State:
     <cfselect size="1" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below">
     <option value=""> Select State: </option>
     </cfselect>  
</p>
<div class="align-center">
  <input type="submit" value="Search" />
</div>

</cfform>

</div>


<p><img src="/img/bronzestar.gif" width="14" height="13" alt="bronze star" /> <a href="/directory.cfm">List All Careplanners</a></p>


searchquery.cfm:
<cfset bgcolor="##ffffff">
<cfparam name="form.keyword" default="">
<cfparam name="form.LastName" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.DegreeTitle" default="">
<cfparam name="form.certificationTitle" default="">
<cfparam name="DegreeTitleList" default="">
<cfparam name="certificationTitleList" default="">


<cfif IsDefined("URL.search")>

<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">
SELECT
<!--- select from parent table, CareplannersMembers cp --->
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           <!---AND d.DegreeTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#">--->
                           <!---add the above to tell substring query to select only form.degreeTitle? --->
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif DegreeTitleList IS NOT "">
AND DegreeTitleList = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#">
</cfif>

<cfif certificationTitleList IS NOT "">
AND certificationTitleList = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.certificationTitle#">
</cfif>

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

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

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

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

<cfif keyword IS NOT "">
AND (
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

<cfif searchCareplanners.RecordCount GT 0> 

<cfoutput>
<div class="brownbox">

<p>You searched on these parameters:</p>

<p><strong>Careplanner Name:</strong> <em>#form.LastName#</em><br />
<strong>Degree:</strong> <em>#form.DegreeTitle#</em><br />
<strong>Certification:</strong> <em>#form.CertificationTitle#</em><br />
<strong>Primary Practice:</strong> <em>#form.practice#</em><br />
<strong>City:</strong> <em>#form.city1#</em><br />
<strong>State:</strong> <em>#form.state1#</em><br />
<strong>Keyword:</strong> <em>#form.keyword#</em></p>

<p class="align-center">Results are sorted by Careplanner Name, ascending A to Z.</p>
</div>
</cfoutput>

<p>&nbsp;</p>

<cfoutput query="searchCareplanners" group="LastName" groupcasesensitive="no">




<!--- begin contractor display --->
<div class="contractor_display">

<!--- begin contractor display_left --->
<div class="contractor_display_left">
<p><strong><cfif prefix IS NOT "">#searchCareplanners.prefix#</cfif>
#firstname#
<cfif middleinit IS NOT "">#middleinit#</cfif>
#lastname#<cfif suffix IS NOT "">, #suffix#</cfif></strong></p>

<p>Company or Organization: #company#<br />

Primary Practice: #practice#<br />

Primary Phone: #primaryphone#<br />

Email: #UserEmail#<br />

Web Address #website#<br />

Street: #street1#<br />
City: #city1#<br />
State: #state1#<br />
ZIP or Postal Code: #zip1#<br />
Country: #country1#<br />
Business Telephone 1: #phone1#<br />
Fax 1: #fax1#<br />
<cfif street2 IS NOT "">Business Address 2: #street2#<br /></cfif>
<cfif city2 IS NOT "">#city2#<br /></cfif>
<cfif state2 IS NOT "">#state2#<br /></cfif>
<cfif zip2 IS NOT "">#zip2#<br /></cfif>
<cfif country2 IS NOT "">#country2#<br /></cfif>
<cfif phone2 IS NOT "">#phone2#<br /></cfif>
<cfif fax2 IS NOT "">Fax 2: #fax2#<br /></cfif>

Information: #info#</p>



</div>
<!--- /contractor display_left --->


<!--- begin contractor display_right --->
<div class="contractor_display_right">
<p><strong>Degrees:</strong><br />
<cfoutput group="DegreeTitleList" groupcasesensitive="no">
#listChangeDelims(searchCareplanners.degreeTitleList,"<br />")#
</cfoutput> 
</p>

<p><strong>Certifications:</strong><br />
<cfoutput group="CertificationTitleList" groupcasesensitive="no">
#listChangeDelims(searchCareplanners.certificationTitleList,"<br />")#
</cfoutput> </p>




<!--- /contractor display_right --->
</div>



<!--- end contractor display --->
</div>

</cfoutput>
 


<cfelse>
<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/directory.cfm">Return to Careplanners Directory</a></p>
<p>&nbsp;</p>

</cfif>
</cfif>

Open in new window

0
Comment
Question by:Eric Bourland
  • 14
  • 13
  • 12
  • +1
43 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
This may be too obvious, but you know you have a CF comment around part of the WHERE clause, right?

substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           <!---AND d.DegreeTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#">--->
                           <!---add the above to tell substring query to select only form.degreeTitle? --->

                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )

It appears that is the bit that limits which degrees to show.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Hey Eric,

First, you have to remember that DegreeTitleList is a comma delimited list of titles, so testing a list of values against one value would not match...  for example   A,B,C = B  (would not match)

<cfif DegreeTitleList IS NOT "">
AND DegreeTitleList = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#">
</cfif>


But you really don't want to search by a title name anyway, you want to search by IDs.  
This is the approach you can take, use a sub query in the where clause to locate the desired ID..

WHERE 1 = 1
and exists (select 1
    from careplanners_has_degrees chd
    where chd.CareplannersID = cp.CareplannersID
    and   chd.degreeID =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.degreeID#">
    )

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Wait a minute, you are trying to filter on DegreeTitleList in the same query you are generating that column? That will not work. At least not in most database systems like MS SQL where aliases cannot be referenced in WHERE clause. The FOR XML PATH leads me to believe this is MS SQL, so forgive me if I am wrong. You will probably need to alter the WHERE clause and uncomment the filter in the subquery to FOR XML if a person may have more than one degree.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
msvisa1: Sorry, I should have clarified: I'm using MS SQL 2005, and ColdFusion 9.

Yes, I did comment out the WHERE clause because, for the reasons that gdemaria points out, it's not effective in this case:

>>>for example   A,B,C = B  (would not match)

I'm going to try the subquery the gdemaria suggests. I'll let you know my results.

mwvisa1 and gdmaria -- thank you!

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

Now I'm getting a search result of No Records Found. I am looking at the logic to see why that is so.

Is this the query that you had in mind? And, is it ok to add an EXISTS clause for certifications, after degrees?

<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">
SELECT
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

FROM CareplannersMembers cp

WHERE 1 = 1

and exists (select 1
    from careplanners_has_degrees chd
    where chd.CareplannersID = cp.CareplannersID
    and   chd.degreeID =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.degreeID#">
    )

and exists (select 1
    from careplanners_has_certifications chc
    where chc.CareplannersID = cp.CareplannersID
    and   chc.certificationID =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.certificationID#">
    )

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

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

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

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

<cfif keyword IS NOT "">
AND (
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Eric,

The segment of code I was referring to is the code that generates the comma separated list, correct? Therefore, if you filter it before it generates the list, then it will work.

If what you are saying is you need to filter on multiple, then you can do that using a split function or something like:
substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           AND ', ' + d.DegreeTitle + ',' LIKE ', ' + <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#"> + ','
                           <!---add the above to tell substring query to select only form.degreeTitle? --->
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )

It is okay as long as you have rows that will have BOTH the requested degree and certification. Otherwise, you will get no result set. Also, you are using the ID, should that not be INT data type? Also, do you truly have the degreeID or do you have the degreeTitle? If the latter, then you want your EXISTS to be similar to the subquery.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hi, mvvisa1,

>>>Also, you are using the ID, should that not be INT data type?
Of course. Thanks. =) Fixed.

>>Also, do you truly have the degreeID or do you have the degreeTitle?
I'll try to explain what I was doing (and in doing maybe illustrate that I am doing it wrong or not thinking it through).

* Table careplanners_has_degrees is a relational table that relates table CareplannersMembers to table careplanners_degrees.

I think I can query table careplanners_has_degrees for DegreeID, and ... well, yeah. Hmm. Where would I derive DegreeTitle? DegreeTitle is in the lookup table, careplanners_degrees.

* I'm also trying to fully understand the idea that gdemaria put forward... gdemaria is very likely thinking of something that I am not. =) Or else I don't fully understand his solution.

And I probably need to clarify my goal: a careplanner can have multiple degrees and multiple certifications; in fact, most do. But, for simplicity's sake, I'm allowing a visitor to search by one degree at a time, and / or one certification at a time. So, a visitor might search for degree "MS", certification "CCM", in the state of Illinois. I would hope that the search result displays all careplanners who have an MS degree and a CCM certification who work in Illinois.

For now, since I've got insomnia anyway, I will implement your idea as you suggest above, and see what I get. I'll also work more with gdemaria's suggestion; I think I am overlooking something there. Thank you for this helpful reply and for your time. I'll let you know my result.

Eric
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Since the user is typing in "MS", then you want:

AND EXISTS(
   SELECT 1
   FROM careplanners_has_degrees chd
   INNER JOIN careplanners_degrees d on hd.degreeID = d.degreeID
   WHERE chd.CareplannersID = cp.CareplannersID
   AND d.DegreeTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#">
)

Looking at your code and sample URL, you are only grabbing the certification and degrees by the textual values and not the ID; therefore, you cannot then search on ID as that is not being passed from your form unless I am missing something.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Sorry Eric - I actually went to bed, which I see you didn't do!   Hope you got some sleep eventually.

The answer is easy, you didn't (and I didn't) wrap the where clause in the CFIF statement.   If you don't pass a FORM.DegreeID, then you don't want to do the search because you won't get any records...

So for the same reason you have all the other conditions wrapped in a CFIF statement, you want to also wrap these new conditions in the CFIF statements...


WHERE 1 = 1
<cfif val(form.degreeID)>
and exists (select 1
    from careplanners_has_degrees chd
    where chd.CareplannersID = cp.CareplannersID
    and   chd.degreeID =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.degreeID#">
    )
</cfif>
<cfif val(form.certificationID)>
and exists (select 1
    from careplanners_has_certifications chc
    where chc.CareplannersID = cp.CareplannersID
    and   chc.certificationID =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.certificationID#">
    )
</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
>  therefore, you cannot then search on ID as that is not being passed from your form unless I am missing something.

Eric, mwvisa makes a good point.   I am assuming that you are changing your search form to pass the ID of the degree and the ID of the certification.   You are not going to do text searches on these values since you already have a list for the user to choose from, is that correct?

That is, the user will use checkboxes, radio buttons or a select tag to choose the degree and certification (from your master list) they are interested in.   The user will NOT type in words like "Masters" or "Healthcare" in order to match the degree/certification.   Please confirm.

Also, can the user choose more than one degree when searching?  Or just one...
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
One more comment :)

I noticed that your XML list generation was not in your last select statement.   You don't want to remove it, you want to keep it in there...

 
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           <!---AND d.DegreeTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DegreeTitle#">--->
                           <!---add the above to tell substring query to select only form.degreeTitle? --->
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 

FROM CareplannersMembers cp

Open in new window



This code in the select statement collects up a list of all the degrees, etc and put them into a single comma delimited list.   That's the "fetching" part of the SQL statement.

We want to then add the "filter" part of the SQL statement, to get only the people who have a matching degree (etc).   This is seperate and needs to be there in addition to the code in the select.


This is the result...   If the user chooses to search on "Masters" as a degree, you should see all care planners with a masters degree... but in the column under "degree" you will see a list of all their degrees (not just masters), although masters is guaranteed to be on the list as you searched for it...

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

>>>I am assuming that you are changing your search form to pass the ID of the degree and the ID of the certification.
Correct.

>>>>You are not going to do text searches on these values since you already have a list for the user to choose from, is that correct?
Correct.

>>>The user will NOT type in words like "Masters" or "Healthcare" in order to match the degree/certification.   Please confirm.

Correct and confirmed.

>>>Also, can the user choose more than one degree when searching?  Or just one...
For now, just one degree. I want to keep it simple.

Sorry, I've been in meetings all day. Preventing me from doing actual work. I am getting caught up now. I'm reviewing notes from you and mwvisa and will get back to you soon. Hope your day is going well.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Hi again, gdemaria and mcvisa. I've reviewed your notes and made what I think are the correct changes in my code. Your notes make sense, and your code makes sense to me too.

I'm still getting a list of all 622 careplanners records in my search results. This puzzles me, because in the query, the AND clause tells ColdFusion to request the degree and / or certification value from the FORM scope:

<cfif val(form.degreeID)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)
</cfif>

<cfif val(form.certificationID)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)
</cfif>

Open in new window


If you try the form and search for any degree or cert, you will see that the search result yields all careplanners from A - Z: http://careplanners.net/search.cfm

In search.cfm, the search form is pretty basic:
<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchCareplanners">
<p> Degree:
<cfselect size="1" name="DegreeTitle" value="DegreeTitle" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect> 
</p>

<p>Certification:
<cfselect size="1" name="certificationTitle" value="certificationTitle" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
          </cfselect> 
</p>

       
<p>Primary Practice:
<cfselect size="1" name="practice" value="practice" display="practice" multiple="no" query="GetPractice" queryPosition="below">
     <option value="">Search by Practice:</option>
     </cfselect></p>

<p>Keyword:
<cfinput type="text" name="keyword" size="30" /></p>

              
<p>Careplanner Last Name:
<cfinput type="text" name="LastName" size="30" /></p>

<p>Careplanner City:
<cfinput type="text" name="city1" size="30" /></p>

<p>State:
     <cfselect size="1" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below">
     <option value=""> Select State: </option>
     </cfselect>  
</p>

  <input type="submit" value="Search" />


</cfform>

Open in new window


Values for Degrees and Certifications are derived by querying a lookup table. I wonder, should the SELECT menu for Degrees have value "DegreeID"?

<cfselect size="1" name="DegreeTitle" value="DegreeTitle" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">

... since DegreeID is the variable that searchquery.cfm works with?

Or have I missed something in my query, below?

Thanks again. =)

Eric
query:
<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">
SELECT
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
		FROM careplanners_has_degrees hd
		inner join careplanners_degrees d on hd.degreeID = d.degreeID
		WHERE hd.CareplannersID = cp.CareplannersID
		ORDER BY d.DegreeTitle
		FOR XML PATH( '' )), 3, 1000 )

,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
        FROM careplanners_has_certifications hc
        inner join careplanners_certifications c on hc.certificationID = c.certificationID
        WHERE hc.CareplannersID = cp.CareplannersID
        ORDER BY c.certificationTitle
        FOR XML PATH( '' )), 3, 1000 )

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif val(form.degreeID)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and  chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)
</cfif>

<cfif val(form.certificationID)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)
</cfif>

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

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

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

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

<cfif keyword IS NOT "">
AND (
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

output:

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Try checking the parameters using is defined. I suspect the if conditions are not being met, so you are only testing where 1=1.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
That makes sense. And I need to include the WHERE 1 = 1 because I need at least one true condition for a WHERE clause, in case all CFIF conditions are false.

I'll try IsDefined and let you know what I find. Thanks, mcvisa.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I think that the parameters for Degree and Certificate are indeed being passed over. Skipped.

I can search on Practice, or by state, and I get the expected results. I can search on Occupational Therapy in state of California and I get the expected results.

So, this part is not working:

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif isDefined(form.degreeID)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)
</cfif>

<cfif isDefined(form.certificationID)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)
</cfif>

Open in new window


I am going to look in the search.cfm code -- the form code. Maybe I need to form submit DegreeID and CertificateID, rather than DegreeTitle and CertificateTitle?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 84 total points
Comment Utility
(I've only skimmed so take this with a grain of salt. ...)

> Maybe I need to form submit DegreeID and CertificateID, rather than DegreeTitle and CertificateTitle?

Yes.  Also this sounds like a problem we've had before.  Remember, I think one of your forms was submitting a field named "degreeTitle"

      <cfselect size="1" name="DegreeTitle" value="DegreeTitle" ...>

... but the action page was expecting a field named "degreeID" instead. Same goes for certificateID.

      <cfselect size="1" name="DegreeID" value="DegreeID" ...>

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>>Yes.  Also this sounds like a problem we've had before.  Remember, I think one of your forms was submitting a field named "degreeTitle"

I remember this. I edited the search.cfm page so that the value of the degree and certification fields is ID, not Title:

<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchCareplanners" style="margin-top:1px;">

<p>Degree:
<cfselect size="1" name="DegreeTitle" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect> 
</p>

<p>Certification:
<cfselect size="1" name="certificationTitle" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
          </cfselect> 
</p>

Open in new window


Hmmm. Still, I get all records returned, rather than the records I searched for.

There is probably something very simple that I am missing. I am reviewing the code again.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
The name of your select tag needs to be the ID fields... the way you have this, you are passing the ID into a variable FORM.DegreeTitle...    (see below, the value is DegreeID, but the NAME is DegreeTitle.)   It is the name of the tag that creates the form variable...  so those have to be the IDs...

<p>Degree:
<cfselect size="1" name="DegreeTitle" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect>
</p>

<p>Certification:
<cfselect size="1" name="certificationTitle" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
          </cfselect>


0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Are you using a different search page? Because this is still using the titles

         http://careplanners.net/search.cfm

> I am reviewing the code again.

Always debug the basics.  See if it's a bad value or bad cfif  (not sure which cfif test you're using now).

1) Before your cfquery, dump the form scope to what value (if any) is passed
<cfdump var="#form#">
2) Mimic the if/else test to see if it passes.
<cfif val(form.certificationID)>
       yay! my subquery code will execute
<cfelse>
      either this is the wrong test or nothing was selected.
</cfif>
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility

Don't use isDefined() here...  that really does nothing.   You want to filter only if you have a value passed in the form field.   The form variable will be defined no matter what (because it's a select tag) so the condition will always be true.   But when the DegreeID is left blank, you will either get an error, or you will return no values (because it's attempting to match degreeID = NULL or degreeID = 0)

So this...

<cfif isDefined(form.degreeID)>

Should be this...

<cfif val(form.degreeID)>

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> The name of your select tag needs to be the ID fields.

lol.  I thought that was corrected. I even bolded both values that needed to be changed ;-) in http://#a36895041 too. Eric I think you're as tired as I am.  Time for a break. For me anyway.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
lol, ya...  I hear ya
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
The code in the body of the question shows only the DegreeTitle being pulled from SQL; therefore, I do not see how DegreeID is even possible, which is why I kept writing the code using DegreeTitle. If that is the issue, then you need to do a whole lot more than change the name of the cfselect.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

mwvisa, the table " careplanners_has_degrees " is a join between degrees and careplanners, it contains the degreeID.

The search form has a select tag which is a listing of degreeID and degreeTitles from the degrees table.  The use will select a degree and the select tag will pass the degreeID to the search form.

Eric had mistakenly put the degreeTitle in the value parameter of the select tag, he should have put the degreeID in the value tag so the search could be done by ID not by title name.  

make sense?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
It does NOT matter what he puts in the value tag if it is NOT being retrieved from the database.

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeTitle
        FROM   careplanners_degrees
</cfquery>

I know I have not been coding in CF for a while, but trust me I am not totally clueless to the concepts of web development. :)

Where I am offering my expertise right now though is as a database professional. This query does NOT return anything other than DegreeTitle, so that is the only option available to the cfselect that will actually have values. Therefore, my comment still stands that if it is the case that DegreeID is what is intended to be passed, that simply changing the cfselect is NOT sufficient. :)
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 83 total points
Comment Utility
If we want DegreeID, then this at a minimum must be done:

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeID, DegreeTitle
        FROM   careplanners_degrees
</cfquery>

<!--- ... --->

<!--- let's name the select by its function and not lock in which column represents just in case --->
<cfselect size="1" name="DegreeSelect" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect>

That will display the nice english title, but store the DegreeID as the value. You can then reference the select by DegreeSelect no matter which field you land on as the value.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
I didn't mean to insult you, you asked a question and I thought I was answering it.

If anything you were simply unclear as to the point you were making.  If your point was that the getDegrees query needs the degreeID, then I absolutely agree.    But I can't even see where that query getDegrees is posted, except for the first post....  I just assumed degreeID was added to the query because we didn't hear about an error when loading the search form...

Eric, if you didn't put the degreeID into this query yet, please be sure it's there so that your select tag will show the degreeTitle and the value will be degreeID...

<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
     SELECT  DegreeTitle,  DegreeID
     FROM   careplanners_degrees
</cfquery>

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
I did not ask any questions. I had no ? marks in my comment. It was all statements. I was reemphasizing a point I had been making to Eric all along about the use of DegreeTitle over DegreeID because of this fact. Therefore, I am sorry it was unclear to you.

The fact that the query has not been shown as changed is my point as Eric continues to reflect that simply changing to DegreeID is not working; therefore, rather than assume, I chose to continue point that out rather than push the query had to be a certain way.

Glad you are now on my page.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
First of all -- thank you to gdemaria, mcvisa1, and _agx_ for the suggestions and input. I'm very grateful, and I am learning a great deal from this conversation. The client, Susan at Careplanners.net, asked me today: Where's my search form? And I said, I'm working on it. =) Susan's a cool lady and I have been trying hard to get her search form to her and her careplanners group. gdemaria, mcvisa1, and _agx_ -- you're swooping in to save the day. =) I wish there were some way to express my gratitude besides this small ExpertsExchange TEXTAREA input field.

gdemaria and _agx_,  after all these years of your help -- I gotta make you guys dinner, do your shopping, wash your cars or something. Not even joking. OK, I will respond to the above:

>>>I know I have not been coding in CF for a while, but trust me I am not totally clueless to the concepts of web development. :)

mcvisa1, I'm really glad to get your input. Personally, I love CF. It's fascinating and useful. I worry that fewer developers use it these days. A few weeks ago a friend of mine in DC joked that his organization was moving away from ColdFusion. "It's too efficient."

>>>The name of your select tag needs to be the ID fields... the way you have this, you are passing the ID into a variable FORM.DegreeTitle...    (see below, the value is DegreeID, but the NAME is DegreeTitle.)   It is the name of the tag that creates the form variable...  so those have to be the IDs...

Yes. I was wondering about this, before. So I tested it; I put DegreeID in the name and DegreeID in the value. Like so:

<cfselect size="1" name="DegreeID" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
</cfselect> 

<cfselect size="1" name="certificationID" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
</cfselect>

Open in new window


But I still get the wrong result with the CFSELECT code above. I search on a degree or a certification, and I get back all 622 records. Rather than the records that contain only the requested degrees and certs.

gdemaria and mcvisa1 pointed out:
>>If we want DegreeID, then this at a minimum must be done:

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeID, DegreeTitle
        FROM   careplanners_degrees
</cfquery>

I agree 100% and I did exactly that, so that template search.cfm knows where to get a value for DegreeID. I should have made this clear in a previous post.

>>I just assumed degreeID was added to the query because we didn't hear about an error when loading the search form...

Correct. But I should made that clear.

mcvisa1:
>><!--- let's name the select by its function and not lock in which column represents just in case --->
<cfselect size="1" name="DegreeSelect" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect>

Interesting point. I'll go ahead and set name = DegreeSelect.

Hmm. When I search on a degree or a certification, I'm still getting result: all records. Whether name = DegreeID or DegreeSelect. I believe the value attribute is what matters most here: value should be DegreeID. Template search.cfm passes value DegreeID to template searchquery.cfm. It is up to template searchquery.cfm to deliver correct output based on FORM input from template search.cfm.

I get the feeling I am missing something very obvious that has been pointed out to me before.

I'm going to look again at this code, tomorrow. I got only a few hours of sleep last night and am reaching the end of my effectiveness today.

Usually I don't post the full code of my templates because I don't expect experts to wade through many lines of code to access the code that matters to my question. I will go ahead and post the full content of my two templates: search.cfm and searchquery.cfm. I think I have paid attention to the advice I have been given here. (I hope so.) I've commented both templates thoroughly. These are the templates I am currently working with.

I'm going to review both templates very carefully in the clear light of morning. Thank you again, friends. I hope your Friday evening is relaxing. Peace.

Eric B
search.cfm:
<!-----
Name:        search.cfm
Author:      Eric Bourland, gdemaria, _agx_, mcvisa1
Description: search Careplanners
Created:     September - October 2011
ColdFusion Version 9
MS SQL Server 2005
----->

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeID, DegreeTitle
        FROM   careplanners_degrees
</cfquery>

<!--- this query selects certificationTitle from lookup table careplanners_certifications --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetCertifications">
        SELECT  certificationID, certificationTitle
        FROM   careplanners_certifications
</cfquery>

 <!--- this query selects practice from table lookup careplanners_practice --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetPractice">
        SELECT  practice
        FROM    careplanners_practice
        ORDER BY practice ASC
</cfquery>

<!--- this query selects StateName values from lookup table USCA_States --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>


<h2>Search Life Careplanners</h2>

<div class="boxf9edd0 width400px">

<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchCareplanners" style="margin-top:1px;">

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Degree" /> Degree:
<cfselect size="1" name="DegreeSelect" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect> 
</p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Certification:
<cfselect size="1" name="certificationSelect" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
          </cfselect> 
</p>

       
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Primary Practice:
<cfselect size="1" name="practice" value="practice" display="practice" multiple="no" query="GetPractice" queryPosition="below">
     <option value="">Search by Practice:</option>
     </cfselect></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Keyword" /> Keyword:
<cfinput type="text" name="keyword" size="30" /></p>

              
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Last Name" /> Careplanner Last Name:
<cfinput type="text" name="LastName" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by City" /> Careplanner City:
<cfinput type="text" name="city1" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by State" /> State:
     <cfselect size="1" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below">
     <option value=""> Select State: </option>
     </cfselect>  
</p>
<div class="align-center">
  <input type="submit" value="Search" />
</div>

</cfform>

</div>


<p><img src="/img/bronzestar.gif" width="14" height="13" alt="bronze star" /> <a href="/directory.cfm">List All Careplanners</a></p>

searchquery.cfm:
<!-----
Name:        searchquery.cfm
Author:      Eric Bourland, gdemaria, _agx_, mcvisa1
Description: search Careplanners
Created:     September 2011
ColdFusion Version 9
MS SQL Server 2005
----->

<cfset bgcolor="##ffffff">
<cfparam name="form.keyword" default="">
<cfparam name="form.LastName" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.DegreeID" default="">
<cfparam name="form.certificationID" default="">

<!--- is URL.search defined? --->
<cfif IsDefined("URL.search")>

<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">
SELECT
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
		FROM careplanners_has_degrees hd
		inner join careplanners_degrees d on hd.degreeID = d.degreeID
		WHERE hd.CareplannersID = cp.CareplannersID
		ORDER BY d.DegreeTitle
		FOR XML PATH( '' )), 3, 1000 )

,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
        FROM careplanners_has_certifications hc
        inner join careplanners_certifications c on hc.certificationID = c.certificationID
        WHERE hc.CareplannersID = cp.CareplannersID
        ORDER BY c.certificationTitle
        FOR XML PATH( '' )), 3, 1000 )

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif isDefined(form.degreeID)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)
</cfif>

<cfif isDefined(form.certificationID)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)
</cfif>

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

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

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

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

<cfif keyword IS NOT "">
AND (
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

<!--- check search recordcount --->
<cfif searchCareplanners.RecordCount GT 0> 

<cfoutput>
<div class="brownbox">

<p>You searched on these parameters:</p>

<p><strong>Careplanner Name:</strong> <em>#form.LastName#</em><br />
<strong>Degree:</strong> <em>#form.DegreeID#</em><br />
<strong>Certification:</strong> <em>#form.CertificationID#</em><br />
<strong>Primary Practice:</strong> <em>#form.practice#</em><br />
<strong>City:</strong> <em>#form.city1#</em><br />
<strong>State:</strong> <em>#form.state1#</em><br />
<strong>Keyword:</strong> <em>#form.keyword#</em></p>

<p class="align-center">Results are sorted by Careplanner Last Name, ascending A to Z.</p>
</div>
</cfoutput>

<p>&nbsp;</p>

<cfoutput query="searchCareplanners" group="LastName" groupcasesensitive="no">




<!--- begin careplanners display --->
<div class="contractor_display">

<!--- begin display_left --->
<div class="contractor_display_left">
<p><strong><cfif prefix IS NOT "">#searchCareplanners.prefix#</cfif>
#firstname#
<cfif middleinit IS NOT "">#middleinit#</cfif>
#lastname#<cfif suffix IS NOT "">, #suffix#</cfif></strong></p>

<p>Company or Organization: #company#<br />

Primary Practice: #practice#<br />

Primary Phone: #primaryphone#<br />

Email: #UserEmail#<br />

Web Address #website#<br />

Street: #street1#<br />
City: #city1#<br />
State: #state1#<br />
ZIP or Postal Code: #zip1#<br />
Country: #country1#<br />
Business Telephone 1: #phone1#<br />
Fax 1: #fax1#<br />
<cfif street2 IS NOT "">Business Address 2: #street2#<br /></cfif>
<cfif city2 IS NOT "">#city2#<br /></cfif>
<cfif state2 IS NOT "">#state2#<br /></cfif>
<cfif zip2 IS NOT "">#zip2#<br /></cfif>
<cfif country2 IS NOT "">#country2#<br /></cfif>
<cfif phone2 IS NOT "">#phone2#<br /></cfif>
<cfif fax2 IS NOT "">Fax 2: #fax2#<br /></cfif>

Information: #info#</p>



</div>
<!--- /display_left --->


<!--- begin display_right --->
<div class="contractor_display_right">
<p><strong>Degrees:</strong><br />
<cfoutput group="DegreeTitleList" groupcasesensitive="no">
#searchCareplanners.DegreeTitleList#
</cfoutput> 
</p>

<p><strong>Certifications:</strong><br />
<cfoutput group="certificationTitleList" groupcasesensitive="no">
#searchCareplanners.certificationTitleList#
</cfoutput> </p>




<!--- /display_right --->
</div>



<!--- end careplanners display --->
</div>

</cfoutput>
 


<cfelse>
<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/directory.cfm">Return to Careplanners Directory</a></p>
<p>&nbsp;</p>

</cfif><!--- /check search recordcount --->

</cfif><!--- /is URL.search defined? --->

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
As instructed by others, try with Val(). The documentation shows IsDefined(), but it may not apply here. I suggested earlier only because Val() was not working. It is probably better to stick with Val() since recommended by others. If it does not work, try explicitly comparing like:

<cfif Val(form.certificationSelect) is not 0>

Anyway, try the code below.
search.cfm:
<!-----
Name:        search.cfm
Author:      Eric Bourland, gdemaria, _agx_, mcvisa1
Description: search Careplanners
Created:     September - October 2011
ColdFusion Version 9
MS SQL Server 2005
----->

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeID, DegreeTitle
        FROM   careplanners_degrees
</cfquery>

<!--- this query selects certificationTitle from lookup table careplanners_certifications --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetCertifications">
        SELECT  certificationID, certificationTitle
        FROM   careplanners_certifications
</cfquery>

 <!--- this query selects practice from table lookup careplanners_practice --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetPractice">
        SELECT  practice
        FROM    careplanners_practice
        ORDER BY practice ASC
</cfquery>

<!--- this query selects StateName values from lookup table USCA_States --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>


<h2>Search Life Careplanners</h2>

<div class="boxf9edd0 width400px">

<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchCareplanners" style="margin-top:1px;">

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Degree" /> Degree:
<cfselect size="1" name="degreeSelect" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Search by Degree:</option>
          </cfselect> 
</p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Certification:
<cfselect size="1" name="certificationSelect" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Search by Certification:</option>
          </cfselect> 
</p>

       
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Primary Practice:
<cfselect size="1" name="practice" value="practice" display="practice" multiple="no" query="GetPractice" queryPosition="below">
     <option value="">Search by Practice:</option>
     </cfselect></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Keyword" /> Keyword:
<cfinput type="text" name="keyword" size="30" /></p>

              
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Last Name" /> Careplanner Last Name:
<cfinput type="text" name="LastName" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by City" /> Careplanner City:
<cfinput type="text" name="city1" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by State" /> State:
     <cfselect size="1" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below">
     <option value=""> Select State: </option>
     </cfselect>  
</p>
<div class="align-center">
  <input type="submit" value="Search" />
</div>

</cfform>

</div>


<p><img src="/img/bronzestar.gif" width="14" height="13" alt="bronze star" /> <a href="/directory.cfm">List All Careplanners</a></p>

searchquery.cfm:
<!-----
Name:        searchquery.cfm
Author:      Eric Bourland, gdemaria, _agx_, mcvisa1
Description: search Careplanners
Created:     September 2011
ColdFusion Version 9
MS SQL Server 2005
----->

<cfset bgcolor="##ffffff">
<cfparam name="form.keyword" default="">
<cfparam name="form.LastName" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.DegreeID" default="">
<cfparam name="form.certificationID" default="">

<!--- is URL.search defined? --->
<cfif IsDefined("URL.search")>

<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">
SELECT
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
		FROM careplanners_has_degrees hd
		inner join careplanners_degrees d on hd.degreeID = d.degreeID
		WHERE hd.CareplannersID = cp.CareplannersID
		ORDER BY d.DegreeTitle
		FOR XML PATH( '' )), 3, 1000 )

,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
        FROM careplanners_has_certifications hc
        inner join careplanners_certifications c on hc.certificationID = c.certificationID
        WHERE hc.CareplannersID = cp.CareplannersID
        ORDER BY c.certificationTitle
        FOR XML PATH( '' )), 3, 1000 )

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif Val(form.degreeSelect)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeSelect#">)
</cfif>

<cfif Val(form.certificationSelect)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationSelect#">)
</cfif>

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

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

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

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

<cfif keyword IS NOT "">
AND (
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

<!--- check search recordcount --->
<cfif searchCareplanners.RecordCount GT 0> 

<cfoutput>
<div class="brownbox">

<p>You searched on these parameters:</p>

<p><strong>Careplanner Name:</strong> <em>#form.LastName#</em><br />
<strong>Degree:</strong> <em>#form.DegreeID#</em><br />
<strong>Certification:</strong> <em>#form.CertificationID#</em><br />
<strong>Primary Practice:</strong> <em>#form.practice#</em><br />
<strong>City:</strong> <em>#form.city1#</em><br />
<strong>State:</strong> <em>#form.state1#</em><br />
<strong>Keyword:</strong> <em>#form.keyword#</em></p>

<p class="align-center">Results are sorted by Careplanner Last Name, ascending A to Z.</p>
</div>
</cfoutput>

<p>&nbsp;</p>

<cfoutput query="searchCareplanners" group="LastName" groupcasesensitive="no">




<!--- begin careplanners display --->
<div class="contractor_display">

<!--- begin display_left --->
<div class="contractor_display_left">
<p><strong><cfif prefix IS NOT "">#searchCareplanners.prefix#</cfif>
#firstname#
<cfif middleinit IS NOT "">#middleinit#</cfif>
#lastname#<cfif suffix IS NOT "">, #suffix#</cfif></strong></p>

<p>Company or Organization: #company#<br />

Primary Practice: #practice#<br />

Primary Phone: #primaryphone#<br />

Email: #UserEmail#<br />

Web Address #website#<br />

Street: #street1#<br />
City: #city1#<br />
State: #state1#<br />
ZIP or Postal Code: #zip1#<br />
Country: #country1#<br />
Business Telephone 1: #phone1#<br />
Fax 1: #fax1#<br />
<cfif street2 IS NOT "">Business Address 2: #street2#<br /></cfif>
<cfif city2 IS NOT "">#city2#<br /></cfif>
<cfif state2 IS NOT "">#state2#<br /></cfif>
<cfif zip2 IS NOT "">#zip2#<br /></cfif>
<cfif country2 IS NOT "">#country2#<br /></cfif>
<cfif phone2 IS NOT "">#phone2#<br /></cfif>
<cfif fax2 IS NOT "">Fax 2: #fax2#<br /></cfif>

Information: #info#</p>



</div>
<!--- /display_left --->


<!--- begin display_right --->
<div class="contractor_display_right">
<p><strong>Degrees:</strong><br />
<cfoutput group="DegreeTitleList" groupcasesensitive="no">
#searchCareplanners.DegreeTitleList#
</cfoutput> 
</p>

<p><strong>Certifications:</strong><br />
<cfoutput group="certificationTitleList" groupcasesensitive="no">
#searchCareplanners.certificationTitleList#
</cfoutput> </p>




<!--- /display_right --->
</div>



<!--- end careplanners display --->
</div>

</cfoutput>
 


<cfelse>
<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/directory.cfm">Return to Careplanners Directory</a></p>
<p>&nbsp;</p>

</cfif><!--- /check search recordcount --->

</cfif><!--- /is URL.search defined? --->

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
Eric, the only problem with your form-search is that your SELECT tag name still does not match the code you're using on your search page.

You need to change the CFSELECT tag names to DegreeID and certificationID as shown here...

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Degree" /> Degree:
<cfselect size="1" name="DegreeID" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
<option value="">Search by Degree:</option>
</cfselect> 
</p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Certification:
<cfselect size="1" name="certificationID" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
<option value="">Search by Certification:</option>
</cfselect> 
</p>

Open in new window


As you can see here in your search criteria, you are correctly using form.degreeID and form.certificationID as your variables.   You just need to match those names in your CFSELECT tag... no changes to this part of your code is required.


 
<cfif isDefined(form.degreeID)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)
</cfif>

<cfif isDefined(form.certificationID)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)
</cfif>

Open in new window



I recommend staying with the names degreeID and certificationID because these names match the value that will be in the variable (they are the IDs after all, so name them that way).   I am a firm believer that a developer should not require a secret decoder ring to figure out what value is in a variable... name the variable after the value that it contains and life will be simpler.






0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
@gdemaria: Please be consistent. In http:#36895202, you stated isDefined() was useless--"that really does nothing." Now, it is just fine. I do not disagree in principle on the variable name, but given this started because I showed the code properly using degreeTitle which was the variable being passed at the time and you changed to degreeID and that has been a point of pain for several posts trying to explain the issue, I advocated taking the name out of the equation as something to remember to change while the value decision is being worked out. That is clearly Eric's challenge here: every time one piece of code is changed, it is not being consistently reflected everywhere in the remainder of the code. Going back and forth on what you are suggesting does not help. All that does is increase the chances of this disconnect.

Anyway, I am done. It seems like even if I support your comments on code like Val(), then you change your opinion to the opposite. It is clear that you just want to cause conflict and that is not useful in helping Eric. Therefore, I am done posting as I believe the question is answered and anymore banter will strictly be for argument's sake and irrelevant.

@Eric, good luck with your project. Regarding http:#36895619, yes it is a shame. I am sad I am not in that world anymore. I use to manage multiple multi-million dollar ecommerce sites using CF and so was heavy into CFML. Now, not so much. :) I still have a copy of the installation of server and development tools in my garage, though the CDs have Macromedia on them...so you can guess how old they are. ;) A number of schools still use CF for teaching Web development and have the students maintain their Web sites in it; therefore, there is hope the new generation will have some love for CF.

Best regards and happy coding,

Kevin
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
thanks for pointing that out mwvisa1, I copied and pasted the wrong version of code... (there are so many in this thread!)   I had copied it from Eric's last post, but the change had not been made there there... I should have noticed.   You're correct, it would be best if we always posted updated versions so we don't get lost or confused.   But that is the only reason it appears that I am inconsistent, a simple copy and paste problem; sorry for any confusion that may have caused.

Here is the proposed full version...  I hope that I haven't missed any of the suggested changes...

Eric, when testing this, please be sure to include all changes in your test... the initial degreeID in the cfquery list for the cfselect, using the name degreeID in the cfselect and using val() in the cfif statement of the where clause with form.degreeID...



search.cfm:
<!-----
Name:        search.cfm
Author:      Eric Bourland, gdemaria, _agx_, mcvisa1
Description: search Careplanners
Created:     September - October 2011
ColdFusion Version 9
MS SQL Server 2005
----->

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT  DegreeID, DegreeTitle
        FROM   careplanners_degrees
</cfquery>

<!--- this query selects certificationTitle from lookup table careplanners_certifications --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetCertifications">
        SELECT  certificationID, certificationTitle
        FROM   careplanners_certifications
</cfquery>

 <!--- this query selects practice from table lookup careplanners_practice --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetPractice">
        SELECT  practice
        FROM    careplanners_practice
        ORDER BY practice ASC
</cfquery>

<!--- this query selects StateName values from lookup table USCA_States --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>


<h2>Search Life Careplanners</h2>

<div class="boxf9edd0 width400px">

<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchCareplanners" style="margin-top:1px;">

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Degree" /> Degree:
<cfselect size="1" name="DegreeID" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
 <option value="">Search by Degree:</option>
</cfselect> 
</p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Certification:
<cfselect size="1" name="certificationID" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
 <option value="">Search by Certification:</option>
</cfselect> 
</p>

       
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Certification" /> Primary Practice:
<cfselect size="1" name="practice" value="practice" display="practice" multiple="no" query="GetPractice" queryPosition="below">
     <option value="">Search by Practice:</option>
     </cfselect></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Keyword" /> Keyword:
<cfinput type="text" name="keyword" size="30" /></p>

              
<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by Last Name" /> Careplanner Last Name:
<cfinput type="text" name="LastName" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by City" /> Careplanner City:
<cfinput type="text" name="city1" size="30" /></p>

<p><img src="/img/smallsearch.gif" width="13" height="13" alt="Search Careplanners by State" /> State:
     <cfselect size="1" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below">
     <option value=""> Select State: </option>
     </cfselect>  
</p>
<div class="align-center">
  <input type="submit" value="Search" />
</div>

</cfform>

</div>


<p><img src="/img/bronzestar.gif" width="14" height="13" alt="bronze star" /> <a href="/directory.cfm">List All Careplanners</a></p>

searchquery.cfm:
<!-----
Name:        searchquery.cfm
Author:      Eric Bourland, gdemaria, _agx_, mcvisa1
Description: search Careplanners
Created:     September 2011
ColdFusion Version 9
MS SQL Server 2005
----->

<cfset bgcolor="##ffffff">
<cfparam name="form.keyword" default="">
<cfparam name="form.LastName" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.DegreeID" default="">
<cfparam name="form.certificationID" default="">

<!--- is URL.search defined? --->
<cfif IsDefined("URL.search")>

<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">
SELECT
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
		FROM careplanners_has_degrees hd
		inner join careplanners_degrees d on hd.degreeID = d.degreeID
		WHERE hd.CareplannersID = cp.CareplannersID
		ORDER BY d.DegreeTitle
		FOR XML PATH( '' )), 3, 1000 )

,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
        FROM careplanners_has_certifications hc
        inner join careplanners_certifications c on hc.certificationID = c.certificationID
        WHERE hc.CareplannersID = cp.CareplannersID
        ORDER BY c.certificationTitle
        FOR XML PATH( '' )), 3, 1000 )

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif val(form.degreeID)>
and exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)
</cfif>

<cfif val(form.certificationID)>
and exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)
</cfif>

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

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

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

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

<cfif keyword IS NOT "">
AND (
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

<!--- check search recordcount --->
<cfif searchCareplanners.RecordCount GT 0> 

<cfoutput>
<div class="brownbox">

<p>You searched on these parameters:</p>

<p><strong>Careplanner Name:</strong> <em>#form.LastName#</em><br />
<strong>Degree:</strong> <em>#form.DegreeID#</em><br />
<strong>Certification:</strong> <em>#form.CertificationID#</em><br />
<strong>Primary Practice:</strong> <em>#form.practice#</em><br />
<strong>City:</strong> <em>#form.city1#</em><br />
<strong>State:</strong> <em>#form.state1#</em><br />
<strong>Keyword:</strong> <em>#form.keyword#</em></p>

<p class="align-center">Results are sorted by Careplanner Last Name, ascending A to Z.</p>
</div>
</cfoutput>

<p>&nbsp;</p>

<cfoutput query="searchCareplanners" group="LastName" groupcasesensitive="no">




<!--- begin careplanners display --->
<div class="contractor_display">

<!--- begin display_left --->
<div class="contractor_display_left">
<p><strong><cfif prefix IS NOT "">#searchCareplanners.prefix#</cfif>
#firstname#
<cfif middleinit IS NOT "">#middleinit#</cfif>
#lastname#<cfif suffix IS NOT "">, #suffix#</cfif></strong></p>

<p>Company or Organization: #company#<br />

Primary Practice: #practice#<br />

Primary Phone: #primaryphone#<br />

Email: #UserEmail#<br />

Web Address #website#<br />

Street: #street1#<br />
City: #city1#<br />
State: #state1#<br />
ZIP or Postal Code: #zip1#<br />
Country: #country1#<br />
Business Telephone 1: #phone1#<br />
Fax 1: #fax1#<br />
<cfif street2 IS NOT "">Business Address 2: #street2#<br /></cfif>
<cfif city2 IS NOT "">#city2#<br /></cfif>
<cfif state2 IS NOT "">#state2#<br /></cfif>
<cfif zip2 IS NOT "">#zip2#<br /></cfif>
<cfif country2 IS NOT "">#country2#<br /></cfif>
<cfif phone2 IS NOT "">#phone2#<br /></cfif>
<cfif fax2 IS NOT "">Fax 2: #fax2#<br /></cfif>

Information: #info#</p>



</div>
<!--- /display_left --->


<!--- begin display_right --->
<div class="contractor_display_right">
<p><strong>Degrees:</strong><br />
<cfoutput group="DegreeTitleList" groupcasesensitive="no">
#searchCareplanners.DegreeTitleList#
</cfoutput> 
</p>

<p><strong>Certifications:</strong><br />
<cfoutput group="certificationTitleList" groupcasesensitive="no">
#searchCareplanners.certificationTitleList#
</cfoutput> </p>




<!--- /display_right --->
</div>



<!--- end careplanners display --->
</div>

</cfoutput>
 


<cfelse>
<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/directory.cfm">Return to Careplanners Directory</a></p>
<p>&nbsp;</p>

</cfif><!--- /check search recordcount --->

</cfif><!--- /is URL.search defined? --->

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
The search on Degrees and Certifications is working now. =)

I see what I was doing wrong with the cfselect and DegreeID. (Though am a little baffled because last night I <em>thought</em> I had changed cfselect name and value to DegreeID. Obviously I was missing something.)

I have one more problem with this application, which manifests after a search has been done. The "You searched on these parameters" message displays the integer values for Degree and Certification, which makes sense, since the CFOUTPUT is this:

<p>You searched on these parameters:</p>
<strong>Degree:</strong> <em>#form.DegreeID#</em><br />
<strong>Certification:</strong> <em>#form.CertificationID#</em>

It would be better to have the Titles rather than the IDs. However, I can't output #form.DegreeTitle# or #form.CertificationTitle# because neither DegreeTitle nor CertificationTitle is defined in scope FORM. (I tried. Didn't work. =) I also tried using a QoQ to derive valid values for #form.DegreeTitle# and #form.CertificationTitle#; and I tried playing around with using substring values DegreeTitleList and certificationTitleList in the OUTPUT. Also didn't work, for reasons I understand now after trying out these ideas.)

What road should I go down to get DegreeTitle and DegreeCertification to appear in the output?

Thank you very much to gdemaria and mwvisa1 (whose EE sobriquet I typoed multiple times in my last post -- sorry!). I really appreciate your time and expertise. Hope your Saturday is going well. =)

Peace.

Eric
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 333 total points
Comment Utility
You just have to add a query to lookup the title from the ID.   Do this at the end where you intend to display them..

select degreeTitle from degrees where degree_id= #form.degree_id#   (of course use your cfqueryparam and such)

Don't be concerned that adding an extra query will slow anything down, it won't - just another millisecond..
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
<ot>
    > I use to manage multiple multi-million dollar ecommerce sites using CF and
    > so was heavy into CFML. Now, not so much. :)

@mwvisa1 - Just curious, what technologies do you use now? I love CF but have also started using .net too.  There's lots of differences, but having picked up some java from using CF over the years, c# isn't as much of a culture shock as if I were learning php or something ;-)
</ot>
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
<cfquery name="getDegreeTitles" datasource="#application.datasource#">
SELECT DegreeID, DegreeTitle
FROM careplanners_degrees
WHERE DegreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.DegreeID#">
</cfquery>

<cfquery name="getCertTitles" datasource="#application.datasource#">
SELECT CertificationID, CertificationTitle
FROM careplanners_certifications
WHERE CertificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.CertificationID#">
</cfquery>

Working really well. I'll come back and close this question. Wooo! Thank you. =)

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Thank you gdemaria, mwvisa1, and _agx_. This solution makes a lot of sense. The client is going to be happy.

<ot>
mwvisa1: Thank you for contributing. I appreciate your time and expertise. If you hang around the ColdFusion forum here on EE, you'll run into my questions pretty often. (I always try to research and solve my own questions before I ask them here.) I've benefitted immeasurably from the experience, patience, and professionalism of gdemaria, _agx_, brijeshchauhan, and other experts. I hope I will see you around in the CF forum or elsewhere on EE. I think the CF forum will benefit from your experience. Also, I too am curious about the platform you are working with now.

At some point I need to start building applications in .NET or some other popular language. (I also need to be smarter about running my small web development business so that I earn more than a slightly-more-than-subsistence income.) I hope Adobe does well with ColdFusion X and revitalizes the CF platform.

This month, my ISP, hosting.com, chose to terminate my Windows 2008 datacenter server, with the message that datacenters are no longer cost-effective for them, and, to host a VPS with hosting.com, I will need to migrate to a cloud server at a cost of ~$600- 700 per month, which is outside the scope of my business budget. My offer to wash dishes and walk the dog in exchange for cloud hosting was ignored.

In order to preserve the ColdFusion platform on which I've built many of my clients' web sites, I am moving to a datacenter VPS at Viviotech.net. The people at Viviotech are involved in OpenBlueDragon development, and are part of a larger, supportive CFML community. Jordan Michaels and Matt Woodward have been especially generous with their support.

Apparently, at least, I've negotiated with hosting.com to maintain my MS SQL Server 2005 database account, where all my clients' data resides.

So, after I complete this careplanners task, I'm pulling up stakes at hosting.com and moving forty or so web sites and email hosting accounts to viviotech.net: a step-by-step task that I hope I have planned very carefully. =)
</ot>

Thank you again for your help. Enjoy the rest of the autumn weekend.

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
Hope I have divided points here appropriately. Thank you again. Eric
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Eric, I am glad you got that all sorted. It is always fun to get a page working. Woohoo, indeed!

<ot>My career path just took me more down the Java path from a development perspective; however, because of the work I did with CF, I ended up being asked to build and maintain large IIS farms for the same organization for several years; therefore, my previous knowledge of ASP and VB was expanded to include working with ASP.NET more. Therefore, I too found the same ease of picking up C# when it was released, but then again I learned to program with Visual C++. I am at a much smaller place now and they use VB.NET mainly with a few Java web services scattered about for some ERP-related apps integration. We have some PHP sites and I do not touch the code unless I am consulted for logic issues...</ot>
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I also realize I confused Accepted and Assisted solutions. It matters, the order in which one selects the Answer checkboxes.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Eric, that was my challenge with my Web consulting back in the day. CF hosting was too expensive --at least when you are not making the income to support it. For a while, NewAtlanta had a free CF server and I was toying with hosting through my own VPS loaded with that. With my move for work 10 years ago, things just got too hectic and could not maintain clients from my old home who all wanted the hands on local touch, which I could not longer provide.

So long story short, even at a reduced price, I could not justify the costs and my day job was keeping me plenty busy. :)

It appeared several years ago when I looked that the free Blue Dragon server was dropped, but you can always check as it worked pretty well as long as you had no need for the tags it did not support at the time...again the tag support for some of the more advanced CF features have been added over time, so if interested I would check now.

Developing database applications in .net is not bad at all and that is the beauty of CF in it's abstraction of the DB connection layer. With newer design principles and things like LINQ, you can probably get the same ease in ASP.NET if you have to move at some point.

And yes, I hope to see you around too. I tend to pop my head in here every now and again because of my passion for CF, but with the folks you mentioned around here i tend to sit back until something pops up that may be difficult from database perspective like FOR XML which I tthought was question here originally. So I respect the experts here enough to keep my ignorance to newer CFML from causing trouble, but can't resist a challenge; therefore, dangle a carrot and you may see me... Or come ask database specific questions in SQL zones. ;)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

6 Experts available now in Live!

Get 1:1 Help Now