Re: Multiple Select Field

FIND_IN_SET(str,strlist) Returns a value 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a comma (`,') character. mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 Robb Kerr wrote: > Please excuse the length and complexity of this post. I am facing a > complicated problem and need to find a solution. Also, I have posted this > on both PHP and MySQL boards so please overlook the possibly slightly > off-topic subject. > > I have got one table that contains personal information about users. > Another table consists of a list of many interests. This table is composed > of simply 2 fields – an ID and the interest. (see below) > > I can build an HTML form which includes a multiple select list out of which > the user can select their interests. I have the list configured so that it > displays the “interest” while the value stored is the “ID”. PHP stores this > entry as an Array which I can easily turn into a comma delimited list > before storing in the database. > > Here’s my problem. If I store a comma-delimited list in a text field in the > Users table, how do I query to determine anyone that has selected specific > interests? If I use a full-text search and someone selects “1” for their > search criteria, the query will return anyone who selects 1, 10, 11, 12, > 13, 14, etc. as their interest. > > This problem has surely occurred for others. It would affect how you store > newsletters for which one might subscribe, keywords in a clipart system, > interests the user has, anything from which you want your users to select > multiple entries from a long list of choices. > > Table = Interests > Fields = ID, int, auto-increment > Interests, text > > Table = Users > Fields = ID, int, auto-increment > Name, text > Address, text > Interests, text > > Thanx in advance for any help you might provide. If anyone knows of > anything written on configuring this type of system, please include a link. > > Robb > Digital IGUANA >