problème avec une requête sql avec UNION et ORDERR BY

cette requête fonctionne parfaitement , mais si je voulais ajouter ORDER BY id , ça ne donne rien comme résultat.!!

voici mon

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
$sth = $this->db->prepare("SELECT id, r1, r2, r3, r4
    FROM table1
        WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 
    UNION
        SELECT   id,r1, r2, r3, r4  FROM table2
            WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 
    UNION
        SELECT   id,r1, r2, r3, r4  FROM table3
            WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 
    UNION
        SELECT  id, r1, r2, r3, r4  FROM table4
            WHERE name LIKE '%{$key}%'  AND status = 1 LIMIT 0,20 ");
 
$sth->execute();
$data = $sth->fetchAll();

j'ajoute après chaque LIMIT 0,20 ORDER BY id. pouvez vous m'aider a résoudre ce problème . merci

$sth = $this->db->prepare(" SELECT * FROM ( SELECT id, r1, r2, r3, r4 FROM table1 WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 UNION SELECT id, r1, r2, r3, r4 FROM table2 WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 UNION SELECT id, r1, r2, r3, r4 FROM table3 WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 UNION SELECT id, r1, r2, r3, r4 FROM table4 WHERE name LIKE '%{$key}%' AND status = 1 LIMIT 0,20 ) as Alias ORDER BY ID"); $sth->execute(); $data = $sth->fetchAll();