PHP MYSQL Problem

ChicoMendez

I have a queory in my php code that is not really working.
Heres what its all about

Ive a menu table sky_menu and in this i have a colum called access which dictates which user group will see the menu. (the user groups are stored in here as comma seperated values)

If i log in as user group 32 it will only show me the items if ONLY that user group exists in the access column... however the problem is that there will be many user groups for each menu item.


Heres the query in the php

SELECT * FROM sky_menu WHERE menutype = 'extranetmenu' AND published = 1 AND (29 IN (access) OR 32 IN (access) OR access='') ORDER BY parent, ordering

Is the query wrong ...does the IN command have to be the other way around to work... WHERE access IN(........)

How do i sort my issue

any help appreciated -

seamus

Is it two late for a DB redesign?

What I did for a similar project was to have another table which stored the security tuples.

So, for example, say you have a row in your sky_menu with ID of 10. And you want group IDs 3, 6 and 8 to have access.
You have a table "permission", which looks like:

groupid		menuid	
3		10		
6		10
8		10

Then your query looks like:

SELECT sky_menu.* FROM sky_menu, permissions WHERE sky_menu.menutype = 'extranetmenu' AND sky_menu.published = 1 AND permissions.menuid = sky_menu.id AND (permissions.groupid = 29 OR permissions.groupid = 32)

I'm not sure if it fits what you want to do, but I managed to build a working system incorporating a similar access control list.

ChicoMendez

TNX seamus

that is a solution but to be honest i cant go there...
im actually hacking a CMS and if i had to go and add tables then i woudl literally have to rewrite the whole system :eek: :eek: :eek:

is there not a simple way to do this

should the 32 IN (access) not work though ...where access is a comma seperated list.....hmmmmmmm

seamus

ChicoMendez wrote:

should the 32 IN (access) not work though ...where access is a comma seperated list.....hmmmmmmm

A quick google says not. It works the opposite way: "access IN (32, 29, ...)".

If the column is a comma-separated string, then the only thing I can think that would work is LIKE statements.
The problem with a LIKE statement is false positives. Let's say you were trying to pick out 32 from a list of values. You could use "access LIKE '%32%'", but then this would match 132, 321, etc etc. You would need to try and match it in all cases.
There are four possible places where the number you're searching for could be; at the start of the list, at the end of the list, in the middle of the list, or on its own. To avoid false positives, you need to try and match each of these explicitly:

(access LIKE '32,%' OR access LIKE '%,32' OR access LIKE '&,32,%' OR access LIKE '32')  

This is just how I see it at the moment, but someone with more SQL experience may have a better solution.