MySQL Joins using PHP

The MySQL JOIN clause is used to combine rows of two or more tables based on common column between them. There are four types of JOINs in MySQL:

• INNER JOIN - It is sometimes called simple JOIN. It returns records based on matching rows in both tables.
• LEFT JOIN - It is sometimes called LEFT OUTER JOIN. It returns records which contains all rows from left table and matching rows from right tables.
• RIGHT JOIN - It is sometimes called RIGHT OUTER JOIN. It returns records which contains all rows from right table and matching rows from left tables.
• CROSS JOIN - Returns records which contains all rows from both tables. It is sometimes called CARTESIAN JOIN because in the absence of a WHERE condition it behaves like a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number of rows of the two tables.

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

In the query below, the INNER JOIN clause is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match found in both tables.

SELECT Employee.Name, Employee.Age, Contact_Info.Address 
FROM Employee
INNER JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID;

This will produce the result as shown below:

Please note that, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

The num_rows() function can be used to check if there are more than zero rows returned. Then, the fetch_assoc() function can be used to fetch the result set as an associative array. Later on the free_result() function can be used to free the memory associated with the result.

MySQL JOIN - Object-oriented style

The example below demonstrates how to perform INNER JOIN discussed above using object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//getting query result from the database
$sql = 'SELECT Employee.Name, Employee.Age, Contact_Info.Address 
FROM Employee
INNER JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID;';

$result = $mysqli->query($sql);

//fetching associative array
while ($row = $result->fetch_assoc()) {
  printf("Name: %s, Age: %d, Address: %s\n", 
          $row["Name"], $row["Age"], $row["Address"]);
}

//free result set
$result->free_result();

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

Name: Marry, Age: 24, Address: Brooklyn, New York, USA
Name: Jo, Age: 27, Address: Grenelle, Paris, France
Name: Kim, Age: 30, Address: Geuzenveld, Amsterdam, Netherlands
Name: Huang, Age: 28, Address: Yizhuangzhen, Beijing, China 

MySQL JOIN - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//getting query result from the database
$sql = 'SELECT Employee.Name, Employee.Age, Contact_Info.Address 
FROM Employee
INNER JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID;';
$result = mysqli_query($mysqli, $sql);

//fetching associative array
while ($row = mysqli_fetch_assoc($result)) {
  printf("Name: %s, Age: %d, Address: %s\n", 
          $row["Name"], $row["Age"], $row["Address"]);
}

//free result set
mysqli_free_result($result);

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

Name: Marry, Age: 24, Address: Brooklyn, New York, USA
Name: Jo, Age: 27, Address: Grenelle, Paris, France
Name: Kim, Age: 30, Address: Geuzenveld, Amsterdam, Netherlands
Name: Huang, Age: 28, Address: Yizhuangzhen, Beijing, China 

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.