Jump to content

Many to Many


paul2463

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/93724-many-to-many/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/93724-many-to-many/#findComment-480247
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/93724-many-to-many/#findComment-480271
Share on other sites

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;

Link to comment
https://forums.phpfreaks.com/topic/93724-many-to-many/#findComment-480306
Share on other sites

... 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

Link to comment
https://forums.phpfreaks.com/topic/93724-many-to-many/#findComment-480796
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.