#1  
Old 11th January 2011, 06:00
rodobrist rodobrist is offline
Member
 
Join Date: Sep 2010
Posts: 30
Thanks: 10
Thanked 2 Times in 2 Posts
Default Mysql bit value

Hi all,

I'm falling into the trap of mySQL optimisation and my question is about using binary values, BIT(3) in particular.

To imagine how I'm using this data think of a permissions table:
(read|write|delete)
(0, 1 , 0)

Three bits, and in that example the user only has write permission.
I'm not worried about the data manipulation side of the equation(php), more the query:

SELECT name FROM permission_table
WHERE bit_colum LIKE b '%1%';

This query does not work, and I would not expect it to either(% only works on text), but my question is how could I make a

SELECT name where user has write permission
so the 3 bits = (any,1,any)
Reply With Quote
Sponsored Links
  #2  
Old 11th January 2011, 15:39
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,751 Times in 2,581 Posts
Default

Why don't you do it like Linux does it? For example:

write = 4
read = 2
delete = 1

Then you add up the permissions, for example:
write, read, delete = 4 + 2 + 1 = 7
write, read = 6
write, delete = 5
write = 4
read, delete = 3
read = 2
delete = 1

That way, you can take the value you are looking for and query it in the database.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
The Following User Says Thank You to falko For This Useful Post:
rodobrist (12th January 2011)
  #3  
Old 12th January 2011, 02:42
rodobrist rodobrist is offline
Member
 
Join Date: Sep 2010
Posts: 30
Thanks: 10
Thanked 2 Times in 2 Posts
Default i never thought of that

Thats a pretty good way to solve the issue when there are 3 bits to check for, and I guess its my fault for not indicating otherwise, but if I'm working with 8(which I am at this stage) bits and I'm curious about the 3rd and only the third

(?|?|1|? | ?|?|?|?), to try and map out the combinations numerically would be a nightmare, ie WHERE bincol=64||65||66||67 .. etc(total of 128 possibilities)

I think a bitwise & operator might be needed eg:

(??1? , ????)&
(0010,0000)

==(0010,0000)

I have a feeling Im close to the solution
Reply With Quote
  #4  
Old 12th January 2011, 04:34
rodobrist rodobrist is offline
Member
 
Join Date: Sep 2010
Posts: 30
Thanks: 10
Thanked 2 Times in 2 Posts
 
Default found it

here it is
SELECT * FROM bits

WHERE bnry & 0x20 = 0x20


so if we have an 8 bit column and you want to check if the third bit

(??1? ????)

you use the bitwise & operator against (0010 0000)

which when matched will == (0010 0000)

sorry to answer my own question
Reply With Quote
The Following User Says Thank You to rodobrist For This Useful Post:
falko (12th January 2011)
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU load locks up box. Apache or MYSQL related. crypted General 61 29th October 2010 23:16
Squirrelmail login failure Cracklefish Installation/Configuration 9 30th June 2010 21:38
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 14:58
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 10:11
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 17:04


All times are GMT +2. The time now is 09:12.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.