Retrieving Data From Related Tables in PHP

Let's make a query that will fetch all users along with their cities. For this, we will need the LEFT JOIN command:

Its syntax looks like this:

SELECT fields FROM table_name
	LEFT JOIN related_table_name ON join_condition 
WHERE selection_condition

Let's break down the individual parts of the syntax of this command.

Fields

Since the selection is from multiple tables, selecting all fields via * will not work. The following query will select fields only from the main table, but not from the related one:

SELECT *

In order to select data from all tables, you need to specify the table name before *:

SELECT users.*, cities.*

Alternatively, you can list the fields we need, specifying the table name before them:

SELECT users.name, cities.name

These two methods have a problem. The thing is, if the fields in the tables have the same names, then in the PHP array there will be a name conflict and only one field will win, and the second one will be missing.

To solve the problem, conflicting names need to be renamed using the as command:

SELECT users.name, cities.name as city_name

Join Condition

After the ON command we must specify the fields from the two tables that are used for the relationship. In our case, this will be the field id from the cities table and the field city_id from the users table:

ON cities.id=users.city_id

Query

As a result, the query that will fetch users along with their cities will look like this:

SELECT 
	users.name, cities.name as city_name
FROM 
	users
LEFT JOIN cities ON cities.id=users.city_id

Practical Tasks

Suppose you have a table with products and a table with their categories. Write a query that fetches product names along with their categories.