MYSQL PHP query question

clukas · January 15, 2017

Hi,

Im new to the forums, and wanted to kick off by asking a coding question.

Im building a php script and need help with mysqli scripting.

I have a database similar to the following:

dep_airport arr_airport flt_num

LHR JFK 100

LHR FRA 200

JFK MIA 300

The user for instance wants to fly from MIA to FRA, however theres no direct connection, so a simple query such as

SELECT * FROM schedules WHERE dep_airport = 'MIA', arr_airport = 'FRA'

would not work as the user would need to stop over in JFK and LHR to catch a connecting flight.

How can I structure a php query for it to find and display that connection?

Many Thanks !

Stefany93 · January 16, 2017

I suppose you will need a one-many-relationships (junction) table to prevent duplicate data.

But let me show you the simplest way:

Let's say you want to fly from JFK to SOF

You have a table called schedule below:

id, airport, flies_to

1 SOF FRA

2 FRA SOF

3 JFK FRA

4 FRA JFK

5 ATH SOF

First you check if JFK flies directly to SOF and if the query returns null, then you run this one:

SELECT airport FROM schedule WHERE flies_to = 'SOF' # FRA, ATH

Edited January 16, 2017 by Stefany93

benanamen · January 16, 2017

@clukas,

With your table you don't need any relationships or junction tables and you don't need more than one query.

Output from code:

Array(    [Flight One] => 300    [Depart One] => MIA    [Arrive One] => JFK    [Flight Two] => 100    [Depart Two] => JFK    [Arrive Two] => LHR    [Flight Three] => 200    [Depart Three] => LHR    [Arrive Three] => FRA)

Flight Itenerary
1. Flight 300 MIA To JFK
2. Flight 100 JFK To LHR
3. Flight 200 LHR To FRA

<?php
$hostdb   = 'localhost';
$dbname   = 'flights';
$username = 'root';
$password = '';

    $pdo = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT
    f1.flt_num AS 'Flight One',
    f1.arr_airport AS 'Depart One',
    f1.dep_airport AS 'Arrive One',
    f2.flt_num AS 'Flight Two',
    f2.arr_airport AS 'Depart Two',
    f2.dep_airport AS 'Arrive Two',
    f3.flt_num AS 'Flight Three',
    f3.dep_airport AS 'Depart Three',
    f3.arr_airport AS 'Arrive Three'
FROM
    myflights AS f1,
    myflights AS f2,
    myflights AS f3
WHERE
    f1.id = 3
AND f2.id = 1
AND f3.id = 2";

    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    echo "<pre>";
    print_r($row);
    echo "</pre>";
?>

Flight Itenerary
<br>
1. Flight <?= $row['Flight One'] ?> <?= $row['Depart One'] ?> To <?= $row['Arrive One'] ?>
<br>
2. Flight <?= $row['Flight Two'] ?> <?= $row['Depart Two'] ?> To <?= $row['Arrive Two'] ?>
<br>
3. Flight <?= $row['Flight Three'] ?> <?= $row['Depart Three'] ?> To <?= $row['Arrive Three'] ?>

-- ----------------------------
-- Table structure for myflights
-- ----------------------------
DROP TABLE IF EXISTS `myflights`;
CREATE TABLE `myflights` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dep_airport` varchar(255) DEFAULT NULL,
  `arr_airport` varchar(255) DEFAULT NULL,
  `flt_num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of myflights
-- ----------------------------
INSERT INTO `myflights` VALUES ('1', 'LHR', 'JFK', '100');
INSERT INTO `myflights` VALUES ('2', 'LHR', 'FRA', '200');
INSERT INTO `myflights` VALUES ('3', 'JFK', 'MIA', '300');

Edited January 16, 2017 by benanamen

Barand · January 16, 2017

@benanamen, the objective is to find the connections, not input the results just to print them out ::)

Data

