Jump to content

MYSQL PHP query question


clukas

Recommended Posts

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 ;D !

Link to comment
Share on other sites

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

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 by Stefany93
Link to comment
Share on other sites

@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 by benanamen
Link to comment
Share on other sites

@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     |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+

  • Like 2
Link to comment
Share on other sites

 

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

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!

Link to comment
Share on other sites

 

@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?

Link to comment
Share on other sites

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 by Stefany93
Link to comment
Share on other sites

 

@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!

  • Like 1
Link to comment
Share on other sites

 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
Link to comment
Share on other sites

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 ;D 

  • Like 2
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.