Page 1 of 1

how to store a checkbox result into sql ?

Posted: Tue Jun 16, 2009 10:33 pm
by RiMMER
Hello there,

I'm new to this forum, so sorry in advance, if I do something wrong.

I've got a checklist on my page, which user can check and submit:

Code: Select all

<input type="checkbox" name="checkbox[]" value="foods" />
<input type="checkbox" name="checkbox[]" value="drinks" />
<input type="checkbox" name="checkbox[]" value="cold drinks" />
When they submit, I can get the data into the array:

Code: Select all

$categories = $_POST['checkbox'];
This defines into which categories a food belongs to.

So how do I store this definition into an sql database with the article name?

For example when user wants to store a "milk", which they select it belongs
to drinks and cold drinks, then $categories contains array of [0] => drinks [1] => cold drinks

So what query, please, should I send, to store the categories definition?

Thanks in advance!

RiMMER

Re: how to store a checkbox result into sql ?

Posted: Tue Jun 16, 2009 11:16 pm
by califdon
You would have to tell us how you intend to store the categories. It appears that you want to have a many-to-many relationship between foods and categories. That requires 3 tables: Foods, Categories, and Food_Category. If that's what you have, and if you have established primary keys for Foods and Categories, then for each Category that applies to a food, you would need one new record inserted in the Food_Category table, with the primary key values of the Food and of the Category as the 2 fields in that record.

Re: how to store a checkbox result into sql ?

Posted: Tue Jun 16, 2009 11:20 pm
by RiMMER
I have table categories, let's say:

id 1 | foods
id 2 | drinks
id 3 | sweets

And I want to add a new food into table foods, like this:

id 1 | bread | 1 // this is a category id it belongs to = foods
id 2 | tea | 2
id 3 | cocoo | 2
id 4 | chips and cola | 1,2
id 5 | sugar | 3

I will read it back by joining those two tables, but it doesn't matter now.

Re: how to store a checkbox result into sql ?

Posted: Wed Jun 17, 2009 12:47 pm
by califdon
RiMMER wrote:I have table categories, let's say:

id 1 | foods
id 2 | drinks
id 3 | sweets

And I want to add a new food into table foods, like this:

id 1 | bread | 1 // this is a category id it belongs to = foods
id 2 | tea | 2
id 3 | cocoo | 2
id 4 | chips and cola | 1,2
id 5 | sugar | 3

I will read it back by joining those two tables, but it doesn't matter now.
That's what I was afraid of. That won't work. The reason is that you cannot store multiple values in the same field (your chips and cola, above). You need a 3rd table, to establish a many-to-many relationship. Like this:

Code: Select all

+---------------+   +---------------------+   +----+----------------+
| Categories    |   | Foods-to-Categories |   | Foods               |
+---------------+   +---------------------+   +----+----------------+
| ID | Category |   | CatID  | FoodID     |   | ID | Food           |
+---------------+   +---------------------+   +----+----------------+
|  1 | Foods    |   |     1  |      1     |   |  1 | Bread          |
|  2 | Drinks   |   |     2  |      2     |   |  2 | Tea            |
|  3 | Sweets   |   |     2  |      3     |   |  3 | Cocoa          |
+----+----------+   |     1  |      4     |   |  4 | Chips and Cola |
                    |     2  |      4     |   |  5 | Sugar          |
                    |     3  |      5     |   +----+----------------+
                    +--------+------------+