mysql> SELECT * FROM myflights;
+----+-------------+-------------+---------+
| id | dep_airport | arr_airport | flt_num |
+----+-------------+-------------+---------+
|  1 | LHR         | JFK         |     100 |
|  2 | LHR         | FRA         |     200 |
|  3 | JFK         | MIA         |     300 |
|  4 | MCR         | FRA         |     400 |
|  5 | MCR         | MIA         |     500 |
|  6 | JFK         | LHR         |     150 |
|  7 | FRA         | LHR         |     250 |
|  8 | MIA         | JFK         |     350 |
|  9 | FRA         | MCR         |     450 |
| 10 | MIA         | MCR         |     550 |
| 13 | MIA         | FRA         |     600 |
| 13 | FRA         | MIA         |     650 |
+----+-------------+-------------+---------+

Query

-- DIRECT FLIGHTS
SELECT
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , NULL as flight2
  , NULL as depart2
  , NULL as arrive2
  , NULL as flight3
  , NULL as depart3
  , NULL as arrive3
FROM myflights f1
 WHERE 
    f1.dep_airport = 'MIA'
    AND f1.arr_airport = 'FRA'
    
UNION
-- ONE CONNECTION
SELECT
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , f2.flt_num as flight2
  , f2.dep_airport as depart2
  , f2.arr_airport as arrive2
  , NULL as flight3
  , NULL as depart3
  , NULL as arrive3
FROM myflights f1
    JOIN myflights f2 ON f1.arr_airport = f2.dep_airport
 WHERE 
    f1.dep_airport = 'MIA'
    AND f2.arr_airport = 'FRA'

UNION
-- TWO CONNECTIONS
SELECT 
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , f2.flt_num as flight2
  , f2.dep_airport as depart2
  , f2.arr_airport as arrive2
  , f3.flt_num as flight3
  , f3.dep_airport as depart3
  , f3.arr_airport as arrive3

FROM myflights f1
    LEFT JOIN 
  myflights f2 ON f1.arr_airport = f2.dep_airport
    LEFT JOIN 
  myflights f3 ON f2.arr_airport = f3.dep_airport
WHERE 
    f1.dep_airport = 'MIA'
    AND f3.arr_airport = 'FRA'
    AND 'MIA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops
    AND 'FRA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops

Result

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| flight1 | depart1 | arrive1 | flight2 | depart2 | arrive2 | flight3 | depart3 | arrive3 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|     600 | MIA     | FRA     |         |         |         |         |         |         |
|     550 | MIA     | MCR     |     400 | MCR     | FRA     |         |         |         |
|     350 | MIA     | JFK     |     150 | JFK     | LHR     |     200 | LHR     | FRA     |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+

clukas · January 16, 2017

I suppose you will need a one-many-relationships (junction) table to prevent duplicate data.

But let me show you the simplest way:

Let's say you want to fly from JFK to SOF

You have a table called schedule below:

id, airport, flies_to

1 SOF FRA

2 FRA SOF

3 JFK FRA

4 FRA JFK

5 ATH SOF

First you check if JFK flies directly to SOF and if the query returns null, then you run this one:
SELECT airport FROM schedule WHERE flies_to = 'SOF' # FRA, ATH

Why would a one-many-relationships (junction) table be necessary? Lets assume id acts as the flight number, then duplicate entries won't be a problem as multiple flights can exist, I was simplifying the database.

Also what does this do?

# FRA, ATH

Many thanks for your help, much appreciated!

clukas · January 16, 2017

@benanamen, the objective is to find the connections, not input the results just to print them out ::)

Data

