Issue with UNION Query

I'm trying what seems to be a simple union query for two tables in my database though I am unable to retrieve a successful array result. It should be noted that each table has different columns and that I'd like to create a virtual result (promo AS origin [for the result pulled from the 'venpromo' table] && vacation AS origin [for the result pulled from the 'vacation' table]) in order to sort the results into different array structures. I've looked everywhere and UNIONS are all using different syntax. Thank you all in advance!

<?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);

?>

When visiting the page to view the results, I see 'null'.

Answers


Put the columns names explicitly rather than *, and make sure the number of columns and data types match for the same column in each select.

I have added some dummy columns to match the number and data types in both the tables and also changed the order of the columns.

try this sample way ::

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;

Need Your Help

What is the difference betwen two methods?

javascript oop

I can call parse method of Date object directly as follows:

Wicket - How to use ListView in custom form component?

listview wicket wicket-1.6

I'm trying to implement a custom form component which contains a ListView.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.