Need correct syntax for UNION in queries

SCRUBBIE1

HELP!!I Can't get UNION to work!!
In the below code, each query works correctly if I put it by itself, I get the right results. When I put them together with UNION, as below, I only get results from the second query.

I think its a syntax issue, and I can't find anything to show me the correct syntax in PHP. So I need to have a unique variable for each query joined by UNION? Do I call a $result after each query, or at the end of the UNION query? I'm getting nowhere on my own...

SOMEBODY HELP!!

code:--------------------------------------------------------------------------------<?
if (isset($_POST['Submit'])) { // Search database.

$message = NULL;
}
if ($POST['month']) {
$mo = $POST['month'];
}

if ($POST['year']) {
$y = $POST['year'];
}

if ($POST['salesperson']) {
$sp = $POST['salesperson'];
}

$query = "SELECT COUNT(deal_id)AS '# Sales' FROM salesdemo WHERE ";

if(isset($POST['month']) && ($POST['month']) !='00'){
$query .= "month = '$mo' AND ";
}

if(isset($POST['year']) && ($POST['year']) !='00'){
$query .= "year = '$y' AND ";
}
if(isset($POST['salesperson']) && ($POST['salesperson']) !='00'){
$query .= "salesperson = '$sp' AND ";
}
//minus the trailing 'AND '
$query=substr($query, 0, strlen($query)-4) ;

UNION;

$query = "SELECT COUNT(etch)AS '# Etch' FROM salesdemo WHERE etch !='0.00' AND ";

if(isset($POST['month']) && ($POST['month']) !='00'){
$query .= "month = '$mo' AND ";
}

$result=mysql_query($query)or die("Invalid query: " . mysql_error());

tsinka

Hi,

the union query in your case should look like (in short):

(SELECT COUNT(deal_id) AS cnt FROM salesdemo WHERE .....)
UNION ALL
(SELECT COUNT(etch) AS cnt FROM salesdemo WHERE....)

MySQL will return a result set with two rows and will use the column names/aliases of the first query for all rows in the result set.

In this case the first row in the result set contains the deal_id count and the second row contains the etch count.

Thomas

SCRUBBIE1

Thomas,
I tried to do that, but eveytime I put () around the query, I get a mysql error. I think the concatentated query is messing me up, but I need to do it that way to get the correct results.
I am having trouble with the union for this query, as well

For example, does UNION ALL need to be

UNION ALL;

$query.=UNION ALL;

or something else?

Thanks for the help! Cori

tsinka

Ok,

add

echo $query;

just in front of the mysql_query line to check how the query looks like and post the result.

Thomas

Roger_Ramjet

Scrubbie, UNION is NOT a PHP statement, it is SQL. So your attempts to use it in PHP to join the 2 queries will not work. It should be

$query = "SELECT COUNT(deal_id) AS count FROM salesdemo WHERE ";

if(isset($_POST['month']) && ($_POST['month']) !='00'){
$query .= "month = '$mo' AND ";
}

if(isset($_POST['year']) && ($_POST['year']) !='00'){
$query .= "year = '$y' AND ";
}
if(isset($_POST['salesperson']) && ($_POST['salesperson']) !='00'){
$query .= "salesperson = '$sp' AND ";
}
//minus the trailing 'AND '
$query=substr($query, 0, strlen($query)-4) ;


$query .= " UNION ";

$query .= "SELECT COUNT(etch) AS count FROM salesdemo WHERE etch !='0.00' AND ";

if(isset($_POST['month']) && ($_POST['month']) !='00'){
$query .= "month = '$mo' AND ";
}

if(isset($_POST['year']) && ($_POST['year']) !='00'){
$query .= "year = '$y' AND ";
}
if(isset($_POST['salesperson']) && ($_POST['salesperson']) !='00'){
$query .= "salesperson = '$sp' AND ";
}
//minus the trailing 'AND '
$query=substr($query, 0, strlen($query)-4) ;

Notice that I have changed your aliase names. As thomas said, the column names from the first select will be used so why waste time putting in diffrent ones. AND NEVER BREAK NAMING CONVENTIONS BY USING SPACES OR SPECIAL CHARS ETC. Have a look at the manual for valid column names and reserved words.

SCRUBBIE1

Roger,
Thanks for the more thorough explanation, it was very helpful!
'Preciate ya!
Cori