Posted by Marek Kilimajer on 09/29/52 11:12

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
>

[Back to original message]