When you begin to separate data into distinct tables, you will gain advantages as well as disadvantages. With this data separated, you may have instances where you need relatable data from two or more tables. Your first thought may be to query the database multiple time, then do some looping conditional checks in PHP to string your data together. This would certainly work; however, it is not the most efficient way. For starters, the code to string this data together is messy, and could be tricky to get perfect. Additionally, you’re making multiple calls to the database, with is putting more strain on your server’s resources.

There is an easier and more efficient method of accomplishing this. We can use MySQL’s table join functionality to get MySQL to do all our heavy lifting. Given a reference point, MySQL can join multiple tables together and return the final result to us, all in one database query. A reference point being two fields which will share matching numbers in both of the tables. For example, if you were storing data for school students, you may have a student information table and a student marks table. To join these two tables, you will need a field in the student information table that will match one of more rows in the marks table via a field. A student ID would be a good choice, as it would relatable to other tables.

Now that we are familiar with what is required to create a table join, we can learn the syntax for it. Basically, we select from the first table, tell MySQL to join with another table and then specify the reference point.

[sql]SELECT * FROM table1 JOIN table2 ON table1.id = table2.id[/sql]

You’ll then receive a result with all the fields of table1 and table2 (the reference point will only appear once). You handle these results as normal MySQL queries.

In our next article, we’ll be covering joining more than two tables together, as well as different types of joins.