Can you have multiple LIKE statements in ColdFusion / MySQL query? 1

[fatroman]

Hi, I'm running ColdFusion with a MySQL database.

I'm trying to run a multiple keyword search on designated columns in a single table. So, you can enter the requested terms on one page, and then the next page runs the query and spits out any matches.

I want to be able to search for LIKE matches, instead of exact matches. Anytime I run the query below, it works ok if you only enter a term in a single text box, but throws an error if more than 1 box is filled in.

<CFQUERY DATASOURCE="dataname" NAME="findmatch">
SELECT *
FROM #Member_Table#
WHERE 1=1

<CFIF #form.search_last# is not ""> Last_Name LIKE '%#form.search_last#%' AND </CFIF>

<CFIF #form.search_Organization# is not ""> Organization LIKE '%#form.search_Organization#%' AND </CFIF>

<CFIF #form.search_id# is not ""> MemberID = '#form.search_id#' </CFIF>

<CFIF #form.search_state# is not ""> State_Province = '#form.search_state#' </CFIF>

<CFIF #form.search_zip# is not ""> Zip = '#form.search_zip#' </CFIF>
ORDER BY #Member_Order#

</CFQUERY>

Thanks in advance for any help.

 

[spookie]

It seems that there is a problem with the formation of the query.Print out the query and see if the syntax is correct.

(BTW does the AND supposed to be at the beginning of the if clause rather than at the end?)

ie

<CFQUERY DATASOURCE="dataname" NAME="findmatch"> 
SELECT * 
FROM #Member_Table#
WHERE 1=1

<CFIF #form.search_last# is not "">AND Last_Name LIKE '%#form.search_last#%'  </CFIF>

<CFIF #form.search_Organization# is not "">AND Organization LIKE '%#form.search_Organization#%' </CFIF>
CFIF #form.search_id# is not "">AND MemberID = '#form.search_id#' </CFIF>

<CFIF #form.search_state# is not "">AND State_Province = '#form.search_state#' </CFIF>

<CFIF #form.search_zip# is not "">AND Zip = '#form.search_zip#' </CFIF>

ORDER BY #Member_Order#

</CFQUERY>

ps : code not tested

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[fatroman]

Spookie,

Thanks!

That was exactly the problem. I put AND at the beginning of the clause, and everything works great now.

Thanks so much.

Steve