I didn't state my question all that clearly, but the problem isn't querying
the form field (which is a list if multiple items) are shown ("IN" does take
care of this), the problem is testing it against a list that is in a
database field.
Example:
Data in "Category" database field: 'internet', 'coldfusion', 'html'
Form field to check for: 'internet'
'internet' is a category item for this person, but unless the form field
matches this exactly ('internet', 'coldfusion', 'html')
The query returns nothing.
The case I have a solution for and folks have provided alternative solutions
for is the opposite case.
Data in "Category" database field: 'internet'
Form field to check for: 'internet', 'coldfusion', 'html'
This will search each item individually for a match in the database,
returning matches.
So a form field list searching for an individual match in the database
works; however, a single or list form field searching the database with a
list as its entry won't work.
Anymore ideas? There was another post today on the same question. So knock
off 2 birds with one stone!
Thanks,
Michael W. Blair
Web Applications Developer
Canyon WebWorks
An Arizona Internet LLC Company
http://www.canyon.net
[EMAIL PROTECTED]
PHONE: (520) 773-9059
FAX: (520) 773-4945
-----Original Message-----
From: Byron M [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 13, 2000 9:07 PM
To: [EMAIL PROTECTED]
Subject: RE: searching a database list
I think this is what you have. A field in your db that has the expertise in
a list. If this is the case then utimately you want to design your DB
differently. I would create another table with something like a UserID and
category, and then use a JOIN in your query. But...
This might work in your case, or some variation...
<cfquery name="getResults" datasource="expertise">
SELECT * FROM projects
WHERE 1=1
<CFLOOP LIST="#Form.SelectExpertise#" INDEX="tmpVar" DELIMETERS=",">
OR category LIKE #PreserveSingleQuotes(tmpVar)#
</CFLOOP>
</cfquery>
-----Original Message-----
From: Michael Blair [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 13, 2000 11:05 PM
To: [EMAIL PROTECTED]
Subject: searching a database list
Need some help on this one. I have a select field ("expertise") that
folks can select multiple items from and then upon submittance are
submitted into the databaseThen there is a search form to search on these
database fields
This is my search:
<cfquery name="getResults" datasource="expertise">
SELECT * FROM projects
WHERE category IN (#PreserveSingleQuotes(FORM.SelectExpertise)#)
</cfquery>
This does not find an entry within the database such as ('internet', 'html',
'coldfusion')
I give the option for the users to select numerous options but unless
they select the exact same expertise entered into the database it
returns nothing. If they are an expert in internet and that is within
their list I want that returned. Is there a way to do this if the
database already has these fields are do you have to enter them each
individually into their own row?
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
