question about select

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
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

Post 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
jaxn
Forum Commoner
Posts: 55
Joined: Fri Jan 16, 2004 1:50 pm
Location: Nashville, TN

Post by jaxn »

You want a tabe to link departments to users. That way you can do it easily with the join.

-Jackson
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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');
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

SELECT d.* FROM data d, users u WHERE FIND_IN_SET(d.permission_number, u.permission_list) AND u.user_name='foo1';
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

Post by pelegk2 »

very nice!
can u explain the idea of "FIND_IN_SET"?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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';
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

Post by pelegk2 »

very nice
thanks alot!
i have posted another question maybe u can help me
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

i have posted another question maybe u can help me
Where?
Post Reply