[PHP] Multiple Select Field
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. Heres 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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.
Heres 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Multiple Select Field
Hi, Monday, April 4, 2005, 2:58:36 AM, you wrote: RK Please excuse the length and complexity of this post. I am facing a RK complicated problem and need to find a solution. Also, I have posted this RK on both PHP and MySQL boards so please overlook the possibly slightly RK off-topic subject. RK I have got one table that contains personal information about users. RK Another table consists of a list of many interests. This table is composed RK of simply 2 fields an ID and the interest. (see below) RK I can build an HTML form which includes a multiple select list out of which RK the user can select their interests. I have the list configured so that it RK displays the interest while the value stored is the ID. PHP stores this RK entry as an Array which I can easily turn into a comma delimited list RK before storing in the database. RK Heres my problem. If I store a comma-delimited list in a text field in the RK Users table, how do I query to determine anyone that has selected specific RK interests? If I use a full-text search and someone selects 1 for their RK search criteria, the query will return anyone who selects 1, 10, 11, 12, RK 13, 14, etc. as their interest. RK This problem has surely occurred for others. It would affect how you store RK newsletters for which one might subscribe, keywords in a clipart system, RK interests the user has, anything from which you want your users to select RK multiple entries from a long list of choices. RK Table = Interests RK Fields = ID, int, auto-increment RK Interests, text RK Table = Users RK Fields = ID, int, auto-increment RK Name, text RK Address, text RK Interests, text RK Thanx in advance for any help you might provide. If anyone knows of RK anything written on configuring this type of system, please include a link. RK Robb RK Digital IGUANA Create a third table which links the other two, it will make life easier for searches and stats and be a lot quicker. table = user_interests fields userid int, interestsid int primary index would be on both fields -- regards, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
