Joining search queries

[Alec]

Newbie question.

Its really an SQL question, but have had no reply from the SQL
newsgroup.

I want to search the same table for two different criteria, and then
join the search results together into one new list. See below.

<?php

$result = @mysql_query ("SELECT company, priority FROM table1 WHERE
priority ='high' "); UNION; @mysql_query ("SELECT company, priority
FROM table1 WHERE priority ='low' ");

while ($row = mysql_fetch_array($result))

{

$company = $row['company']; echo $company;
$priority = $row['priority']; echo $priority;

}
?>

BUT it only returns the first search result, even though there are
records that relate to the second.

What am I doing wrong? Also can UNION be used to combine more than two
searches?

Many thanks

Alec

[Jerry Stuckle]

Alec wrote:

Newbie question.

Its really an SQL question, but have had no reply from the SQL
newsgroup.

I want to search the same table for two different criteria, and then
join the search results together into one new list. See below.

<?php

$result = @mysql_query ("SELECT company, priority FROM table1 WHERE
priority ='high' "); UNION; @mysql_query ("SELECT company, priority
FROM table1 WHERE priority ='low' ");

while ($row = mysql_fetch_array($result))

{

$company = $row['company']; echo $company;
$priority = $row['priority']; echo $priority;

}
?>

BUT it only returns the first search result, even though there are
records that relate to the second.

What am I doing wrong? Also can UNION be used to combine more than two
searches?

Many thanks

Alec

Where did you ask? I didn't see it in comp.databases.mysql, which is
where you should be asking.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Boris Stumm]

Alec wrote:

$result = @mysql_query ("SELECT company, priority FROM table1 WHERE
priority ='high' "); UNION; @mysql_query ("SELECT company, priority
FROM table1 WHERE priority ='low' ");

First thing to remember: post readable code.

$result = @mysql_query("SELECT company, priority FROM table1
WHERE priority ='high' ");
UNION;
@mysql_query ("SELECT company, priority FROM table1
WHERE priority ='low' ");

Now it is quite visible what is happening. You only assign the
first query to $result.

Solution if the only priorities that exist are "high" and "low":

SELECT company, priority FROM table1;

Other solution:

SELECT company, priority FROM table 1
WHERE priority in ('high', 'low');

The less-than-optimal, but correct UNION-solution:

SELECT company, priority FROM table 1
WHERE priority ='high')
UNION
SELECT company, priority FROM table 1
WHERE priority = 'low');

You REALLY should read some SQL-Tutorial, because this is
really BASIC knowledge about SQL.