Posted by Tom Rogers on 10/04/25 11:12

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> Here’s 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

[Back to original message]