Sometimes you might want data from more than one table to combine and show on a single page. You need two tables to do this so follow these steps to get another one to go with the employees one:
- make sure you have the correct database selected in PHPMyAdmin (the one with the employees table in)
- click the Import tab
- navigate to where you save this file and choose it
- press Go
You should now have a table called appointments with a few records in it. The new table can be joined to the employees table because the person who has an appointment is recorded in the ref field.
Create a page called sqljoin.php which displays the data from appointments in a list.
Joining the data
The page you have created is a bit unfriendly because it shows the employee ref rather than the name of the employee. To fix that change your query to this:
SELECT family, personal, dateTime, room FROM appointment JOIN employees ON employees.ref=appointment.employeeID;
You will need to change at least three other lines of code as well as you have different data coming in. When you are done you should have something like this:
Look at the new query. The important bit is from JOIN onwards. JOIN says to also get data from the employees table. For every appointment also get the name of the employee from the employees table. It knows which employee name to get because of the ON bit (this specifies what data to make the join based on). For each appointment there is a employeeID for the person involved. The query will get the matching employee record (where the ref field in employees matches the appointmentID in appointment).
Note that the table name is used with the field name in the last bit of the query. This tells the server which field you mean if there are two with the same name (one in each table). To be safe you should do the same with all other field names so that the query looks like this:
SELECT employees.family, employees.personal, appointment.dateTime, appointment.room FROM appointment JOIN employees ON employees.ref=appointment.employeeID;
Note that here I have also split the query over multiple lines to make it easier to see. The server does not care if you put extra line breaks in so do what you want. The end of a query is marked by the ; so extra spaces, tabs and line breaks are for your benefit only.
You can join more than two tables in the same way. That can get confusing though so sometimes it is easier to just run more than one query to get the data from the various tables. As you get more confident you will use JOIN more.