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

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.

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#">
    )

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.

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

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>

Select allOpen in new window

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.

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

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.

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>

Select allOpen in new window

>  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...

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

Select allOpen 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...

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

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>

Select allOpen 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>

Select allOpen 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:

Select allOpen in new window

Try checking the parameters using is defined. I suspect the if conditions are not being met, so you are only testing where 1=1.

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.

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>

Select allOpen 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?

>>>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>

Select allOpen 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.

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>

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>

> 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.

lol, ya...  I hear ya

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.

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?

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. :)

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>

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.

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>

Select allOpen 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? --->

Select allOpen in new window

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? --->

Select allOpen in new window

@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

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

<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>

<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

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

Hope I have divided points here appropriately. Thank you again. Eric

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>

I also realize I confused Accepted and Assisted solutions. It matters, the order in which one selects the Answer checkboxes.

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. ;)