Many to Many

[paul2463]

Hello guys

I have a database with many tables but one group in particular is confusing me, the group involves three tables

 

table 1 'aicrew'

idaircrew

'rank'

'firstname'

'lastname'

 

table 2 'flight'

idflight

`captain` (aircrew)

`pilot` (aircrew)

aircraft

flighttime

`date` (timestamp)

 

and because it is a many to many relationship as each flight has two aircrew and each aircrew can do many flights there is a resolved table to remove the many to many relationship called aircrew_has_flight

 

table 3 'aircrew_has_flight'

idaircrew

idflight

 

now I know how to pull the information for flights but i get confused about inserting a flight and pulling aircrew information for a particular flight.

 

$day = strtotime("NOW");
$start = strtotime("- 1 month", $day);//to get a date 1 months ago
$query = "SELECT * FROM 'flight' where `date` > '$start' ORDER BY `date` DESC"; //will list all flights in last month starting from the earliest

 

now I get confused going across the many to many to pull the last names of the aircrew who flew the flights, can any one help me

also when inserting a new flight do I have to insert information into the aircrew_has_flight table as well?

 

FENWAY????

[rhodesa]

I think a better structure would be:

 

table 1 'aicrew'

'idaircrew'

'rank'

'firstname'

'lastname'

 

table 2 'flight'

'idflight'

'aircraft'

'flighttime'

'date'

 

table 3 'flight_aircrew'

'idflight'

'idaircrew'

'position' (captain, pilot, etc)

 

Then if you want people who have flown this month:

$query = "SELECT * FROM aircrew WHERE idaircrew IN (SELECT fa.idaircrew FROM flight_aircrew fa INNER JOIN flight f ON fa.idflight = f.idflight WHERE f.date > '$start' ORDER BY f.date DESC";

[paul2463]

Then if you want people who have flown this month:

$query = "SELECT * FROM aircrew WHERE idaircrew IN (SELECT fa.idaircrew FROM flight_aircrew fa INNER JOIN flight f ON fa.idflight = f.idflight WHERE f.date > '$start' ORDER BY f.date DESC";

 

thanks for that Rhodesa , so if I want a list of flights that pilot a did in the last  month would I

$query1 = "SELECT idaircrew FROM aircrew WHERE lastname = 'Reynolds'";
//then the rest to get the idaircrew to equal $id
$query2 = "SELECT flighttime FROM flight WHERE idaircrew IN (SELECT fa.idaircrew FROM flight_aircrew fa INNER JOIN flight f ON fa.idflight = f.idflight WHERE f.date > '$start' ORDER BY f.date DESC && idaircrew = '$id'";
//would that list all flighttime for the pilot called Reynolds in the last month??

[rhodesa]

You can actually do it in one query...here it is with new lines/indents to make it more readable:

 

SELECT flighttime FROM flight
  WHERE date > '$start' AND
    idflight IN (
      SELECT fa.idflight FROM flight_aircrew fa 
        INNER JOIN aircrew a ON fa.idaircrew = a.idaircrew
        WHERE a.lastname = 'Reynolds'
    )
  ORDER BY date DESC;

[Barand]

JOINS are faster than subqueries

 

SELECT f.*
FROM flight f
    INNER JOIN flight_aircrew fa ON f.idflight = fa.idflight
    INNER JOIN aircrew a ON fa.idaircrew = a.idaircrew
WHERE a.lastname = 'Reynolds'

[paul2463]

thanks ever so much guys for that

so if I want to insert a new flight, would i insert the flight data into the `flight` table,  pull mysql_insert_id() and then put that along with the aircrewid into the `flight_aircrew` table ?

[Barand]

... so if I want to insert a new flight, would i insert the flight data into the `flight` table,  pull mysql_insert_id() and then put that along with the aircrewid into the `flight_aircrew` table ?

 

Yes, write a record for each aircrew member

[paul2463]

many thanks Barand and also to Rhodesa for your help in this matter, it is truly appreciated