php - MySQL Union 不正确返回表名

Question

我必须在多个表中搜索:

$query ="(SELECT *, 'product' AS type FROM products WHERE title LIKE ? OR description LIKE ? OR full_description LIKE ? OR keywords LIKE ? OR option1 LIKE ? OR option2 LIKE ? OR option3 LIKE ? OR option4 LIKE ? OR option5 LIKE ? OR option6 LIKE ? OR option7 LIKE ? OR option8 LIKE ?) 
UNION 
(SELECT *, 'article' AS type, NULL AS image1, NULL AS image2, NULL AS image3, NULL AS image4 FROM articles WHERE title LIKE ? OR description LIKE ? OR full_description LIKE ? OR keywords LIKE ? OR option1 LIKE ? OR option2 LIKE ? OR option3 LIKE ? OR option4 LIKE ? OR option5 LIKE ? OR option6 LIKE ? OR option7 LIKE ? OR option8 LIKE ?) 
UNION 
(SELECT *, 'news' AS type, NULL AS image1, NULL AS image2, NULL AS image3, NULL AS image4 FROM news WHERE title LIKE ? OR description LIKE ? OR full_description LIKE ? OR keywords LIKE ? OR option1 LIKE ? OR option2 LIKE ? OR option3 LIKE ? OR option4 LIKE ? OR option5 LIKE ? OR option6 LIKE ? OR option7 LIKE ? OR option8 LIKE ?) 
UNION 
(SELECT *, 'page' AS type, NULL AS categoryID, NULL AS categoryName, NULL AS full_description, NULL AS image, NULL AS image1, NULL AS image2, NULL AS image3, NULL AS image4, NULL AS price FROM pages WHERE title LIKE ? OR description LIKE ? OR option1 LIKE ? OR option2 LIKE ? OR option3 LIKE ? OR option4 LIKE ? OR option5 LIKE ? OR option6 LIKE ? OR option7 LIKE ? OR option8 LIKE ?) 
UNION 
(SELECT *, 'gallery_image' AS type, NULL AS categoryID, NULL AS categoryName, NULL AS full_description, NULL AS image1, NULL AS image2, NULL AS image3, NULL AS image4, NULL AS price FROM gallery_images WHERE title LIKE ? OR description LIKE ? OR option1 LIKE ? OR option2 LIKE ? OR option3 LIKE ? OR option4 LIKE ? OR option5 LIKE ? OR option6 LIKE ? OR option7 LIKE ? OR option8 LIKE ?)";
$selectFromTables = $this->db->prepare($query);
$selectFromTables->execute(array("%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%", "%" . $keyword . "%"));
$found = $selectFromTables->rowCount();
while($rowSearch = $selectFromTables->fetch(PDO::FETCH_ASSOC))
{
    $id = $rowSearch['id'];
    $type = $rowSearch['type'];
    $title = $rowSearch['title'];
    echo $id . "->" . $type . "<br />";
}

为什么 while 循环返回这个输出？

23->product
24->product
25->product
27->product
28->product
1->
1->
2->
4->
5->

但我希望得到这样的输出:

23->product
24->product
25->product
27->product
28->product
1->article
1->news
2->page
4->page
5->page

Answer 1

很有可能你是misusing the UNION statement .

The column names from the first SELECT statement are used as the column names for the results returned.

我打赌你所有的表都没有相同数量的列(请向我们展示所有涉及的表的完整描述)，使你的列别名无用。同样，您的 title 列可能不在所有表中的相同位置。