SQL JOIN in PHP - is it possible to retrieve two different values from one column?

[untouchable8708]

Hey guys,

my first time in this forum, i have tried looking for the solution to my problem on this forum as well as others, however i have not found an answer yet. would much appreciate your help. I am building a web based database using php and mysql. i think this problem is more associated with mysql.

basically i have a two tables in the database: Location, Distance.

Location consists of the fields: Location_ID, name.
Distance consists of the fields: Distance_ID, origin, destination, distance.

the two fields origin and destination in the distance field, reference the Location_id.

what i am wondering is, is it possible to create a query that will out put a table with the following details: Distance_ID, Origin_name, Destination_name, distance.

for example:
Location_ID, name
1, london
2, paris
3, rome

Distance_ID, origin, destination, distance
1, 1, 2, 50
2, 1, 3, 75
3, 3, 2, 12

what i want to display is:
Distance_ID, Origin, Destination, Distance
1, london, paris, 50
2, london, rome, 75
3, rome, paris, 15

the code that i have so far is:

$query="SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
 
if ($num==0){
echo "<b>Sorry, there are no distances.</b>";
}
 
else while ($i < $num) {
 
$DistanceID=mysql_result($result,$i,"DistanceID");
$origin=mysql_result($result,$i,"Location.name");
$destination=mysql_result($result,$i,"Location.name");
$distance=mysql_result($result,$i,"distance");
 
echo "<tr><td>$DistanceID</td><td>$origin</td><td>$destination</td><td>$distance</td></tr>";
$i++;
}

what my code will output is :

Distance_ID, Origin, Destination, Distance
1, london, london, 50
2, london, london, 75
3, rome, rome, 15


this is not exactly what i want. i can sort of see where the problem is (the issue with Location.name), but i have no idea how to solve this, or whether it is even possible. i am not familiar with INNER JOIN, but i understand that

INNER JOIN Location ON Distance.origin=Location.locationID

gets stored in origin

$origin=mysql_result($result,$i,"Location.name")

aswell as destination

$destination=mysql_result($result,$i,"Location.name");

. and that only

INNER JOIN Location ON Distance.origin=Location.locationID

is declared for origin. but if i also do it for destination, the code breaks. i.e.

"SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID INNER JOIN Location ON Distance.destination=Location.locationID"

any ideas on how to display the names of both the origin and destination would be much appreciated.


edit: i guess i could always create 2 tables named location_origin and location_destination, but my idea was to save space by just having one table to serve the purpose of storing names of places. though i wonder if it would be a better/easier idea to have the two tables instead of one.

[shabinesh]

select distance_id, a1.name , a2.name from location a1 a2, distance where a1.name=$origin and a2.name=$destination;

I know this query is buggy but i think this should enlighten you that Inner join is not so neccessary.

PS: i am sql newbie ;)

[untouchable8708]

shabinesh,

thanks for your reply, i haven't tried your code, but i got some help from a different forum earlier which worked for me.

<Snipped. Please read the guidelines before posting -- Atli>

thanks for your reply though!