A very big thanks to CF Talk/Community regulars Ewika and Phil for
taking the time to help me off-list with this problem.
Here is the solution. It was all done using one SQL statement and LIKE.
<CFQUERY name="sresults" datasource="xxx">
SELECT * FROM tblMembers
WHERE 0=1
<CFIF IsDefined("membertype") AND #membertype# NEQ 0>
OR member_type = #form.membertype#
</CFIF>
<CFIF IsDefined("form.company") AND #form.company# NEQ "">
OR company_name LIKE '%#form.company#%'
</CFIF>
<CFIF IsDefined("form.coating_search") AND form.coating_search EQ
1><!--- If the user selected a coating search option then check for
coatins --->
<CFLOOP list="#form.coatings#" index="i">
or ',' & coatings & ',' LIKE '%,#i#,%'
</CFLOOP>
</CFIF>
ORDER BY company_name
</CFQUERY>
The section in question is the IsDefined("form.coating_search").
SO, the SQL statement adds the , and , around the variable list being
searched and the list element that is being searched for.
Let's break down the SQL:
------
OR ',' & coatings & ','
This needs to be added to account for the element being the First or
Last element in the coatings field.
Recall that this field contains data of the form:
Example 1) 1,14,20,100
Example 2) 5,7,10
In the first example, searching for ,1, would not result in a hit
although the element 1 IS contained in the field.
We would have to search for 1,
To get around this, we make the element that we are searching for ,1,
artificially by wrapping the value with extra commas.
We are using LIKE so it does not matter if the value becomes ,,1, or
,,,,1,,,,,
it will still be found.
As another example, if we were searching for 10 using the second data
example we would search for
OR ,10 LIKE ,10,
Which would not produce a hit for the search.
So by using the line
OR ',' & coatings & ','
we change the search to:
OR ,,10, LIKE ,10,
You see why it's necessary? :)
Let's try to look for a value in the middle and see what would be
searched for. Taking the Second example, let us search for 7
Remember the code is
OR ',' & coatings & ','
SO the statement would become
OR ,,7,, LIKE ,7,
When the SQL is executed.
The % % signs are standard MS Access Wildcards.
And that's all there is to it. The statement may have become more
complicated if there were further form fields to search for, but with
only three fields it was manageable with just a series of OR statements.
Note that WHERE 0 = 1 at the start of the SQL Statement prevents the SQL
from returning all the values regardless of any criteria yet still
allowing us to use further OR statements. It forms the first element of
our criteria to allow us to use AND ,OR,NOT etc. etc.
I would also like to add that one should avoid finding oneself in this
situation in the first place. As a rule it is bad to store comma
delimited lists of values which are linked to another table in one
field. It would have been FAR better if the database structure had been
tblMembers:MemberID,Member_Name,Member_Type...etc. etc.
tblCoatings:coatingID,CoatingDescription
And then tblMemberCoatings Which would have stored the MemberID and the
CoatingID.
Had this been done this entire situation would have been avoided.
***Normalisation of data is extremely important when designing the
database.***
I really hope this helps someone now or in the future, because it
certainly helped me and I learned a thing or two about SQL :)
Thanks again to Phil and Ewika!
*slides them both fresh,warm blueberry muffins*
-Gel
-----Original Message-----
From: Angel Stewart [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 12:01 PM
To: CF-Talk
Subject: SQL: Comparing elements in a list to comma delimited data in a
table field
Hello all,
I want to take a comma delimited list created in a Search form, and
check to see if any of the elements in that list is contained in a field
in the database which is also comma delimited in a compound CFQUERY
statement.
SO...form.coatings = "1,5,10,13"
The field coatings in the database contains "1,3,14".
So therefore that record will be a part of the resultset.
The next record coatings field may contain "7,8,9" and therefore should
NOT be a part of the resultset.
The SQL I tried was:
<CFQUERY name="sresults" datasource="napca">
SELECT * FROM tblMembers
WHERE 0=0
<CFIF IsDefined("membertype") AND #membertype# NEQ 0>
AND member_type = #form.membertype#
</CFIF>
<CFIF IsDefined("form.company") AND #form.company# NEQ "">
AND company_name LIKE '%#form.company#%'
</CFIF>
<CFIF IsDefined("form.coating_search")><!--- If the user selected a
coating search option then check for coatins --->
<!--- Loop through every coating that the user multiselected on
the search form --->
AND coatings CONTAINS '#ListFirst(form.coatings)#'
<!---DEBUGGING <CFLOOP list="#form.coatings#" index="coating">
OR coatings = '#coating#'
</CFLOOP> DEBUGGING --->
</CFIF>
</CFQUERY>
I was just testing the correct syntax with just checking for one element
of the form field list. But I keep getting syntax errors.
How can I accomplish this??
-Gel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
