CODEWITHSUNDEEP
phpmysql
Farhad 95
Farhad 95

Reputation: 9

Inner Join problems PHP

I have three tables: bound, station and time

Bound has 2 columns: boundID and boundName

Station has 3 columns: stationID stationName boundID

Time has 4 columns: timeID departureTime tramID stationID

I am wanting to display the startStation and endStation the user enters into the form and display the time if the stations entered by the user in the textfields have the same boundID. Currently, this is all I have managed to do and can't seem to get a result. Any help would be greatly appreciated!

    //display tram times from one station to another        
    ELSE if($startStation != '' && $endStation !='' && $DepartureTime =='' && $DepartureTime2 =='')
    $query ="SELECT b.boundName, s.stationName, t.departureTime
    FROM Station s, Time t, Bound b
    INNER JOIN Bound b
    ON S.boundID=B.boundID
    WHERE s.stationName = '$startStation'AND'$endStation'";

I get this message when I try this: "Not unique table/alias: 'b'"

This is an example of the data I have:

stationID stationName boundID

5 | CitySquare | 2 ________________________________________________________________________________ boundID boundName

2 | South-Southbound ________________________________________________________________________________

timeID departureTime tramID stationID

1 | 07:18:00 | 1 | 5

The expected result is the user is able to insert a startStartion and an endStation and obtain the departure time, if the boundID are the same.

This is the result I want to output

Bound Name | Start Station | End Station | Departure Time

South-SouthBound | Stourton | CitySquare | 09:49:00

Upvotes: 0

Views: 54

Answers (3)

Alex
Alex

Reputation: 17289

Your DB schema is a bit weird to me. From my point of view you are missing a lot of necessary data columns in your tables.

But just to get your expected result you can try this query:

http://sqlfiddle.com/#!9/e5ef0/1

SELECT 
   b.boundName `Bound Name`,
   s.stationName  `Start Station`,
   e.stationName `End Station`,
   t.departureTime `Departure Time`
FROM station s
INNER JOIN station e
ON e.boundId = s.boundId
  AND e.stationName='$endStation'
INNER JOIN bound b
ON b.boundId=s.boundId
INNER JOIN `time` t
ON t.stationId=s.stationId
WHERE s.stationName = '$startStation'

Upvotes: 0

Arun
Arun

Reputation: 3731

Change the below portion of code

INNER JOIN Bound b

with

INNER JOIN Bound c

and change the alias names of the columns too. There are too same alias assignment found in your query

It will make confusion while compile the query. Mysql cound not understand which table you are referring to.

Edit:

Try with the below code. I am not sure which table have the "boundID" field

 $query ="SELECT b.boundName, s.stationName, t.departureTime
FROM Station s, Time t, Bound b
INNER JOIN Bound c
ON s.boundID=b.boundID
WHERE s.stationName = '$startStation'AND'$endStation'";

Upvotes: 0

Suyog
Suyog

Reputation: 2482

Try changing your query to something like below:

SELECT b.boundName as bond_name, s.stationName as station_name, t.departureTime as departure_time
FROM Station as s, Time as t, Bound as b
INNER JOIN Bound as b
ON s.boundID = b.boundID
WHERE s.stationName = '$startStation' AND s.stationName = '$endStation'

Upvotes: 0

Related Questions