SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 39 of 39
  1. #26
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As luck would have it I just found the following article on SQL From statements with JOINS and I think it has helped me crack this query syntax;

    http://www.sitepoint.com/article/sim...from-clause/2/

    Here is what I have come up with which seems to do the trick

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

  2. #27
    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)
    Quote Originally Posted by crmalibu View Post
    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.
    I agree, but it's by no means a killer for this system there are some pretty clear groupings:


    Electricity
    Electricity included
    Electricity by coin operated meter
    Electricity charged separately,

    Heating
    Heating included
    Heating charged separately

    Beds
    Bed linen provided
    Bed linen available at extra cost

    Facilities
    Towels provided
    Off road parking

    Utilities
    TV
    Video
    Microwave
    Iron
    Drying facilities
    Tumble dryer
    Cot


    Site
    On-site shop
    On-site food take-away
    on-site dog
    Indoor swimming pool,
    Outdoor swimming pool

    Children
    Children welcome
    Children’s play area

    You could have each one of those as a field (or group them into three or four distinct fields) and the chances for having more than 31 of each are slim.

    I still believe that the bit system is superior.
    with this system using multiple fields it's really easy to search for properties e.g.

    Where:
    -Electricity is included
    -Children are welcome
    -There's a childrens play area
    -Theres a pool (either indoor or outdoor)

    using the table method. Your query will get messy.

    With flags (with or without multiple columns) this can be done with a simple SELECT, no joins, no sub queries.


    After looking at the list, some of these should probably be radio buttons anyway.

  3. #28
    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 like it TomB.

    using the table method. Your query will get messy.

    With flags (with or without multiple columns) this can be done with a simple SELECT, no joins, no sub queries.
    Can you show how a single one of these selects would work, perhaps using just the idea of pools?

    I think a lot of us would benefit.

  4. #29
    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)
    Sorry for the late reply!

    Something along the lines of:

    PHP Code:
    //not a full list, just to show how groupings could work

    define('SITE_SHOP'0x1);
    define('SITE_TAKEAWAY'0x2);
    define('SITE_DOG'0x3);

    define('ELECTRICITY_INCLUDED'0x1);
    define('ELECTRICITY_COIN'0x2);

    define('UTIL_TV'0x1);
    define('UTIL_MICROWAVE'0x2);
    define('UTIL_IRON'0x2);

    define('POOL_INDOOR'0x1);
    define('POOL_OUTDOOR'0x2);


    //Find all places with a shop and a takeaway
    $mysqli->query('SELECT name, address FROM places WHERE ' . ( SITE_SHOP SITE_TAKEAWAY ) . ' & site');


    //Find all places with a shop and a takeaway and a tv
    $mysqli->query('SELECT name, address FROM places WHERE ' . ( SITE_SHOP SITE_TAKEAWAY ) . ' & site AND ' UTIL_TV ' & utilities');

    //Find all places with an indoor or outdoor pool
    $mysqli->query('SELECT name, address FROM places WHERE pool & ' . (POOL_INDOOR POOL_OUTDOOR)'); 
    Assuming, `site` `utilities` and `pool` are the bit fields in the table.



    The instant power this brings to searching the data quickly with simple queries is well worth designing around the 31 bit limit, imho. It's also much easer to give the end-user (who's searching for accomodation, not the person adding it..) a list of each possible checkbox and do the query the data since you just need to bitwise or each of the boxes they check into the correct groups and do your query without having to try to build up the query join by join depending on what they've checked.

  5. #30
    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)
    What would you recommend is the best way to store the data in mysql then?

    1001 or just 9 ? And what sort of data type?

    I have been fiddling with this for a while now and it seems there are a few ways to get the queries working.

    Another question though, why,
    define('POOL_INDOOR', 0x1);
    define('POOL_OUTDOOR', 0x2);

    and not

    define('POOL_INDOOR', 1);
    define('POOL_OUTDOOR', 2);

    ?

    Thanks.

  6. #31
    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)
    Any integer field will work, choose one depending on the number of bits you want to store, not sure how mysql handles bitwise comparisons on binary fields.

    as for marking as hex, simply habit and laziness on my part:

    It's easier to work out the next one in the sequence

    0x1
    0x2
    0x4
    0x8
    0x10
    0x20
    0x40
    0x80
    0x100
    0x200
    0x400
    0x800
    0x1000
    0x2000
    0x4000
    0x8000
    (repeat 1-8, then add a zero)

    than:

    1
    2
    4
    8
    16
    32
    64
    128
    256
    512
    1024
    2048
    4096
    8192
    ...

  7. #32
    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)
    Oh and for anyone interested, BIT_OR in mysql is a rather under utilised function!

    I created a rather nice permissions system where users could be part of multiple groups (each one was a bit value) and each group had permissions within a section of the site but they could have user level permissions set on top of that


    e.g. User TomB would be in groups 2 and 4 and have permissions 1,2,4 but also have a user permission 8.

    I got the users permission like this:

    Code:
    SELECT distinct BIT_OR(group.permission) | user.allowed) & ~user.denied) as perm 
    FROM user
    Inner Join groups ON user.groups & groups.id
    WHERE groups.module = 'admin'
    AND user.id = 1234
    (admin here refers to a section of the site that permissions have been grouped into)

    my groups table looks like this:

    Code:
    id        module permission 
    1         admin   7
    2         admin   3
    4         admin   1
    This, in a single query grabs all the users permissions (allowing them to be in multiple groups)


    This defines what permissions each group has, and the group field in the user table could potentially be 7 putting the user in all 3 groups. My user then has the permissions of all the groups plus the permissions in their field `allowed` minus the permissions in their field `denied` which is all calculated in a 5 line query. Try doing that without bitfields

    Oh, and to top it off, because all this information is in the database, I link to a permissions table which has 3 fields "module", "permission", label

    e.g.
    Code:
    admin 1 EDIT_USER
    admin 2 DELETE_USER
    admin 4 CREATE_USER
    and use this table to generate a php script with all the constants into module level files.

  8. #33
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TomB View Post
    Oh and for anyone interested, BIT_OR in mysql is a rather under utilised function!

    I created a rather nice permissions system where users could be part of multiple groups (each one was a bit value) and each group had permissions within a section of the site but they could have user level permissions set on top of that


    e.g. User TomB would be in groups 2 and 4 and have permissions 1,2,4 but also have a user permission 8.

    I got the users permission like this:

    Code:
    SELECT distinct BIT_OR(group.permission) | user.allowed) & ~user.denied) as perm 
    FROM user
    Inner Join groups ON user.groups & groups.id
    WHERE groups.module = 'admin'
    AND user.id = 1234
    (admin here refers to a section of the site that permissions have been grouped into)

    my groups table looks like this:

    Code:
    id        module permission 
    1         admin   7
    2         admin   3
    4         admin   1
    This, in a single query grabs all the users permissions (allowing them to be in multiple groups)


    This defines what permissions each group has, and the group field in the user table could potentially be 7 putting the user in all 3 groups. My user then has the permissions of all the groups plus the permissions in their field `allowed` minus the permissions in their field `denied` which is all calculated in a 5 line query. Try doing that without bitfields

    Oh, and to top it off, because all this information is in the database, I link to a permissions table which has 3 fields "module", "permission", label

    e.g.
    Code:
    admin 1 EDIT_USER
    admin 2 DELETE_USER
    admin 4 CREATE_USER
    and use this table to generate a php script with all the constants into module level files.
    Me thinks me smells a bug >.>
    Code:
    SELECT distinct BIT_OR(group.permission) | user.allowed) & ~user.denied)
    Though honestly it would have been simpler to use a simple ACL. Build, cache reuse it.
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  9. #34
    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 sure if I am kidding myself about how the bitwise operators work here, let me ask you this.

    Imagine a simple table:

    bitwise_test
    ============
    id (PK)
    name (varchar)
    pool (Int)

    Containing these places and values:
    ==========================
    1 Splashfun 1
    2 Parkside 2
    3 Swimworld 11
    4 PoshPlace 14
    5 Sanctuary 12

    Where the values of pools are obtained from:
    =================================
    1 = kids pool
    2 = adult pool
    4 = Jacuzzi
    8 = Sauna

    I can get all the places that have a sauna with:

    select * from bitwise_test where pool & 8

    I can get all the places with an adult pool OR a sauna with:

    select * from bitwise_test where pool & (8 | 2)

    But to get all the places that have both an adult pool AND a sauna I have to do this:

    select * from bitwise_test where pool & 8 AND pool & 2

    Is that last one correct? or is there another way of generating the select?

    Thanks

  10. #35
    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)
    There's two methods of doing it, the first is the one you provided (which in my opinion has much greater readability than the other method of:

    Code:
    select * from bitwise_test where pool & 10 = 10
    This works because the & operator doesn't return a boolean value, it returns a number. Using it as a boolean expression evaluates to true because it's not 0.

    Some examples using your data:

    Lets use:
    4 PoshPlace 14

    Does it have an adult pool (2) and sauna (8).

    2 | 8 === 10

    14 & 10 === 10
    (would be found by the query above)

    One of the others, with just an adult pool:

    2 Parkside 2

    2 & 10 === 2

    (would not be found by the query above)


    For readability, the way you are doing it makes more sense, however if the numbers are being generated from user input, it's easier to programatically generate a query with

    x & y = x

    than it is to generate a query of:

    x & y AND z & y

    especially when you consider you could need many times "AND x & y" in the query.

    So, neither is "correct", I'd say it depends on whether the query is hard coded or being dynamically generated.

  11. #36
    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)
    Quote Originally Posted by TomB View Post
    There's two methods of doing it, the first is the one you provided (which in my opinion has much greater readability than the other method of:

    Code:
    select * from bitwise_test where pool & 10 = 10
    I suspected that there would be a way - thanks for showing me that, I went in the direction of comparing numbers but quickly came unstuck.

    Quote Originally Posted by TomB View Post
    For readability, the way you are doing it makes more sense, however if the numbers are being generated from user input, it's easier to programatically generate a query with

    x & y = x

    than it is to generate a query of:

    x & y AND z & y

    especially when you consider you could need many times "AND x & y" in the query.

    So, neither is "correct", I'd say it depends on whether the query is hard coded or being dynamically generated.
    Yes, you have nailed what I am trying to do, I am starting from the database and working my way back out into PHP/Html - because much of this bitwise conversation really belonged in the Mysql forum:

    Now I can start to think about the ramifications of :

    • generating the form elements - as outlined in the OP
    • discover patterns for processing them efficiently
    • generating the sql statements for searches and selects


    I fully expect there to be immense savings.

    If I do any more work on this I will perhaps create another thread dealing with those PHP issues, as we have moved off from the OP quite a bit, although ultimately I want to show how to solve Chucklefrek's problem using bitwise operators partly as proof to myself I can do this, and partly to see what the drawbacks or benefits could be.

    I have not seen this kind of discussion anywhere before although I put down to my lack of basic Computer Science education.

    TomB, I would like to thank you very much for spending the time to spell all of this out to me, and anyone else who may have taken an interest.

  12. #37
    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 think i answered most of those points in my first post in this topic Though i'll expand them and apply them to this example. We are technically still discussing the problem presented in the OP

    In a real world scenario you'd probably want to store the possible fields in a database table, as you'll be able to do a lot easer reporting on the data because you wont need to look up what each number means each time.

    e.g.
    Code:
    value    field      name                 description
    1          site      SITE_SHOP         Has Shop
    2          site      SITE_TAKEAWAY Has Takeaway
    4          site      SITE_DOG          Allows dogs
    1          pool     POOL_INDOOR     Indoor Pool
    2          pool     POOL_OUTDOOR  Outdoor Pool
    the primary key would be made up of `value` and `field`.

    The reason for the name column is so you can write a simple script which generates all the constants for you, should you need them.

    From here you can easily generate a form with:

    Code:
    [] Has Shop
    [] Has Takeaway
    [] Allows dogs
    [] Indoor Pool
    [] Outdoor Pool
    assign the checkboxes something like:

    Code:
    <input type="checkbox" name="pool[]" value="1" /> Indoor Pool
    <input type="checkbox" name="pool[]" value="2" /> Outdoor Pool
    and process it like:

    PHP Code:
    $pool 0;
    foreach (
    $_POST['pool'] as $p) {
        
    $pool |= $p;

    Then in your query for AND which finds records matching all the options the user ticked

    PHP Code:
    $mysqli->query('SELECT * FROM bitwise_test WHERE  ' $pool ' & pool = ' $pool); 
    the OR query to find places with any of the options the user ticked

    PHP Code:
    $mysqli->query('SELECT * FROM bitwise_test WHERE  ' $pool ' & pool); 
    (obviously the $_POST array needs checking for the correct data type to stop sql injection, I haven't done this for the purpose of keeping the example as simple as possible.)


    I'm not surprised you haven't seen discussions like this before.. bitflags in general seem a very under utilised tool. Admittedly the scope for their usage is not massive, but there are plenty of places where they are the neatest solution.

  13. #38
    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)
    Pretty much as I imagined TomB, thanks for spelling it out.

    So in the OPs case there would only be need for 2 tables, say "features" and "rental_places"

    I added a couple of lines to pool to keep with my last example and I am going to spell out what I have understood so far, partly for my own sake and partly for anyone else who might have dropped the ball ...
    Code:
    features
    ======
    value    field      name                 description
    1          site      SITE_SHOP         Has Shop
    2          site      SITE_TAKEAWAY Has Takeaway
    4          site      SITE_DOG          Allows dogs
    1          pool     POOL_INDOOR     Indoor Pool
    2          pool     POOL_OUTDOOR  Outdoor Pool
    4          pool     POOL_JACUZZI    Jacuzzi 
    8          pool     POOL_SAUNA      Sauna
    Code:
    rental_places
    ==========
    id    name     pool  site
    1    Campsite    3     7
    2    Hotel      13    0
    The Campsite pool facilities being 3.

    Running decbin over 3 with decbin(3) gives 0011 (padded zeros for effect here)
    or think of it as being "pool feature 1" and "pool feature 2". (1+2=3)

    The Hotel pool features are 13.

    Running decbin over 3 with decbin(13) gives 1101
    or think of it as being "pool feature 1" and "pool feature 4" and "pool feature 8" (8+4+1=13)

    Creating a rich search which did not need re-writing and debugging every time you added a feature would be simple. I know a sql-generator of some kind could be used but using bitflags would result in neater looking queries, though speed I don't know about.

    Now I think I understand what is going on behind the scenes of more sites which contain "pick must have" or "pick optional" selectors on searches. *ding*

    Creating the form elements for Admin-ers to add features to places would be pretty similar to the site search.

    But getting queries out of the two databases, like "show me all of Hotel's features"?

    I surprised myself by getting this to work:
    Code:
    SELECT 
    description
    FROM features AS f
    LEFT JOIN rental_place AS r 
    ON r.pool & f.value
    WHERE field = 'pool'
    AND r.id =1
    
    // Correctly gives:
    // Indoor pool 
    // Outdoor pool
    I dont know why, but I am pretty amazed that the bitwise operators work in the joins, but how could I get all features (pool and site) for a known place with a single join? i have tried but been unsuccessful ... :|

    Another benefit of your single features table also springs to mind.

    URLs could easily be generated like this;

    places .com/features/site/dogs-allowed

    mod_rewritten to:

    features.php?feature-field=site&feature-description=allows-dogs

    which generates the SQL statement:
    Code:
    select r.place_name 
    from 
    rental_place as r 
    LEFT JOIN features as f 
    ON f.value & r.site  
    where description = "allows dogs"
    
    // gives :
    // Campsite
    Which makes me wonder why can't the feature description ("Allows dogs") be the primary key because it would make an ideal natural key candidate?

    Whereas "value" and "field" merely need to be unique.
    Last edited by Cups; Mar 6, 2009 at 07:05. Reason: formatting code went wrong

  14. #39
    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 just came across this post Questions about Mysql and Bitwise functions, which might shed some light for others interested in this.


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
  •