Thans for awnsering!

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


----- Original Message -----
From: "Martin Adler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 24, 2002 4:47 PM
Subject: [PHP-DB] force no results


> Hi,
>
> is it possible to force mysql in a SELECT clause depend on a
> condition to give no results?
>
> for example:
> I do a SELECT and get 3 rows as result,
> but one row contains a value, which make the
> other rows worthless. and I won't get this results
>
> 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
>



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to