SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 39
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advice on dealing with large amount of checkbox data

    I am creating an accommodation site and need to include quite a lot of information about each accommodation listing especially self-catering.

    Currently I have a long form with check boxes for the following:

    Electricity included
    Electricity by coin operated meter
    Electricity charged separately,
    Heating included
    Heating charged separately
    Bed linen provided
    Bed linen available at extra cost
    Towels provided
    Off road parking
    TV
    Video
    Microwave
    Iron
    Drying facilities
    Tumble dryer
    Cot
    Children welcome
    On-site shop
    On-site food take-away
    on-site dog
    Children’s play area
    Indoor swimming pool,
    Outdoor swimming pool
    and there are more........

    My current thinking is I will need a separate field for each of these items in my database - well this is the only way I know how to store the information.

    However I am wondering if there is a better way to store the information?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    Always learning viveknarula's Avatar
    Join Date
    Mar 2006
    Location
    INDIA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you can store all these values with an delimiter like comma (,) and store them in one field in the database.

    This way you can add new checkboxes later on without needing to create an new field in the database. However you need to pre-determine the sequence of the checkboxes so that you can use correct value for correct checkbox while retrieving and showing the values.

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply - can you elaborate on this please?

    If I stored them all in one field, would the value look like an array and would I just use boolean values to indicate whether a feature was included or not e.g [1,0,1,1,1,0,0,0,]

    Or would it be best to store a meaningful string for each and to only include details of those facilities that are chosen?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  4. #4
    Always learning viveknarula's Avatar
    Join Date
    Mar 2006
    Location
    INDIA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chuckylefrek View Post
    Thanks for the reply - can you elaborate on this please?

    If I stored them all in one field, would the value look like an array and would I just use boolean values to indicate whether a feature was included or not e.g [1,0,1,1,1,0,0,0,]

    Or would it be best to store a meaningful string for each and to only include details of those facilities that are chosen?
    Well it depend upon you how would you like to store. However in my opion you should go for 1 and 0, if an checkbox is checked then 1 and if not then 0.

    Of course, the value that will save in the database will be as 1,0,1,1,1,0,0,0,... you need to explode this value by delimiter (,) while showing the values. and you know the sequence of the values corresponding to the checkboxes. with exploded values in array format you can easily show the checkboxes.

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again.

    I am a little concerned that this method of storing the data is very reliant on the sequence of values. If at a later date one of the checkbox options was removed, this could mess up the sequence. Is there a way to store all the data in one field but have it more meaningful?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  6. #6
    Always learning viveknarula's Avatar
    Join Date
    Mar 2006
    Location
    INDIA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am more concern about the relation of one value to an checkbox. Say, the first checkbox's value should relate to the first value of our comma separated string. If an checkbox has been removed then our values will come in that sequence only ( will not have the value for the removed checkbox)

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Morning,

    If you don't plan on doing any intense reporting on these fields, you could store them as a serialized array within the database.

    This would ensure you could keep the key / value pairs and allow PHP to use the data natively.

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Morning Anthony

    I certainly hadn't planned any intense reporting so it sounds like I should make a coffee and read up about serialized arrays - funny I am sure I used one of those the other day for something else

    Is this a serialized array?

    Code:
    $accommodationTypeFriendlyNames = array('hotel'=>'Hotel', 
    						'guestHouse'=>'Guest House',
    						'bedandbreakfast'=>'Bed and Breakfast',
                                                    'other'=>'Unclassified Accommodation Type');
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Nope, that's an associative array.

    Here you go.

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    Bathroom] => 1
                
    [TV] => 0
                
    [Dog] => 1
            
    )

        [
    1] => Array
            (
                [
    Bathroom] => 0
                
    [TV] => 1
                
    [Dog] => 1
            
    )

        [
    2] => Array
            (
                [
    Bathroom] => 
                [
    TV] => 0
                
    [Dog] => German Shepard
            
    )

    )
    a:3:{i:0;a:3:{s:8:"Bathroom";i:1;s:2:"TV";i:0;s:3:"Dog";i:1;}i:1;a:3:{s:8:"Bathroom";i:0;s:2:"TV";i:1;s:3:"Dog";i:1;}i:2;a:3:{s:8:"Bathroom";N;s:2:"TV";i:0;s:3:"Dog";s:14:"German Shepard";}} 
    Serializing the array converts it into a easily transportable string, which when needed, you can unserialize back into an array.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Anthony

    Couple of questions:

    Just out of interest, what would the implications have been if I had used a separate field for each feature in the database and used TINYINT(1)? Would this seriously affect the perfornance of the database if I had say 5000 accommodation listings?

    I just thought of an alternative in that I could have a separate features table and a features to accommodation linking table? How would this solution compare?

    You previously asked if I would have any intensive reporting. If I wanted to have an advanced search function to allow people to search for accommodation that included one of these features, would this seriously affect my options in terms of serializing the data?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  11. #11
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chuckylefrek View Post
    If I wanted to have an advanced search function to allow people to search for accommodation that included one of these features, would this seriously affect my options in terms of serializing the data?
    If this is the case, you should probably add these columns to the table containing the properties, I don't think there is a need for another table.

    Performance should not be limited by it, I think.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  12. #12
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would strongly recommend against consolidating this data into a single field. The only reason to store this information, is to make it available and useful to users of the application. Part of making information useful, is allowing the abaility to sort, search and sift through information to get to exactly what you are looking for. By consolidating it in a single field, you are making that task impossible, if not impractical.

    Having a table with just the basic information on an item, and then having a "features" table for more detail is a tried and true solution.
    Visit my blog
    PHP && Life
    for technology articles and musings.

  13. #13
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again Anthony for your reply.

    The reason I thought of adding them to a separate table is because each accommodation type has different features. For example a self catering apartment may have a long list of features that are not applicable to a hotel. So say in total there were 100 different features spread across the different accommodation types.

    Only 6 of these 100 features may be applicable to a hotel however I guess if they are all stored as separate fields in the accommodation table then each hotel listing must store 100 fields - does that make sense?

    Anyway I don't want to get too fussy about this as I am starting to get the feeling this website is turning into a mammoth task - been working on it for over 6 months. Whilst I think I am 90% complete, I have a feeling the last 10% is going to prove very time consuming
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  14. #14
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by viveknarula View Post
    Well, you can store all these values with an delimiter like comma (,) and store them in one field in the database.

    This way you can add new checkboxes later on without needing to create an new field in the database. However you need to pre-determine the sequence of the checkboxes so that you can use correct value for correct checkbox while retrieving and showing the values.
    If you do that, or store them in bitfields, you won't be able to do selects on them (ex: how many users had X selected)

    You should store them using 3 tables:
    Table 1: data (id/label = list your provided)
    Table 2: user data (user_id, etc)
    Table 3: link table (link_id (optional), data_id, user_id)

    That way, you can run reports on anything you want.
    Also, if you want to add an option, all you do is insert a record in Table 1, and your done (your php should select / loop those records to display them).

    To read them in your code, name all the checkboxes the same, with their values to their corresponding IDs.
    When the user posts the form, an array of all the selected checkboxes will be posted. So, to save them:
    #1 loop posted data and validate it
    #2 delete from Table 3 where user_id = X
    #3 loop posted data and insert in Table 3 whatever was posted for that field name.

    (Note: this pseudo code only works assuming your displaying all the options from the DB.)

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    chuckylefrek, use a seperate table like Vali described. Btw, this really is a question for the database forums, not php, as it's a database design question.

    Also, before you do anything else, spend some time reading about database normalization. It's a very important concept when designing databases.

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Yeah, you might have a table called "features".

    features
    ======
    1 - indoor pool
    2 - outdoor pool
    3 - tv
    4 - video
    5 - walkman
    6 - on site dog

    then you'd just have a lookup table for each organisation/feature.

    place_feature
    ==========
    1 - 1
    1 - 6

    As explained it will be easier to allow ppl to filter by a feature, "show me all places in county x which have any kind of pool"

    When it comes to hotels which are simpler and dont need to go through 100 items, you could create families of features.

    org_type_family
    ===========
    1 - Hotel
    2 - B+B
    3 - Camping
    4 - Guest house

    Then do similar on their admin page, eg hotels:

    family_feature
    ===========
    1 - 1 (indoor pool)
    1 - 2 (outdoor pool)
    1 - 3 (tv)
    3 - 6 (they don't generally have an onsite dog)

    So that hotel staff only see items 1,2,3 - then if they want to see "the whole lot", then they can.

  17. #17
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys for all the help and sorry that this is really a database question.

    I think I am getting my head around this.

    So I will create a table called features as follows:

    id
    feature

    Code:
    CREATE TABLE `features` (
      `id` smallint(6) NOT NULL,
      `feature` varchar(40) NOT NULL,
      PRIMARY KEY  (`id`)
    )
    I will create an admin page to enable me to add features as follows:

    Code:
    $feature = $_POST["feature"];
    
    $query = "INSERT INTO features (feature)
    				VALUES ('$feature')";
    I will also create an feature_accommodation table to link features to accommodation lisings as follows:

    featureId
    accommodationId

    Code:
    CREATE TABLE `feature_accommodation` (
      `featureId` smallint(6) NOT NULL,
      `accommodationId` smallint(6) NOT NULL
    )
    I am not quite sure how I populate this table. When a user creates a new accommodation listing, do I literally display checkboxes for all the features in the features table then when the form is submitted, insert a row into the feature_accommodation table for each feature the user has checked?

    I like the idea of the families of features - can you explain how this would work please. Would I need to add another table linking each feature to a family as follows

    featureId
    familyId

    Then have an admin page to add features to the various families?

    Does the above sound about right?

    Seems like lots of tables but perhaps this is what database normalization is all about?
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  18. #18
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I am not quite sure how I populate this table. When a user creates a new accommodation listing, do I literally display checkboxes for all the features in the features table then when the form is submitted, insert a row into the feature_accommodation table for each feature the user has checked?
    $accom = 233

    "insert into feature_accommodation values( 233, 1 ), (233, 2), (233, 3")

    later ....

    "select accom_id from feature_acommodation where feature_id in (1,2)"

    Show all accomodation place with some kind of pool.

    Then have an admin page to add features to the various families?
    Yes, if you want to. Else just maintain an array if you prefer.
    PHP Code:
    $features['hotel'][1];
    $features['hotel'][2];
    $features['hotel'][3];
    $features['hotel'][17]; 
    You dont have to make an admin screen for everything, that could be picked up from an ini file that you override manually. Depends who the admin screens are for, you? someone you sell your system to? A hotel?

    Does the above sound about right?
    yes, yes sounds completely normal (eek a pun!)

    Seems like lots of tables but perhaps this is what database normalization is all about?
    Yes, welcome to the club. Its not hard, just harder to draw on a single piece of paper, you might buy some coloured crayons too.

    It's part of the learning curve, you may even start off making too many tables and find ways of reducing them - that seems to be part of the curve too ...

    The trick, as ever it seems, is to try and get as good a handle on how you want to extract information for users of the site, what does the best other site in that niche do? Find out from your client which reports they actually need most in order to maximise the profit they derive from their business using your tool. Then you are in a position to start drawing how your tables will link up together.

    Its not about websites, its about making money for people.

    This long term project of yours might feel like a bit of a drag now, but lessons you learn should also serve to bolster your belief that you can always add more and more value simply by being confident you can use further normalization, or add more data.

  19. #19
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make sure to set some index/key on your feature_accommodation table, if you want to do selects on it.

  20. #20
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    I'd do it with bit flags, it allows advanced reporting and simplifies database structure.


    PHP Code:
    define('ELECTRICITY_INCLUDED'0x1);
    define('ELECTRICITY_COIN'0x2);
    define('ELECTRICITY_CHARGED'0x4); 

    Give each checkbox the same name e.g.

    PHP Code:
    <input type="checkbox" value="<? echo ELECTRICITY_INCLUDED?>" name="features[]" />
    <input type="checkbox" value="<? echo ELECTRICITY_COIN?>" name="features[]" />
    <input type="checkbox" value="<? echo 'ELECTRICITY_CHARGED; ?>" name="features[]" />

    then, to store the data:

    PHP Code:
    $features 0;
    if (
    is_array($_POST['features')) {
        foreach (
    $_POST['features'] as $feature) {
           
    $features |= $feature;
        }

    Now store $features (if they're all checked it will be 7 in this example).

    And then for querying based on features you can do this:

    PHP Code:
    $mysqli->query('SELECT PlaceName FROM places WHERE ' ELECTRICITY_INCLUDED  ' & features'); 
    Which will bring back any place with electricity included as a feature.

    It also allows for easy negative reporting:

    PHP Code:
    $mysqli->query('SELECT PlaceName FROM places WHERE ' ELECTRICITY_INCLUDED  ' & ~features'); 
    (All places which don't have electricity included)

    Which isn't easily possible with the table method (you'd either need a sub query or an extra on/off field in the features table).

  21. #21
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't using bit flags limit it to 31 possible values on the php side, and 63 on the mysql side? I guess you could use GMP for the php side to match the 63 on the mysql side.

    But this still seems to be a pretty severe limitation for this system.

  22. #22
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I completely overlooked bitmasks and I like the idea of them very much, probably not on this occasion as pointed out. If anyone else is interested here's a recent, and very clearly written article Denormalisation with bitmasks.

  23. #23
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    Thanks Cups
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  24. #24
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks peeps - just coming back to this now but looks like there is a lot to digest. I will have a read and hopefully not have to ask too many more questions
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  25. #25
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quick question

    If I know the accommodationId and I want to display all the features for this accommodationId, I basically need to select all the records in the feature_accommodation table using something like the following:

    Code:
    $accommodationIdValue = 22;
    
    $result = "select * from feature_accommodation where accommodationId = '$accommodationIdValue' ";
    This would return a record set of all the featureId values associated with the particular accommodation listing. However I need to get the actual feature title from the features table.

    Would I need to loop through this record set and call a query for each featureId?

    Code:
    $featureId = ?
    $result2 = mysql_query("select * from features where featureId = '$featureId'");
    $row = mysql_fetch_object($result2);
    $featureTitle = $row->feature;
    Or do I need to use some clever JOIN type code to get all the necessary data back in one step?

    I will have a stab at what is necessary - this may be totally wrong

    Code:
    $accommodationIdValue = 22;
    
    $result = mysql_query("select featureId, feature FROM feature_accommodation INNER JOIN features ON accommodationId = '$accommodationIdValue'");
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •