Look into doing select unions in your SQL book. Something like:
select *
from thetable
where thecol = 'blahblah'
union
select *
from thetable
where thecol = 'hi im happy'
.. etc..
In <[EMAIL PROTECTED]>, Michael Blair
([EMAIL PROTECTED]) in a fit of unbridled passion, wrote:
> 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.
>
------------------------------------------------------------------------------
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.
