Let’s discuss how to perform LEFT JOIN on MySQL databases through PHP in the XAMPP Server.
Consider the tables - hospital1, hospital2 present in the database - hospital snapshot with some records.
1. hospital1: 4 rows and 2 columns
2. hospital2: 6 rows and 3 columns
LEFT JOIN
LEFT JOIN is performed on two or more tables. It will return all the rows from the FIRST_TABLE and only matched rows from the SECOND_TABLE with respect to the rows in the FIRST_TABLE.
SQL LEFT JOIN - Syntax
SELECT columns
FROM FIRST_TABLE
LEFT JOIN SECOND_TABLE
ON FIRST_TABLE.COLUMN = SECOND_TABLE.COLUMN;
FIRST_TABLE is the table1 and SECOND_TABLE is the table2.
COLUMN may or may not have similar values in two tables. Based on this column, Tables are joined.
Example 1-
Let's perform LEFT JOIN on two tables - hospital1 and hospital2 based on ID Column and display ID, name from hospital1 and city and type from hospital2.
<?php
// Specify the server
$server = "localhost";
// Specify the user
$user = "root";
// Specify the password
$pwd = "";
// Specify the database
$data = "hospital";
// Let's create connection by using the above details
$connecting_data = new mysqli($server, $user, $pwd, $data);
// Perform Left Join on hospital1 and hospital2 based on ID Column
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
LEFT JOIN hospital2
ON hospital1.ID = hospital2.ID;";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "ID: " . $record_data["ID"]. " , Name: " . $record_data["name"]. " , City: " . $record_data["city"]. " , Type: " . $record_data["type"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
So we can see that only rows with ID 1,2,3 and 3 are present in hospital1 and also present in hospital2.
It can be possible to use WHERE clause along with LEFT JOIN.
Example 2:
Now we will perform LEFT JOIN on hospital1 and hospital2 where name is 'General Hospital'.
<?php
// Specify the server
$server = "localhost";
// Specify the user
$user = "root";
// Specify the password
$pwd = "";
// Specify the database
$data = "hospital";
// Let's create connection by using the above details
$connecting_data = new mysqli($server, $user, $pwd, $data);
// Perform Left Join on hospital1 and hospital2 based on ID Column WHERE name='General Hospital'
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
LEFT JOIN hospital2
ON hospital1.ID = hospital2.ID
WHERE hospital1.name='General Hospital' ;";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "ID: " . $record_data["ID"]. " , Name: " . $record_data["name"]. " , City: " . $record_data["city"]. " , Type: " . $record_data["type"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
So the records with name - 'General Hospital' were returned.
Example 3:
Now we will perform LEFT JOIN on hospital1 and hospital2 where ID > 2.
<?php
// Specify the server
$server = "localhost";
// Specify the user
$user = "root";
// Specify the password
$pwd = "";
// Specify the database
$data = "hospital";
// Let's create connection by using the above details
$connecting_data = new mysqli($server, $user, $pwd, $data);
// Perform Left Join on hospital1 and hospital2 based on ID Column WHERE ID > 2
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
LEFT JOIN hospital2
ON hospital1.ID = hospital2.ID
WHERE hospital1.ID > 2 ;";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "ID: " . $record_data["ID"]. " , Name: " . $record_data["name"]. " , City: " . $record_data["city"]. " , Type: " . $record_data["type"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
We can see that rows with ID greater than 3 were returned.
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with LEFT JOIN in XAMPP Server by running PHP Script. It can be possible to use WHERE Clause with LEFT JOIN.
Would you like to see your article here on tutorialsinhand.
Join
Write4Us program by tutorialsinhand.com
About the Author
Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University.
Published 1400+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning
Page Views :
Published Date :
Jun 14,2024
