question about select
Moderator: General Moderators
- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
question about select
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
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
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');
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');
- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
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
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
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
It basically looks for 'a' in the set/list 'a,b,c,d,e'
Read more about it here