php - What's the best way to store Checkbox Values in MySQL Database?

Question

First, take a look at this page as an example: Click here

If you view the 'Amenities' section, there are 3 sets of checkboxes (Unit Features, Community Features and Utilities Included in Rent).

My Question Is: How can I use PHP to make 3 array variables (e.g. $unit_features, $community_features and $utilities_included) to store which boxes are/are not checked into 3 respective fields in a table?

More importantly: How do I pull the data out of the table in the same array format so that specifics can be viewed/edited/deleted?

• Note, I've tried countless times to do this by having separate fields in the table (as tinyints) - but it gets bulky and isn't elegant at all...I even thought of making an object class but failed yet again..

Answer 1

You have a many-to-many relationship between properties and amenities. To model this, you need a separate table, not a variable number of columns.

There is one table which stores your properties.

INSERT INTO property (id, address, square_footage...) VALUES (111, '123 Main St', 1234...)

There is one table which stores all possible amenities.

INSERT INTO amenities (id, type, description) VALUES (222, 'Unit Features', 'Air Conditioning');

For each amenity a property has, insert one row into the table relating these two:

INSERT INTO property_amenitities (property_id, amenity_id) VALUES (111, 222);

When you want to know what amenities a specific property has, just SELECT all rows from this table for that property's key. When you want to print out checkboxes for all amenities, SELECT from the amenities table and do a LEFT OUTER JOIN to the property_amenities table. Those rows with null values from the property_amenities table are the boxes which are unchecked.

Related reading. You should pick up a book on relational databases from your local BORDERS before they go out of business :)