May be simple, but I'm not getting it.

[popeus]

I need to query a list against a list in the db field.

Example:
<cfqery name="MyQuery" datasource="application.ds">
select MyField
from DbTable
where cat,dog,fish,bird LIKE '%form.cat,form.dog%'
</cfquery>

The above is a rough example, but I have a select box on a form where you can select multiple criteria. For instance
<select name="animal" size="4" multiple>
<option value="cat">cat</option>
<option value="dog">dog</option>
<option value="fish">fish</option>
<option value="bird">bird</option>
</select>

If you select only one of the options the query works. If you select more than one option you get 0 results.

I need to do this at the query level and not loop over the results.

Any help would be greatly appreciated.

Thank you

 

[GUJUm0deL]

What's the column name for the animals? Is it cat, dog, fish, bird, etc or animal?



____________________________________
Just Imagine.

http://www.imajinarts.com

 

[popeus]

The column name would be animal. With the values of that column being row upon row, of comma separated values. In each row, there may only be one entry in the column for example cat or it may be cat,dog,bird

 

[GUJUm0deL]

So you can just loop over the results and insert into the dB. I'm assuming you want to insert into the dB, correct?

Something like this would work:

<cfloop index="[b]GM[/b]" list="#FORM.Animal#">
  INSERT INTO myTable (Animal)
  VALUES ([b]'#Gm#'[/b])
</cfloop>

The above would loop over the list FORM.Animal and insert a new record for each.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[popeus]

No, I'm searching the db.

 

[GUJUm0deL]

See if this works:

  SELECT Animal
  FROM   myTable
  WHERE  Animal IN ('#FORM.Animal#')

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[popeus]

No, same results as the other way. Select one option in the form and the query works, select two options and get 0 results.

 

[imstillatwork]

does IN() work with non-integers?
try something like so if it's just a simple search.

SELECT Animal
FROM myTable
WHERE 0=0
<cfloop list="#FORM.Animal#" index=i>
OR Animal LIKE '%#i#%'
</cfloop>

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

popeus, are you saying that the values in the MyField field of the DbTable table are like this

dog
cat
dog,cat
cat,bird
dog,bird,cat

if you, you have a much larger problem than a simple IN (list) can solve

kevin, yes, IN (list) works with any datatype -- strings, numbers, dates

r937.com | rudy.ca

 

[popeus]

The column name in the dbtable would be Animal. For each row of records that field may contain only cat or could contain cat,dog or it could contain cat,dog,bird or cat,dog,bird,fish. So that field could contain a single word or a comma separated list of words.


I need to be able to select multiple selections from one select box on the search form. (Whenever you select multiple choices in a form a comma separated list is automatically created)

So basically I have a form with a with a multiple select box like so:
<select name="animal" size="4" multiple>
<option value="cat">cat</option>
<option value="dog">dog</option>
<option value="fish">fish</option>
<option value="bird">bird</option>
</select>

Someone could select all four or they might just select one. If they select more than one a comma separated list is going to be created. Lets say they select cat and fish. They click the search button.

Now I have a list "cat,fish". The query on the action page needs to query the database Table that contains the column Animal. The Animal column may contain just cat or cat,fish or cat,fish,dog or cat,fish,dog,bird

If the column contains cat or if it contains cat,fish or if it contains cat,fish,bird or if it contains cat,fish,dog,bird I need to present that record to the end user, because it contains at least one of the criteria, the end user selected on the search form.

 

[imstillatwork]

You'll need to 'normalize' your data, and read up on 1-to-many relations.

you'll need a new table for animal_types, and a new table that will join animal_types to 'myTable'
so to do this you need three tables.






Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!