mysql> SELECT * FROM myflights;
+----+-------------+-------------+---------+
| id | dep_airport | arr_airport | flt_num |
+----+-------------+-------------+---------+
|  1 | LHR         | JFK         |     100 |
|  2 | LHR         | FRA         |     200 |
|  3 | JFK         | MIA         |     300 |
|  4 | MCR         | FRA         |     400 |
|  5 | MCR         | MIA         |     500 |
|  6 | JFK         | LHR         |     150 |
|  7 | FRA         | LHR         |     250 |
|  8 | MIA         | JFK         |     350 |
|  9 | FRA         | MCR         |     450 |
| 10 | MIA         | MCR         |     550 |
| 13 | MIA         | FRA         |     600 |
| 13 | FRA         | MIA         |     650 |
+----+-------------+-------------+---------+

Query

-- DIRECT FLIGHTS
SELECT
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , NULL as flight2
  , NULL as depart2
  , NULL as arrive2
  , NULL as flight3
  , NULL as depart3
  , NULL as arrive3
FROM myflights f1
 WHERE 
    f1.dep_airport = 'MIA'
    AND f1.arr_airport = 'FRA'
    
UNION
-- ONE CONNECTION
SELECT
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , f2.flt_num as flight2
  , f2.dep_airport as depart2
  , f2.arr_airport as arrive2
  , NULL as flight3
  , NULL as depart3
  , NULL as arrive3
FROM myflights f1
    JOIN myflights f2 ON f1.arr_airport = f2.dep_airport
 WHERE 
    f1.dep_airport = 'MIA'
    AND f2.arr_airport = 'FRA'

UNION
-- TWO CONNECTIONS
SELECT 
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , f2.flt_num as flight2
  , f2.dep_airport as depart2
  , f2.arr_airport as arrive2
  , f3.flt_num as flight3
  , f3.dep_airport as depart3
  , f3.arr_airport as arrive3

FROM myflights f1
    LEFT JOIN 
  myflights f2 ON f1.arr_airport = f2.dep_airport
    LEFT JOIN 
  myflights f3 ON f2.arr_airport = f3.dep_airport
WHERE 
    f1.dep_airport = 'MIA'
    AND f3.arr_airport = 'FRA'
    AND 'MIA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops
    AND 'FRA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops

Result

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| flight1 | depart1 | arrive1 | flight2 | depart2 | arrive2 | flight3 | depart3 | arrive3 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|     600 | MIA     | FRA     |         |         |         |         |         |         |
|     550 | MIA     | MCR     |     400 | MCR     | FRA     |         |         |         |
|     350 | MIA     | JFK     |     150 | JFK     | LHR     |     200 | LHR     | FRA     |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+

That looks excellent. How could this be scripted in php?

Stefany93 · January 16, 2017

Why would a one-many-relationships (junction) table be necessary? Lets assume id acts as the flight number, then duplicate entries won't be a problem as multiple flights can exist, I was simplifying the database.

Also what does this do?
# FRA, ATH
Many thanks for your help, much appreciated!

Don't use id as a flight number - make a new key and set it as primary. Always use a primary auto incrementing key in any table.

I am sorry, I spelled it wrong, it is many-to-many-relationships table (junction) that is if you went with a different DB design - personally I wouldn't want the codes of the airports being repeated in multiple rows, but would prefer ids and then having them bridged in the junction.

However, Barand's answer is better since it uses your existing design.

As for the

# FRA, ATH

# is comment in SQL. I was trying to show you the result of the query, as usually in programming tutorials they will use // to show the results of a statement.

I'd also suggest using more descriptive column names - I couldn't figure out what flt_num stood for, for the longest time.

Edited January 16, 2017 by Stefany93

Stefany93 · January 16, 2017

@benanamen, the objective is to find the connections, not input the results just to print them out ::)

Data

mysql> SELECT * FROM myflights;
+----+-------------+-------------+---------+
| id | dep_airport | arr_airport | flt_num |
+----+-------------+-------------+---------+
|  1 | LHR         | JFK         |     100 |
|  2 | LHR         | FRA         |     200 |
|  3 | JFK         | MIA         |     300 |
|  4 | MCR         | FRA         |     400 |
|  5 | MCR         | MIA         |     500 |
|  6 | JFK         | LHR         |     150 |
|  7 | FRA         | LHR         |     250 |
|  8 | MIA         | JFK         |     350 |
|  9 | FRA         | MCR         |     450 |
| 10 | MIA         | MCR         |     550 |
| 13 | MIA         | FRA         |     600 |
| 13 | FRA         | MIA         |     650 |
+----+-------------+-------------+---------+

