Page 1 of 1

question about select

Posted: Sun Feb 01, 2004 1:01 am
by pelegk2
i have for each user permmision list of department
i have departments of 1,2,3.....,50

and the user have for example permission to :
1,4,8,13,44

i want to make a select that in each table the user will see only the things he has permission to !!!!!!!
in the DB i write the permmision list like this :
1,2,3,4,5,....(and so on)
so how do i do it?
thanks in advance
epelg

Posted: Sun Feb 01, 2004 1:31 am
by jaxn
You want a tabe to link departments to users. That way you can do it easily with the join.

-Jackson

Posted: Sun Feb 01, 2004 1:33 am
by pelegk2
what to link?????
i exaplined how my DB is already working
and i asked how do i make a select that will show the user only the departments that he can access
or to sub "titles" that he may access by the permmision

Posted: Sun Feb 01, 2004 1:43 am
by markl999
Without seeing the exact db structure it's a little difficult.
But you want something like :

SELECT d.title FROM departments d, users u WHERE FIND_IN_SET(d.id, u.permissions)

Below is a dump of the db/tables i used to test this so you can tweak it to match your setup.

CREATE TABLE `departments` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

#
# Dumping data for table `departments`
#

INSERT INTO `departments` VALUES (1, 'dep1');
INSERT INTO `departments` VALUES (2, 'dep2');
INSERT INTO `departments` VALUES (3, 'dep3');
INSERT INTO `departments` VALUES (4, 'dep4');
INSERT INTO `departments` VALUES (5, 'dep5');
INSERT INTO `departments` VALUES (6, 'dep6');
INSERT INTO `departments` VALUES (7, 'dep7');
INSERT INTO `departments` VALUES (8, 'dep8');
INSERT INTO `departments` VALUES (9, 'dep9');
INSERT INTO `departments` VALUES (10, 'dep10');

# --------------------------------------------------------

#
# Table structure for table `users`
#

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL default '',
`permissions` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

#
# Dumping data for table `users`
#

INSERT INTO `users` VALUES (1, 'user1', '1,3,5,7');

Posted: Sun Feb 01, 2004 2:27 am
by pelegk2
ok i exaplin my self again
the user's table is like this :
user name,password,permission list
permission list is in this structure : 1,5,8,12,31,43

and there is a table with data :
data id,data name,premmision number
where premmision number can be for example 8 (which our user have!)
so he can see this row from the DB table!
but if the premmision number was 10 he chouldnt!
so how do i build a select for this?
thanks in advance
peleg

Posted: Sun Feb 01, 2004 2:39 am
by markl999
SELECT d.* FROM data d, users u WHERE FIND_IN_SET(d.permission_number, u.permission_list) AND u.user_name='foo1';

Posted: Sun Feb 01, 2004 2:59 am
by pelegk2
very nice!
can u explain the idea of "FIND_IN_SET"?

Posted: Sun Feb 01, 2004 3:01 am
by markl999
FIND_IN_SET('a', 'a,b,c,d,e')
It basically looks for 'a' in the set/list 'a,b,c,d,e'

Read more about it here

Posted: Sun Feb 01, 2004 3:14 am
by pelegk2
1 more question
say i want a specific user to have a permission to everything
so instead of writing 1,2,3,4,5....50
i want to write say "*"
how can i do it so it will work with your quesry?
thanks in advance
peleg

Posted: Sun Feb 01, 2004 3:25 am
by markl999
You could use '*' in the users permission_list and just modify the query to:

SELECT d.* FROM data d, users u WHERE (FIND_IN_SET(d.permission_number, u.permission_list) OR u.permission_list = '*') AND u.user_name='foo1';

Posted: Sun Feb 01, 2004 3:30 am
by pelegk2
very nice
thanks alot!
i have posted another question maybe u can help me

Posted: Sun Feb 01, 2004 3:34 am
by markl999
i have posted another question maybe u can help me
Where?