具有两个选择的 PHP/MySQL 联合

Question

我的陈述有什么问题？我已经尝试了所有我能想到的。

$stmt = $mysqli->prepare("SELECT host_name, review_title FROM lhr_reviews
              UNION
              SELECT host_url FROM lhr_hostinfo WHERE host_name = ?
              ORDER BY host_name");

$stmt->bind_param("s", $id);
$stmt->execute(); 
$res = $stmt->get_result();

while($row = $res->fetch_assoc()) {
  $list .=  "<li><a href='".$row['host_url']."'>".substr($row['review_title'],0,20)."</a></li>";
}

Answer 1

从两个查询中选择的列必须在列数和相应的数据类型方面相互匹配:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

在您的查询中，第一个选择选择两列，而第二个选择仅选择一列。这不是真的，您可以在第二个选择中选择空字符串作为变通方法。另一个问题是 ORDER BY host_name 你可以这样添加它，你必须将这些查询放在子查询中并在外部查询中排序，如下所示:

SELECT *
FROM
(
    SELECT host_name, review_title FROM lhr_reviews
    UNION
    SELECT host_url, ''            FROM lhr_hostinfo WHERE host_name = ?
) AS sub
ORDER BY host_name;