2

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..
Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
Eric T
  • 944
  • 1
  • 11
  • 26

3 Answers3

7

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 :)

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • Thank you Dan, I definitely need to improve my understanding of Data Structures & normalizing dbs - I keep looking for shorthand fixes but I guess that's just avoiding the real issue (I don't know much about data structuring) – Eric T Jul 22 '11 at 01:12
  • Dan - I'm confused about the JOIN statement. I've been doing some reading about the JOIN & OUTER JOIN statements and don't understand how to apply them here. Could you provide a sample Sql statement for printing out checked/unchecked amenities for a property? – Eric T Jul 22 '11 at 18:48
  • 1
    `SELECT amenities.*, property_amenities.property_id FROM amenities LEFT OUTER JOIN property_amenities ON amenities.id = property_amenities.amenity_id WHERE property_amenities.property_id = 7`. The rows with a value for `property_id` are the checked boxes, the rows with a NULL for `property_id` are the unchecked boxes. – Dan Grossman Jul 22 '11 at 19:19
  • @DanGrossman I know this is very old, but I had to change the "WHERE" in your query to "AND" in order to return the null values – DasBeasto Jul 02 '17 at 20:49
1

If they are not mutually exclusive; you can use bitwise operators to store them.

field = 0;
if($unity_features_selected == true) {
  field |= (1<<0);
}
if($community_features_selected == true) {
  field |= (1<<1);
}
if($utilties_included_selected == true) {
  field |= (1<<2);
}

then store field as an unsigned int. This lets you query for (ex. unity_features) such as 

select * from <table> where field&(1<<0);

Or community features:

select * from <table> where field&(1<<1);
Suroot
  • 4,315
  • 1
  • 22
  • 28
  • Would whomever dinged me please give information on why I was dinged? – Suroot Jul 22 '11 at 00:57
  • Probably for not mentioning relationships or "just using multiple columns" ;-) I have a +1 to balance, but it could be a more rounded answer. Often times munging like this is done "as a premature optimization" and not as a well thought-out decision. (I'm not saying this approach isn't good; and indeed I use it in *some very particular cases* where such is the encoding of the data -- a good bit comes down to the actual *semantics* of the usage.) In any case, this still doesn't really address the "how to many it easy" bit. –  Jul 22 '11 at 00:59
  • Did you look at his webpage? There are not 3 checkboxes, but 31 checkboxes, and given the application, that's likely to change. He's also going to want to do normal queries against these individual checkboxes when people search for properties. There's no reason to try to pack a normal relationship into an integer field like this. You were downvoted for giving poor advice. – Dan Grossman Jul 22 '11 at 01:02
  • @Dan Grossman That requires more knowledge about the data-model than the question provides... but generally correct. –  Jul 22 '11 at 01:03
-3

1) php serialize and unserialize (http://php.net/manual/en/function.serialize.php ) will help you keep arrays or objects or any other structural data in db.

2)use array format for input's names like described here: Submitting a multidimensional array via POST with php

3)remember that checkboxes unchecked won't be send at all, so always make one hidden input field for every checkbox field. That will ensure, that unchecked checbox will be sent as false. Here is an example: Post the checkboxes that are unchecked

So reasuming - you could sent that way thre arrays, with falues for all checkboxes (1 or 0), and store them in three fields as serialized objects.

Community
  • 1
  • 1
Luigi
  • 866
  • 13
  • 34
  • Why are you suggesting storing basic relationships as serialized PHP arrays in a single column, making the individual relationships non-queryable, non-updatable and non-deletable? Database normalization 101 is that fields should be single-valued. This database is not a special case that presents a reason to ignore those norms. – Dan Grossman Jul 22 '11 at 01:00
  • You're right, a new tables should be created, just like you posted. My propositon would be good, but only if these checboxes wouldn't be used for sql queries other then getting whole field to php. – Luigi Jul 22 '11 at 01:04
  • Serialized data is definitely to be avoided... it's a real nightmare when actually working with that data. – Steve Hill Jul 22 '11 at 12:04