select help needed

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

laserlight

Might be better to avoid comma delimited data and just change the schema such that the users table has a column for each permission.
Then use boolean values to determine if a user has a given permission.

dopey

Hi,

I would agree with the previous poster in re-arranging your db schema if possible.

If you really want to use a comma seperated for the permissions and assuming your departments are in a table as below:

1,Sales
2,Marketing
3,Support

then the following would prob. work:

select * from users,depts where locate(depts.id,users.permissions) != 0;

Have to be careful of matching depts. that only have single digits in depts. that have 2 digits e.g. 33,Finance would match when 'Support' permission is looked for. You'd have to mod. the query

ahundiak

Consider adding a third table:
CREATE TABLE user_department_permission (
user_id INTEGER,
department_id INTEGER
);

Once implemented you will find that queries become easier as does adding departments. You can add a third column to control the level of permission that each user has on a per department basis.

pelegk2

locate(depts.id,users.permissions)

this do?

dopey

It returns the position of the string dept.id in users.permissions. Return of 0 means not found