Can someone help me with this MySQL query...

cdbsi

SELECT * FROM data WHERE for='*' OR MATCH for AGAINST ('.$id') ORDER BY priority DESC,date ASC

i am making a calendar/project management system and for each record there is a 'for' field which contains something like this:

.1.56.a

. and then a number is a specific user that the record pertains to and a . and then a letter is a specific group that can view/edit the entry. if it involves everyone then the field only contains a * or "all". so in the previous example user 1 and 56 can view/edit the entry and so can everyone in the admin or "a" group

when the user logs in mysql searchs through all of the records and if the "for" field contains the precedings then it would output the information for that record.

how would i do this?
i would prefer that the query would use a regular expression.

thank you,
Chris.

Lars_Berg

how would i do this?

Redesign the datamodel. Break up the information and store each part as a separate item.

herve

Hi,

I have exactly the same point of view as Lars Berg.

You are trying to do a 1->N relationship in
a single column. That's DBMS, not RDBMS 🙂

You need to a do a relation table between
your "object" and "who is able to do ..."

object_id | agent_id

13 | 1
13 | 36

and in another table :

object_id | group_id

13 | a

Regards,
Hervé.

cdbsi

i just need to know how to do the sql query.

the users are, in fact, in another table.

thanks dudes.

herve

Well, without the structure of tables, it's
a bit theorical, but ... :

SELECT object_id
FROM rel_object_agent
WHERE agent_id='$agent_id';

This will give you the complete list of objects
this particular user is able to access.

The same goes for group of users (using
a second query after the first one)

SELECT object_id
FROM rel_object_group
WHERE group_id='$group_id';

and at first you need to get the group(s)
that specific agent is in :

SELECT group_id
FROM rel_agent_group
WHERE agent_id='$agent_id';

so you have a list of group_id to use in
the second request above

regards,
Hervé.

cdbsi

yeah, but there are multiple users who use the same entry...