MySQL - Join
Thus far we have only been getting data from one table at a time. This is fine for simple tasks, but in most real world MySQL usage you will often need to get data from multiple tables in a single query.
The act of joining in MySQL refers to smashing two or more tables into a single table. This means everything you have learned so far can be applied after you've created this new, joined table.
MySQL Join Table
We like to show examples and code before we explain anything in detail, so here is how you would combine two tables into one using MySQL. The two tables we will be using relate to a families eating habits.
person Table
| Name | Age |
|---|
| Putu | 21 |
| Made | 18 |
| Nyoman | 20 |
| Ketut | 17 |
food Table
| Meal | Name |
|---|
| Ayam Betutu | Putu |
| Tipat Cantok | Made |
| Nasi Jinggo | Nyoman |
| Lawar | Ketut |
The important thing to note here is that the column
Name contains information that can tie these two tables together. In the "person" table, the Name column contains all of the person and their respective ages. In the "food" table the
Name column contains the person who enjoys that dish.
It's only through a shared column relationship such as this that tables can be joined together, so remember this when creating tables you wish to have interact with each other.
Let's imagine that we wanted to SELECT all the dishes that were liked by a persons. If you remember from the previous lesson, this is a situation when we need to use the WHERE clause. We want to SELECT all the dishes WHERE a persons likes it.
We will be performing a generic join of these two tables using the Name column from each table as the connector.
Note: This example assumes you have created the MySQL tables "food" and "person". If you do not have either of them created, you can either create them using our MySQL Create Table lesson or do it manually yourself.
PHP and MySQL Code
<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT person.Name, food.Meal ".
"FROM person, food ".
"WHERE person.Name = food.Name";
$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Name']. " - ". $row['Meal'];
echo "
";
}
?>
The statement "WHERE person.Name = food.Name" will restrict the results to the rows where the Name exists in both the "person" and "food" tables.
Results
Putu - Ayam Betutu
Made - Tipat Cantok
Nyoman - Nasi Jinggo
Ketut - Lawar
Those are the results of our PHP script. Let's analyze the tables to make sure we agree with these results.
