Mysql and array's

datona

I was wondering if there is anyone that can guide me in the right direction.

I have a table in the database that only has three columns: client_id,email,client_preferences

client_preferences consists of an array created from multiple checkboxes saved as an array with variables like:
apples,oranges,banana,peach,plum

I am attempting to construct form and a query that has a list of checkboxes so that I can email all clients that say like banana and oranges.

I hope that I have explained this correctly.

drawmack

if you had normalized properly you wouldn't have this problem.

Make a table called preferences that contains all possible selections.

Then make a table called client_to_preference that contains id, client_id, pref_id.

that makes the query a very simple join.

datona

I actually do have a table that has two fields:
pref_id (autoincrement)
pref_name varchar(255)

So you are stating that I would creat the table: client_to_preference

And then if a client chooses "apple" and "orange", then that should create TWO lines in the database?

Then how would I query that to send an email only ONE email to people who choose say: "apples" and "oranges"?

drawmack

select distinct(a.*) from users_table as a, prefs_table as b, users_to_prefs as c where a.id = b.userid and b.pref_id = c.id and c.name in ('apples','oranges')

datona

Thanks drawmack, I'll give that a try.

So when I submit the form that would email all clients where I have selected specific fruits (again, there are checkboxes) like:
<input type="checkbox" name="fruit[]" value="Pear">
<input type="checkbox" name="fruit[]" value="Peaches">

I would you just implode the selected 'fruits' like:
$fruits = implode(",", $fruits);

Then would I use the variable '$fruits' to use in the query like:

select distinct(a.*) from users_table as a, prefs_table as b, users_to_prefs as c where a.id = b.userid and b.pref_id = c.id and c.name in ($fruits)

But you give an example of ('apples','oranges')
How could I take my array and insert it into that query?
Thanks again for all your help! I really appreciate it.