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