Re: prevent results

Hi, i found a solution by myself i think it's very complex but may help others for finding solutions Thats the query: SELECT entries.id, entries.head, entries.date, entries.author FROM entries LEFT JOIN category ON entries.cat_no REGEXP CONCAT("\\.",category.id,"(\\.|$)") LEFT JOIN entgroup ON entries.id = entgroup.eid LEFT JOIN catgroup ON category.id = catgroup.cid LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid WHERE MATCH (head,text) AGAINST ("Who's not") && (entgroup.gid IS NULL || entgroup.gid = *user-group-id*) GROUP BY entries.id HAVING min(catgroup.gid IS NULL || catgroup.gid = *user-group-id*)<>0; This is the result if all conditions are true otherwise i get none +----+---------------+------------+--------------+ | id | head | date | author | +----+---------------+------------+--------------+ | 6 | Who's not ... | 2002-06-13 | Martin Adler | +----+---------------+------------+--------------+ the soluton for my former problem is in the HAVING clause the stuff in the min()-function returns only 1 when the category have the same group-id as the user or the category don't have a group-id otherwise it returns 0 this condition is executet for every row in the table. If there's no conflict with the user-rights in "every" row should be 1 the result of the condition The min()-function selects the smallest value, if everything is Ok the value is 1 and i get my result if not the value is 0 and 0 <> 0 isn't true and i don't get a result if anyone have a questions about this query just mail me i'll try to help i also thank everyone who tried to help me to find a soluton and if anyone have a better solution please let me know Martin ----- Original Message ----- From: "Martin Adler" <martin.adler@continum.net> To: <php-db@lists.php.net> Sent: Wednesday, June 26, 2002 9:19 AM Subject: prevent results > Hi, > > unfortunately I still stand before the same problem > is it possible to force mysql in a SELECT clause depend on a > condition to prevent results? > I would be happy and grateful for every help > > OK here is my Query > > SELECT entries.id, entries.head, entries.date, entries.author, (catgroup.gid > IS NULL || catgroup.gid = *user-group-id*) AS indi > FROM entries > LEFT JOIN category ON entries.cat_no REGEXP > CONCAT("\\.",category.id,"(\\.|$)") > LEFT JOIN entgroup ON entries.id = entgroup.eid > LEFT JOIN catgroup ON category.id = catgroup.cid > LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid > LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid > WHERE > MATCH (head,text) AGAINST ("Who's not") && > (entgroup.gid IS NULL || entgroup.gid = *user-group-id*); > > > With thies query, i try to get a entry with a fulltext search. > The entry should only be shown if the entry-group-id is NULL or > is the same as the user-group-id and > if all category-group-id's above the entry is NULL or > is the same as the user-group-id > > With "GROUP BY indi" i'll get maximally 2 results with which i can handle > easy in PHP > but i want to try it in MySQL. > Maybe someone knows a cleaner solution > > This is the result: > +----+---------------+------------+--------------+------+ > | id | head | date | author | indi | > +----+---------------+------------+--------------+------+ > | 6 | Who's not ... | 2002-06-13 | Martin Adler | 1 | > | 6 | Who's not ... | 2002-06-13 | Martin Adler | 0 | > | 6 | Who's not ... | 2002-06-13 | Martin Adler | 1 | > +----+---------------+------------+--------------+------+ > > > Here are the used tables > > Table entries > +----+-----+-------------+---------------+------------+--------------+------ > ---------------------------------------------------+---------------------- -- > ------+ > | id | uid | cat_no | head | date | author | text > | keywd | > +----+-----+-------------+---------------+------------+--------------+------ > ---------------------------------------------------+---------------------- -- > ------+ > | 1 | 1 | 0.1.3.8 | Headline | 2002-03-12 | Martin Adler | Some Text > | keywords | > | 2 | 2 | 0.1.2 | Mittagessen | 2002-04-03 | Martin Adler | > Fruehjahrsputz | Spring > | > | 3 | 4 | 0.1.2 | Hausboot | 2002-04-03 | Martin Adler | > Quarktasche | Summer > | > | 4 | 2 | 0.1.2.21.22 | Kongo | 2002-04-04 | Martin Adler | Kebup > | Springer | > | 5 | 2 | 0.1.3 | Hanfkissen | 2002-04-04 | Martin Adler | > Bananen sind lecker! | Palmen Testwagen > Radiergummi | > | 6 | 2 | 0.1.2.24 | Who's not ... | 2002-06-13 | Martin Adler | .in > the group technik shouldn't be able to read this | > | > +----+-----+-------------+---------------+------------+--------------+------ > ---------------------------------------------------+---------------------- -- > ------+ > * These are the entries, which are assigned to the categories > > > Table category > | id | cat_no | cat | > +----+--------------------+-----------------------+ > | 1 | 0 | ROOT | > | 2 | 0.1 | Hardware | > | 3 | 0.1 | Software | > | 4 | 0.1.2 | HP | > | 5 | 0.1.2.4 | e-pc | > | 6 | 0.1.2.4.5 | CD-Brenner | > | 7 | 0.1.2 | PC | > | 8 | 0.1.3 | php | > | 9 | 0.1.3.8 | rekursiv | > | 10 | 0.1.3.8.9 | path | > | 12 | 0.1.3.8.9.10.11 | kategorie12 | > | 15 | 0.1.3.8.9.10.11 | kategorie15 | > | 16 | 0.1.3.8.9.10.11 | kategorie16 | > | 17 | 0.1.3.8.9.10.11 | kategorie17 | > | 20 | 0.1.3.8.9.10.11.12 | kategorie20 | > | 21 | 0.1.2 | Netzwerktechnik | > | 22 | 0.1.2.21 | Router u. L3-Switches | > | 24 | 0.1.2 | abba | > +----+--------------------+-----------------------+ > * cat_no is the Hierarchy --HP is a subdir of Hardware and Hardware is a > subdir of ROOT and ROOT is the topleveldir > > Table mzgroup > +----+---------+ > | id | name | > +----+---------+ > | 1 | nobody | > | 2 | technik | > +----+---------+ > > > Table catgroup > +-----+-----+ > | gid | cid | > +-----+-----+ > | 2 | 24 | > | 2 | 23 | > | 1 | 2 | > +-----+-----+ > > * gid = group id, eid = category id > > > Table entgroup > +-----+-----+ > | gid | eid | > +-----+-----+ > | 2 | 6 | > +-----+-----+ > * gid = group id, eid = entries id > > > > thank's > > > > > sincerely > Martin > > > ----------------------------------------------------------------- > Martin Adler CGI, Perl, PHP > > Continum AG Tel. +49 761 4794090 > Bötzinger Straße 29a Fax. +49 761 4794099 > 79111 Freiburg i. Br. > http://www.continum.net > ----------------------------------------------------------------- > > >