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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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
> -----------------------------------------------------------------
>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
