Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2011
    Posts
    408
    Thanks
    18
    Thanked 26 Times in 26 Posts

    Using binary selection with SET

    I have a column with type SET to hold values for privacies. These values specify who can view the data for the row. I need to create a WHERE clause so it only gets rows that have matching data between the row's and the given data.

    Basically, lets say I have these permissions:
    1- Everyone
    2- Members
    4- Friends
    8- Self

    Now I need to build a query that gets rows that have any match to 11, so any row with everyone, members, or self in the permissions. I could do this with PHP, but the issue would be if the table is too big it would take a while to get the data, so I use limits. If I check the data in the limited range, it could cut the results down farther, resulting in less rows than intended.

    So basically, every user gets permission 1, members get 2 (so now 3), and the self isn't really necessary (I'll probably remove it). Members should see any row with the member permission, and everyone should see any row with the everyone permission. I could do this using array_interset() in PHP, which returns values that are present in the given arrays (keeping keys from the first). I just need to use this functionality in MySQL to be sire I get the most results that's possible. The only other option I can think of is creating a separate column for each permission, and then adding it to the where clause as I go.

    Also, when I build up the query, I have a binary version of the user's permissions (so members have at least 3), but I can change this as necessary as well. The next challenge will be the buddies/friends system, as well as buddies of buddies, friends of friends, etc. (buddies are one way friendships, friends are both ways). It will be a lot to incorporate, but I'll work on that part later, I just want it to be better setup
    Last edited by Dubz; 06-24-2014 at 03:08 AM.
    If I've helped you out, show your appreciation by clicking the "Thanks" link as well as a link below!

    AdFly
    Facebook | Twitter
    Google | YouTube

  • #2
    Regular Coder
    Join Date
    Sep 2011
    Posts
    408
    Thanks
    18
    Thanked 26 Times in 26 Posts
    I've figured it out. Just using 'SELECT * FROM `table` WHERE `col` & '.$bin_val allows me to check it by binary, and then use OR to pass different values to test.
    If I've helped you out, show your appreciation by clicking the "Thanks" link as well as a link below!

    AdFly
    Facebook | Twitter
    Google | YouTube


  •  

    Posting Permissions

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