php - UNION 查询的问题

Question

尽管我无法检索成功的数组结果，但我正在尝试对数据库中的两个表进行看似简单的联合查询。应该注意的是，每个表都有不同的列，我想创建一个虚拟结果(promo AS origin [用于从“venpromo”表中提取的结果] && Vacation AS origin [用于从“vacation”表中提取的结果) ' table]) 以便将结果排序到不同的数组结构中。我到处都看过，UNIONS 都使用不同的语法。提前谢谢大家!

<?php
    require_once('includes/config.php');

    $event_query = "SELECT *, promo AS origin FROM venpromo
    UNION 
    SELECT *, vacation AS origin FROM venpromo 
    ORDER BY popularity DESC";
    $event_result = mysql_query($event_query, $connection);
    while ($event = mysql_fetch_array($event_result)) {
        if ($event['origin'] == "promo") {
            $event_array[] = array(
                'id' => $event['id'],
                'title' => $event['calname'],
                'start' => $event['sdate'],
                'end' => $event['edate'],
                'color' => "red",
                'url' => "http://www.norrisportal.com/bulletinpost.php?id=" . $event['id'] . "&hashkey=akdULjsjyUpYyTzOT7"
            );
        } elseif ($event['origin'] == "vacation") {
            $event_array[] = array(
                'id' => $event['id'],
                'title' => $event['reason'],
                'start' => $event['vacstart'],
                'end' => $event['vacend'],
                'color' => "blue"
            );
        }
    }

    echo json_encode($event_array);

?>

当访问页面查看结果时，我看到“null”。

Answer 1

明确放置列名称而不是 *，并确保列数和数据类型与每个选择中的同一列匹配。

我添加了一些虚拟列来匹配两个表中的数字和数据类型，并更改了列的顺序。

尝试这个示例方法::

select 
id,
uid,
approval,
vacstart,
vacend,
reason,
'dummy1' col1,
'dummy2' col2,
'dummy3' col3,
curdate() col4,
'dummy4' col5,
'dummy5' col6,
'dummy6' col7,
'dummy7' col8,
promo AS origin
from vacation
union
select
id,
venid,
authid,
sdate,
edate,
authname,
tags,
title,
calname,
date,
intro,
body,
sum,
sortdate,
vacation AS origin
from venpromo;