在这里，我对成员出版物的排序有一点问题。
所以我有两个请求，我想用我试图ORDER BY做一个ORDER BY (p.publication, pmembre.publication) ASC
但它没有奏效。
这是我的功能：
function recup_publi_ami_membre()
{
global $bdd;
$query = $bdd->prepare("
( SELECT m.id_login, m.prenom, m.nom, m.affiche, m.id_admin, pmembre.publication
FROM amis AS a
INNER JOIN membre AS m
ON IF(a.id_dest = :id, a.id_exp, a.id_dest) = m.id_login
INNER JOIN publication_membre AS pmembre
ON m.id_login = pmembre.id_auteur
WHERE (a.id_exp = :id OR a.id_dest = :id)
AND a.active = 1
) UNION (
SELECT m.id_login, m.prenom, m.nom, m.affiche, m.id_admin, p.publication
FROM membre AS m
INNER JOIN publication AS p
ON m.id_login = p.id_auteur
WHERE m.id_login = :idget
)
");
$query->execute(array('id' => $_GET['id'], 'idget' => $_GET['id']));
return $query->fetchAll();
}
详细说明jkns.co的评论：
将您的查询视为
(
SELECT
m.id_login as field1,
m.prenom as field2,
m.nom as field3,
m.affiche as field4,
m.id_admin as field5,
pmembre.publication as field6
FROM ...
)
UNION
(
SELECT
m.id_login as field1,
m.prenom as field2,
m.nom as field3,
m.affiche as field4,
m.id_admin as field5,
p.publication as field6
FROM ...
)
UNION 为两个查询创建单个结果集，结果集由具有 fields1,...,field6 的记录（所有）组成。而且，您几乎可以像对表（派生表）一样对这个结果集进行操作，如果您过于努力，您可能希望"询问"解释性能影响）。所以你可以做一些类似的事情
SELECT field1,field2,field3,field4,field5,field6
FROM
(
SELECT
m.id_login as field1,
m.prenom as field2,
m.nom as field3,
m.affiche as field4,
m.id_admin as field5,
pmembre.publication as field6
FROM ...
)
UNION
(
SELECT
m.id_login as field1,
m.prenom as field2,
m.nom as field3,
m.affiche as field4,
m.id_admin as field5,
p.publication as field6
FROM ...
)
ORDER BY field6 ASC
但
a）您不必将工会包装在另一个SELECT ... FROM <derived table>中，( SELECT ... ) UNION ( SELECT ...) ORDER BY foo的"快捷方式"就可以了。
b） 您不必显式设置别名来匹配字段名称;MySQL将只使用第一个UNION的名称。
所以
( SELECT m.id_login, m.prenom, m.nom, m.affiche, m.id_admin, pmembre.publication
FROM amis AS a
...
) UNION (
SELECT m.id_login, m.prenom, m.nom, m.affiche, m.id_admin, p.publication
FROM membre AS m
...
)
ORDER BY publication ASC
应该做这个伎俩。
编辑：等一下。这些字段名称确实匹配。您确定这些应该是两个单独的表，而不是带有标志字段的单个表吗？
edit2：关于没有收到任何错误消息：由于您的代码不包含"就地"错误处理，因此您可能希望将 pdo 实例切换到 PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION ，例如在创建实例时：
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
PDO::ATTR_EMULATE_PREPARES=>false,
PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));