Searching a column containing a list against a form list

[DixieGal]

I have a form that allows users to select multiple items from a list.

form.hobbies = 1,5,3,6,8,2 (from a table pullup)
I want to be able to search a table of users where
each user has a column of "hobbies" also a list.

How can my select statement parse the table of
users and compare and select those users
who have at least one hobby in their record
that is in the selected form.hobbies?

I am a bit braindead on this one :)
Tx in advance.... I have the CF end
worked out, just need the SQL portion of it.


--
Tami
aka DixieGal

**************************
So it is that the gods to dno give all men gifts of grace - neither good
looks not intelligence nor eloquence...
--Homer, The Odyssey
**************************

[mxstu]

This isn't a good way to store your data. The best method, from both a data
modeling and performance perspective, would be to normalize the information.
You would have three tables:

USER
--------------
UserID
FirstName
LastName

HOBBY
------------
HobbyID
Title (Programming, Classic Cars, etc)

USER_HOBBY
------------------------
UserID
HobbyID

Each user hobby would be entered as a separate record in the USER_HOBBY table.
Like this:

UserID | HobbyID
------------------------------
22 | 1
22 | 5
22 | 3
22 | 6

... then to find users with at least one hobby that is in your list, run a
simple select statement using a where in .... clause

--- not tested. note - should use cfqueryparam
SELECT u.UserID, u.FirstName, u.LastName
FROM User u INNER JOIN User_Hobby uh ON u.UserID = uh.UserID
WHERE uh.HobbyID IN (#form.hobbies#)

[Dan Bracuk]

As mxstu said, your data structure makes it easy to load records, but very
difficult to use them for anything. But, it you gotta go with what ya got,
this will work.

<cfsetting requestTimeout="a very large number">
<cfloop list="form.hobbies" index = "hobby">
<cfquery>
select stuff from tableofusers
where hobbies like '%#hobby#%'
</cfquery>
something to store results so far.
</cfloop>


Originally posted by: Newsgroup User
I have a form that allows users to select multiple items from a list.

form.hobbies = 1,5,3,6,8,2 (from a table pullup)
I want to be able to search a table of users where
each user has a column of "hobbies" also a list.

How can my select statement parse the table of
users and compare and select those users
who have at least one hobby in their record
that is in the selected form.hobbies?

I am a bit braindead on this one :)
Tx in advance.... I have the CF end
worked out, just need the SQL portion of it.


--
Tami
aka DixieGal

**************************
So it is that the gods to dno give all men gifts of grace - neither good
looks not intelligence nor eloquence...
--Homer, The Odyssey
**************************

[DixieGal]

yea, I know. I was just being lazy <wink>. The more I
chew on it, the more of a no-brainer it is....

On a similar note that may be helpful to others,
I did find CF_LIST_COMPARE
which is a custom tag that does compare 2 lists. And
it is very nice... returns a list of all items in first list but
not in second, a list of all items in second but not first,
and a list of all items in both lists. Found it on MM site :)

THanks all... I will quit being lazy for now, and do it right
<wink>
Tami

--
Tami
aka DixieGal

**************************
So it is that the gods to dno give all men gifts of grace - neither good
looks not intelligence nor eloquence...
--Homer, The Odyssey
**************************
"Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
news:dj421n$7q5$1@forums.macromedia.com...
| As mxstu said, your data structure makes it easy to load records, but very
| difficult to use them for anything. But, it you gotta go with what ya
got,
| this will work.
|
| <cfsetting requestTimeout="a very large number">
| <cfloop list="form.hobbies" index = "hobby">
| <cfquery>
| select stuff from tableofusers
| where hobbies like '%#hobby#%'
| </cfquery>
| something to store results so far.
| </cfloop>
|
|
| Originally posted by: Newsgroup User
| I have a form that allows users to select multiple items from a list.
|
| form.hobbies = 1,5,3,6,8,2 (from a table pullup)
| I want to be able to search a table of users where
| each user has a column of "hobbies" also a list.
|
| How can my select statement parse the table of
| users and compare and select those users
| who have at least one hobby in their record
| that is in the selected form.hobbies?
|
| I am a bit braindead on this one :)
| Tx in advance.... I have the CF end
| worked out, just need the SQL portion of it.
|
|
| --
| Tami
| aka DixieGal
|
| **************************
| So it is that the gods to dno give all men gifts of grace - neither good
| looks not intelligence nor eloquence...
| --Homer, The Odyssey
| **************************
|
|
|
|
|