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
