Condition WHERE CASE

Version imprimable

Bonsoir,

La requete suivante s'execute mais ne retourne rien, alors qu'elle devrait. Ca va faire 2 jours que je cherche une solution sans succès, pourriez vous m'aider ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
<?php
$Query = $Sql->query('SELECT mp_threads.mp_thread_id, 
	mp_threads.mp_thread_starter_id, 
	mp_threads.mp_thread_dest_id, 
	mp_threads.mp_thread_subject, 
	mp_threads.mp_thread_starter_state, 
	mp_threads.mp_thread_dest_state, 
	mp_threads.mp_thread_date, 
	CASE WHEN mp_threads.mp_thread_starter_id = '.$_SESSION['mbr_id'].' THEN "starter" ELSE "dest" END AS mbr_role, 
	members_starter.mbr_id AS mbr_id_starter, 
	members_starter.mbr_pseudo AS mbr_pseudo_starter,
	members_dest.mbr_id AS mbr_id_dest, 
	members_dest.mbr_pseudo AS mbr_pseudo_dest 
	FROM mp_threads 
	LEFT JOIN members AS members_starter
	ON members_starter.mbr_id = mp_threads.mp_thread_starter_id 
	LEFT JOIN members AS members_dest 
	ON members_dest.mbr_id = mp_threads.mp_thread_dest_id
	WHERE (CASE WHEN mp_threads.mp_thread_starter_id = '.$_SESSION['mbr_id'].' THEN
		mp_thread_starter_state != 2 
	ELSE 
		mp_thread_dest_state != 2 
	END) 
	AND (mp_thread_starter_id = '.$_SESSION['mbr_id'].' OR mp_thread_dest_id = '.$_SESSION['mbr_id'].') 
	ORDER BY mp_thread_date DESC') or exit(print_r($Query->errorInfo()));
?>

Merci !

Bonsoir,

Dans votre clause WHERE, il y a :

Code:

1
2
3
4
5
6
7
8
9
 
...
  WHERE (
       CASE
            WHEN mp_threads.mp_thread_starter_id = '.$_SESSION['mbr_id'].
            THEN mp_thread_starter_state        != 2
            ELSE mp_thread_dest_state           != 2
       END)
...

Qui est assez inhabituel :?

J'ecrirai plutôt :

Code:

1
2
3
4
5
6
7
 
...
  WHERE ( (mp_threads.mp_thread_starter_id = '.$_SESSION['mbr_id'].'
                 AND  mp_thread_starter_state        != 2)
            OR (mp_threads.mp_thread_starter_id != '.$_SESSION['mbr_id'].'
                 AND  mp_thread_dest_state           != 2))
...

Cdlt,
OD

Une autre manière de l'écrire

Code:

1
2
3
4
5
WHERE   CASE
        WHEN mp_threads.mp_thread_starter_id = '.$_SESSION['mbr_id'].'
            THEN mp_thread_starter_state
        ELSE mp_thread_dest_state
        END <> 2

Ou encore

Code:

1
2
3
4
5
WHERE   CASE mp_threads.mp_thread_starter_id
        WHEN '.$_SESSION['mbr_id'].'
            THEN mp_thread_starter_state
        ELSE mp_thread_dest_state
        END <> 2