Hi,

what is the correct way to save check box values in a mySQL database?

would it be

<input type="checkbox" name="apples" value="1" ... /> -- into apples field
<input type="checkbox" name="bananas" value="1" ... /> -- into bananas field
<input type="checkbox" name="oranges" value="1" ... /> -- into oranges field

or

<input type="checkbox" name="fruit[]" value="1" ... />

<input type="checkbox" name="fruit[]" value="2" ... />

<input type="checkbox" name="fruit[]" value="3" ... />

$fruit= implode(", ", $_POST['fruit']); --- insert string into fruit field

Is one method more preferable than others, if i am required to

1.show the checked values in an edit form and
2. be able to query the database field to return which fruit have been entered

Thanks

    With checkboxes in HTML, you need to give them the same name if they belong in a group. PHP also has a little oddity in that you need to name them with [] if you want them to appear as an array in your script, which you've got in your second example. This makes it a lot easier to work with, as you can loop over the array rather than lots of arbitrarily named variables:

    foreach($_POST['fruit'] as $fruit)
{
    // do something with each $fruit here
}

    A second point, I'd avoid putting the fruits into your DB as a comma delimited string. It's a bit messy to deal with later. For example, say Fred had the following fruits:

    • Banana

    • Apple

    • Orange

    • Pear

    • Pineapple

    If you store it in a single string, it will look like this:
    "banana,apple,orange,pear,pineapple"

    Now, how would you go about removing 'apple' from the list? It's not just a case of removing a single entry in a DB, you have to rebuild the entire string. Also, what happens if you need to search for anyone who likes 'apples'? If you just search for that word, you pick up pineapple too by mistake.

    It would be a lot better to have a separate table for your fruits, one for people, and a third for the connections between people and fruits, for example:

    People Table:
user_id, name
1, Fred
2, Tom
3, Dick
4, Harry

Fruits Table:
fruit_id, fruit
1, Apple
2, Banana
3, Pear
4, Strawberry
5, Orange
6, Pineapple

People_fruits Table:
user_id, fruit_id
1, 1
1, 2
1, 4
2, 2
2, 3
2, 4
3, 6
3, 1
3, 2
4, 1
4, 6

    It might look complex at first, but it's incredibly more powerful and easy to use.

      Hi Ashley,

      many thanks for taking the time to respond and for the suggestion to use a look up table. However, I have too many checkboxes in my application to make this possible, I think it would make my SQL a nightmare.

      So i guess the best way would be to use a comma delimited string despite its drawbacks. However I need to be able to show the values in the string in an edit form so the field can be updated... would you have any idea how to achieve this??

      Once again many thanks for your time.

        Dereksdontrun;11002198 wrote:

        However, I have too many checkboxes in my application to make this possible

        Why do you think that?

        Dereksdontrun;11002198 wrote:

        I think it would make my SQL a nightmare.

        You mean your SQL queries? If so, I think you'll find things are actually easier once you've normalized out your database like this. If that's not what you meant, then I guess I don't understand what your "SQL" is.

        Dereksdontrun;11002198 wrote:

        So i guess the best way would be to use a comma delimited string despite its drawbacks.

        No, the best way would be to normalize your DB schema (such as how Ashley modeled it above) so it's easier to work with.

          Write a Reply...