PHP MYSQL. How to retrieve the same data from a table based on 2 different ID's in another table

Question

I'm building a website for Airplane Tickets, i have 2 tables, AIRPORTS and AIRPLANES, the AIRPLANES table has 2 columns containing its airport destination id(ID_DESTINATION) and its airport origin id(ID_ORIGIN), what i want to do is to loop all of the AIRPLANES using while with the AIRPORT_NAME as its origin and destination from the AIRPORTS table, how can i do that?

this is the query i'm using :

SELECT a.ID_PLANE, a.PLANE_NAME, 
       a.ID_ORIGIN, a.ID_DESTINATION, 
       a.TAKEOFF, a.LANDING, a.PRICE,
       b.ID_AIRPORT, b.AIRPORT_NAME
FROM AIRPLANES AS a
LEFT JOIN AIRPORTS AS b
ON a.ID_ORIGIN = b.ID_AIRPORT

Thanks in advance.

Welcome to StackOverflow! We will be glad to help you if you get stuck on a specific programming problem, but we are not here to write code for you. Please see How do I ask a good question? and What topics can I ask about here?. — Alex Howansky
– Alex Howansky, Commented Jun 6, 2017 at 19:42
Provide some sample data and expected results. It appears you may just need two joins to airports with different aliases. Once for groin, once for destination. SQL operates best in terms of "SETS" operating one at a time in loops is sub optimal for database systems. — xQbert
– xQbert, Commented Jun 6, 2017 at 19:49
@AlexHowansky I'm sorry, but i'm not asking anyone to write codes for me, I'm just asking "How do i retrieve the same data in a specific table based on 2 different ids from another table" , should i use join? union? transaction? cz i've tried them all, and its still not working — faiqkaboel
– faiqkaboel, Commented Jun 6, 2017 at 19:50
No. Just an additional LEFT join to airports (example below) — xQbert
– xQbert, Commented Jun 6, 2017 at 20:01

xQbert · Accepted Answer · 2017-06-06 19:52:31Z

1

Notice I aliased O for origin D for destination Assuming you just want the names of airports for each airplane (flight)

SELECT a.ID_PLANE
     , a.PLANE_NAME
     , a.ID_ORIGIN
     , a.ID_DESTINATION
     , a.TAKEOFF
     , a.LANDING
     , a.PRICE
     , o.ID_AIRPORT as Orig_ID
     , o.AIRPORT_NAME Orig_Name
     , d.ID_AIRPORT as Dest_ID
     , d.AIRPORT_NAME Dest_Name
FROM AIRPLANES AS a
LEFT JOIN AIRPORTS AS o
  ON a.ID_ORIGIN = b.ID_AIRPORT
LEFT JOIN AIRPORTS d
  ON a.ID_DESTINATION = b.ID_AIRPORT

answered Jun 6, 2017 at 19:52

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

N. Kue · Accepted Answer · 2017-06-06 19:47:00Z

1

Why dont you just use a sql-join?
http://www.sql-join.com/

answered Jun 6, 2017 at 19:47

N. Kue

771 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL. How to retrieve the same data from a table based on 2 different ID's in another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related