UNION Query in the Same Table

mramonster

//Query the Events
$query  = "SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
			FROM eventstable
				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='Live')
			UNION
			SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
			FROM eventstable
				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='TV') AND air_date != '0000-00-00' AND air_time != '00:00:00'
    				ORDER BY a, state, city 
						LIMIT $offset, $rowsPerPage";

I am unable to get this UNION to work. Each Query Works Separately but not together.

dagon

put brackets around the two select statements. orderby will be outside the last bracket

12.2.8.3. UNION Syntax

mramonster

I've tried that. There's no error from MySQL either:

//Query the Events
$query  = "( SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
			FROM eventstable
				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='Live') )
			UNION
			( SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
			FROM eventstable
				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='TV') AND air_date != '0000-00-00' AND air_time != '00:00:00' )
    				ORDER BY a, state, city 
						LIMIT $offset, $rowsPerPage";				

$result = mysql_query($query) 
or die('Error, Event Query Failed.  Report to Administrator.') 
. mysql_error();

while(list($start_date, $end_date, $title, $state, $country, $type, $channel, $url, $org, $monster, $added) = mysql_fetch_array($result))
{
//List in Table Here 
}