Page 1 of 1

[SOLVED] Storing checkbox data in MySQL with serialize()

Posted: Tue Nov 28, 2006 3:54 pm
by batfastad
Hello everyone

I'm gradually converting our databases from FileMaker into MySQL by adding more and more features to our intranet interface.
I'm fairly new to MySQL but I've been using PHP for a couple of years now.

My question relates to the way that you can store checkbox data in MySQL.
In FileMaker if you have a field that can have multiple options via checkboxes, then each one is stored on its own line as text in that field.

Because I have a field that can have up to 20 possible options checked, what's the best way of storing this data in MySQL?

I don't really want to have 20 separate fields - one for each checkbox.
The checkboxes will always be manipulated as an array in PHP, so is it a good idea for me to use PHP's serialize() function to store the contents of the array in one MySQL field.
Then when accessing the data all I need to do is unserialize() and I have the data straight as an array.

The only downside to doing this is that it's probably not best practice in MySQL - as the data is being stored in a format that can only be used by PHP.
Also there is some storage overhead with serialize() - with the various square brackets newlines and other formatting that it adds to the data.


Any suggestions / opinions on this one?

Thanks

Ben

Posted: Tue Nov 28, 2006 4:06 pm
by feyd
one field, multiple records or one field + implode() .. possibly.

Posted: Tue Nov 28, 2006 6:26 pm
by batfastad
Ah yes. Implode would actually work better.

At the moment when accessing the filemaker data with PHP I use implode based on the newline character as FileMaker puts each item on it's own row.
So that's pretty easy to implement.

Plus it doesn't have the storage overhead of serialize.
And it's much neater.


Thanks for the suggestion!

Posted: Tue Nov 28, 2006 6:32 pm
by feyd
Be aware that searching will be a bit more complicated if you use a single field and only one record for multiple check boxes. Not to mention the limited amount of storage.

Typically it's suggested that you use multiple records, one per check box. It may, often does, require a separate table so as to avoid duplicating data too much.

If you still wish to continue using a single record-single field variant, then use a delimiter that's compatible with FIND_IN_SET().. i.e. a comma.

Posted: Tue Nov 28, 2006 7:33 pm
by batfastad
Yeah I think an additional table will be the way to go. Eventually.
For the first few weeks though I'll just be looking to get the data out of filemaker and into SQL as quickly as possible for people to start using the system.

Thanks for all the info!

Ben