Query

-- DIRECT FLIGHTS
SELECT
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , NULL as flight2
  , NULL as depart2
  , NULL as arrive2
  , NULL as flight3
  , NULL as depart3
  , NULL as arrive3
FROM myflights f1
 WHERE 
    f1.dep_airport = 'MIA'
    AND f1.arr_airport = 'FRA'
    
UNION
-- ONE CONNECTION
SELECT
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , f2.flt_num as flight2
  , f2.dep_airport as depart2
  , f2.arr_airport as arrive2
  , NULL as flight3
  , NULL as depart3
  , NULL as arrive3
FROM myflights f1
    JOIN myflights f2 ON f1.arr_airport = f2.dep_airport
 WHERE 
    f1.dep_airport = 'MIA'
    AND f2.arr_airport = 'FRA'

UNION
-- TWO CONNECTIONS
SELECT 
    f1.flt_num as flight1
  , f1.dep_airport as depart1
  , f1.arr_airport as arrive1
  , f2.flt_num as flight2
  , f2.dep_airport as depart2
  , f2.arr_airport as arrive2
  , f3.flt_num as flight3
  , f3.dep_airport as depart3
  , f3.arr_airport as arrive3

FROM myflights f1
    LEFT JOIN 
  myflights f2 ON f1.arr_airport = f2.dep_airport
    LEFT JOIN 
  myflights f3 ON f2.arr_airport = f3.dep_airport
WHERE 
    f1.dep_airport = 'MIA'
    AND f3.arr_airport = 'FRA'
    AND 'MIA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops
    AND 'FRA' NOT IN (f2.dep_airport, f3.dep_airport) -- prevent loops

Result

+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| flight1 | depart1 | arrive1 | flight2 | depart2 | arrive2 | flight3 | depart3 | arrive3 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|     600 | MIA     | FRA     |         |         |         |         |         |         |
|     550 | MIA     | MCR     |     400 | MCR     | FRA     |         |         |         |
|     350 | MIA     | JFK     |     150 | JFK     | LHR     |     200 | LHR     | FRA     |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+

WOW - longest query I have ever seen. Beautiful!

Barand · January 16, 2017

How could this be scripted in php?

In practice there would be other variables, such as departure day and times. For example, the direct flight may only be available at weekend, Or one leg of a two-leg flight requires an eight hour wait for the second. Then there is price - the three leg flight might be the cheapest option.

There is also the question of how you want to use it. If the user only wants direct flights then you might use only the first part of the query then run the second only if there isn't a direct flight available. And the similarly with the three-leg option.

So how you script it will depend on circumstance, there isn't one way.

The general method would be

Define the SQL (done), using placeholders instead of the hard-coded depart and arrive codes.
Prepare the query
Execute it, passing the depart and arrive as parameters.
Process the results as required

benanamen · January 16, 2017

@barand, where did you get the additional flight info? OP pm you? I only had the 3 records to work with.

Barand · January 16, 2017

They were straight out of my imagination, to give me more test options

benanamen · January 16, 2017

Hmm. What would you have done with using only the data the OP provided. I did start out with a UNION with OP's data but wasn't sure if he needed a multi-row result. Data is still the same, just one row and a third the code.

Barand · January 16, 2017

The OP's original data only allowed a three-leg solution, so only the final select would have been required. But then it wouldn't find any direct flights or two-leg solutions, if they existed, as they do in my extended data.

But at least it does search for solutions. With your query you need to know the result, pass the required flight numbers as parameters to the WHERE clause, then merely output the already known result

Sign In

MYSQL PHP query question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information