Im learning PHP and currently creating a website for a local football club.
They have multiple teams at various age groups. I have created a database of 'team' which has all of the information with regards to team name, which league, team photo, training information. I also have a 'coaches' table which as name, dob, qualifications, contact info. How would i add information to be displayed in a table that link the two.

So in 'coach1' and 'coach2' it will display the name of the two coaches linked to that club.

I am thinking that when i create a new coach (via form) they will be able to select the team from the database via a dropdown box.

what would be the best way to link this together, when a coach is signing up, selecting their team from the dropdown. They are linked. but some teams have two coaches, im guessing the second coach selecting the team would then overwrite the previous, how would you add this as an additional coach? Im thinking that i would add an additional drop down that would fill in 'role' column with either Manager or Assistant, i would then have to add field where 'SELECT coach WHERE role = Assistant AND id = $id'

          <table class="table">

            <thead>

              <tr>

                <th>Team Name</th>

                <th>Training</th>

                <th>League</th>

                <th>Coaches</th>

                <th></th>

                <th></th>

              </tr>

            </thead>

            <tbody>

<?php

    $no     = 1;

    $total  = 0;

    while ($row = mysqli_fetch_array($team)) 

    {

        echo '<tr>

                <td>'." u".$row['age_group']." ".$row['team_name'].'</td> 

                <td>'.$row['training_day'].", ".$row['location']." at ".$row['training_time'].'</td>

                <td>'.$row['league']." - ".$row['match_day'].'</td> 

                <td>'.$row['coach1'].'</td> 

                <td>'.$row['coach2'].'</td>

                <td><a href="viewapp.php?id='.($row['team_id']).'" class="btn btn-warning pull-right btn-xs">View</a></td> 

                </tr>';    

        $no++;

    }?>

</tbody>

          </table>

Since it's DB design only, let's explode the problem.

You have coaches and teams.

Case 1 : A coach can belong to 1 team

A unique coach belongs to a unique team (this is a 1,1 relation)

A unique team can have one or many coaches. (this is a 1,n relation)

We can draw a relational schema of those entities this way :

[coach] -(1, 1)-> [coach_team] <-(1, n)- [team]

In this kind of relation, the foreign key of the team will be stored in the coach table

As exemple, I have 2 teams, foo and bar. And 3 coaches, John Smith, Jane Doe and Zinedine Zidane. John and Jane are the coaches of the team foo and Zidane is the coach of the team bar.

Let's consider the table team has 2 fields, id and name and the table coach has 3 fields, id, name and team_id. We can build the tables this way :

CREATE TABLE team

(

  id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(255) NOT NULL

);



CREATE TABLE coach

(

  id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(255) NOT NULL,

  team_id INT(6) NOT NULL,

  CONSTRAINT FOREIGN KEY(team_id) REFERENCES team(id)

);



INSERT INTO team VALUES (default, "foo"), (default, "bar");



INSERT INTO coach VALUES (default, "John Smith", 1), (default, "Jane Doe", 1), (default, "Zinedine Zidane", 2);

How do I know who are the coaches of the team foo ? Using a JOIN on the foreign key :

SELECT t.name AS "Team name", c.name AS "Coach name"

FROM team t

INNER JOIN coach c

ON c.team_id = t.id

WHERE t.name = "foo";

Result

| Team name | Coach name |

+-----------+------------+

| foo       | John Smith |

| foo       | Jane Doe   |

How do I know what is the team coached by Zidane? Using the same query, only the WHERE clause changes :

SELECT t.name AS "Team name", c.name AS "Coach name"

FROM team t

INNER JOIN coach c

ON c.team_id = t.id

WHERE c.name = "Zinedine Zidane";

Result

| Team name | Coach name      |

+-----------+-----------------+

| bar       | Zinedine Zidane |

You can try it yourself on DB Fiddle

Case 2 : A coach can belong to n team

A unique coach belongs to one or many teams (this is a 1,n relation)

A unique team can have one or many coaches. (this is a 1,n relation)

We can draw a relational schema of those entities this way :

[coach] -(1, n)-> [coach_team] <-(1, n)- [team]

In this kind of relation, a new table coach_team is needed, where both foreign keys of the coach and the team will be stored. To avoid duplication of the couples coach/team, I prefer declaring the PRIMARY KEY as a composition of both foreign keys.

As exemple, I have 3 teams, foo, bar and team 42. And 3 coaches, John Smith, Jane Doe and Zinedine Zidane. John and Jane are the coaches of the team foo and Zidane is the coach of the team bar and the team 42 has Zidane and Jane as coach.

Coach

 id | name

----+-----------------

 1  | John Smith

 2  | Jane Doe

 3  | Zinedine Zidane

Team

 id | name

----+--------

 1  | foo

 2  | bar

 3  | team 42

coach_team

 coach_id | name_id

----------+---------

 1        | 1

 2        | 1

 3        | 2

 2        | 3

 3        | 3

Table creations

CREATE TABLE team

(

  id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(255) NOT NULL

);



CREATE TABLE coach

(

  id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(255) NOT NULL

);



CREATE TABLE coach_team

(

  coach_id INT(6) NOT NULL,

  team_id INT(6) NOT NULL,

  CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coach(id),

  CONSTRAINT FOREIGN KEY(team_id) REFERENCES team(id),

  PRIMARY KEY (coach_id, team_id)

);



INSERT INTO team VALUES (default, "foo"), (default, "bar"), (default, "Team 42");



INSERT INTO coach VALUES (default, "John Smith"), (default, "Jane Doe"), (default, "Zinedine Zidane");



INSERT INTO coach_team VALUES (1, 1), (2, 1), (3, 2), (2, 3), (3, 3);

How do I get the coaches of the team foo ? I query the team, JOIN on the reference table, coach_team, then another JOIN on the table coach.

SELECT t.name AS "Team name", c.name AS "Coach name"

FROM team t

INNER JOIN coach_team ct

ON ct.team_id = t.id

INNER JOIN coach c

ON ct.coach_id = c.id

WHERE t.name="foo";

Result

| Team name | Coach name |

+-----------+------------+

| foo       | John Smith |

| foo       | Jane Doe   |

How do I know what is the team coached by Zidane? Using the same query, only the WHERE clause changes :

SELECT t.name AS "Team name", c.name AS "Coach name"

FROM team t

INNER JOIN coach_team ct

ON ct.team_id = t.id

INNER JOIN coach c

ON ct.coach_id = c.id

WHERE c.name="Zinedine Zidane";

Result

| Team name | Coach name      |

+-----------+-----------------+

| bar       | Zinedine Zidane |

| Team 42   | Zinedine Zidane |

You can try it yourself on DB Fiddle

You could use a foreign key between the two tables to connect the information. In example, add a column named something like team_id in the 'coaches' table. This column should have the id of the team from the 'team' table.

Then, when you register a new coach in a form, you can use the id as the value from the dropdown.