[RESOLVED] Searching stored checkbox array

[adi]

Helo,

I am storing a checkbox array into a table as a string like this:

 ____________________
|client_id   |  preferences  |
 =================
|    1          |     3,4,6,5,8    |
|    2          |     1,2,3,5       |
|    3          |     3,5,8,10     |
 ==================

My problem is : How do i search for clients with a specific combinations of checked preferences?
For example: i need to search for clients who checked : 3, 5 and 8. In this case, the query should select client id 1 and 3 .
Is storing the checkbox array as a string the best solution?

Thank you.

[sfullman]

are you storing these as a string? IF you can make sure to put the values into the database numerically ordered from 1 to infinity, and you need to match a SPECIFIC combination of preferences, then just index the field and the search will work just fine. for example

(form)
[ x] likes apples
[ ] likes banans
[ x] likes grapes
[ x] likes kiwis

assume that form is passed as $preferences, then you can just do:

$query="select * FROM mytable where preferences='".implode(',',$preferences)."'";

if you need to be fuzzy about the selections, then you'll need to normalize you preferences into a 2nd table for that person e.g.

table: user_preferences
field: userid (id of the user)
field: preferenceid (id of the preference

you could then do:
$list="select preferenceid from user_preferences where userid='$thisuserid'";

loop through list then and "weight" the relevance based on # of preferences that match or don't match - that will require thinking of course 🙂

Samuel

[adi]

Thank you sfullman, i think the second solution is best for me.