combining 2 columns in select query

darko886

Hello, I have this code:

$result = mysql_query( "SELECT DISTINCT Departure, Arrival FROM `$division` WHERE `Aircraft`='$aircraft' ORDER BY `Aircraft` ASC" ) 
or die("SELECT Error: ".mysql_error());

And then I am displaying Departures and Arrivals in one table with the following code:

while ($row = mysql_fetch_assoc($result)){ 
echo ' 
<tr> 
<td><p style="font-size:small;"><a href="flightplans2.php?division='.$division.'&aircraft='.$row['Aircraft'].'&airport='.$row['Departure'].'" STYLE="TEXT-DECORATION: NONE">'.$row['Departure'].'</a></td>
</tr>
<tr>
<td><p style="font-size:small;"><a href="flightplans2.php?division='.$division.'&aircraft='.$row['Aircraft'].'&airport='.$row['Arrival'].'" STYLE="TEXT-DECORATION: NONE">'.$row['Arrival'].'</a></td>
</tr>'; 
} 
echo ' 
</table>'; 
?>

The problem is that if for example there are 2 of the same departures but with different arrivals it only shows one of them. For example if I have a 2 departures from Test1 and the 1st departures arrival in test2 and the second departures arrival is test3 then it will only show test2 in the table and not both test2 and test3... how can i make it show both without making it show test1 twice?

What I was thinking is is there a way to combine those two temporarily into one column and then do a distinct select?

Thanks!

nickdrums

I might be wrong on this, but don't you need to modify your query like this:

SELECT DISTINCT Departure, DISTINCT Arrival FROM $division..........

sneakyimp

First off, I don't see any good reason to order by Aircraft if all of your records have the same value where Aircraft=$aircraft.

Second, you might try removing the DISTINCT keyword. If you have three records like this:

ID     Departure     Arrival   SomeOtherField
100    1pm           3pm       foo
101    1pm           3pm       bar
102    1pm           4pm       foobar

Then your query should only return two records in the result because record 100 and 101 have the same values for arrival and departure:

Departure     Arrival
1pm           3pm
1pm           4pm

Removing distinct should return these records

Departure     Arrival
1pm           3pm
1pm           3pm
1pm           4pm

It's not clear from your post why you want DISTINCT in there anyway

darko886

the departure and arrival are cities, and i want the user to be able to click on the city and see all departures and arrivals for that city, so i dont want it repeating.
Using SELECT DISTINCT Departure, DISTINCT Arrival FROM $division.......... gives me the following error:
SELECT Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT Arrival FROM Business Jet WHERE Aircraft='Aircraft1'' at line 1

sneakyimp

In that case your query looks sound to me. Using the DISTINCT keyword as you should mean that each arrival/departure combination should appear only once. If you feel like another record should be listed that is not, perhaps it has a different aircraft value? Or perhaps it is in some other table as specified by $division?

darko886

it isnt right because if for example there are 2 flights from orlando, one to new york and one to chicago, it will only list 2 cities, orlando and new york and wont show chicago...

darko886

im pretty sure that you can do what I need to be done with the Join function but I cant figure it out

darko886

Nevermind, I got it using the UNION command 🙂 Thanks for everyones help