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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
Last edited by batfastad on Tue Nov 28, 2006 6:27 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

one field, multiple records or one field + implode() .. possibly.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post 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
Post Reply