Let’s discuss how to perform RIGHT 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
RIGHT JOIN
RIGHT JOIN is performed on two or more tables. It will return all the rows from the SECOND_TABLE and only matched rows from the FIRST_TABLE with respect to the rows in the SECOND_TABLE.
SQL RIGHT JOIN - Syntax
SELECT columns
FROM FIRST_TABLE
RIGHT 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 RIGHT 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 Right Join on hospital1 and hospital2 based on ID Column
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
RIGHT 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,4,2,4, and 3 are present in hospital2 and 1,2,3 and 3 present in hospital1.
So only 2 rows in hospital1 are not present. so ID will be empty in these two records.
It can be possible to use WHERE clause along with RIGHT JOIN.
Example 2:
Now we will perform RIGHT JOIN on hospital1 and hospital2 where type is 'clinical'.
<?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 Right Join on hospital1 and hospital2 based on hospital2.ID Column WHERE ID=4
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
RIGHT JOIN hospital2
ON hospital1.ID = hospital2.ID
WHERE hospital2.ID=4 ;";
$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 ID = 4 from hospital2 table (Right Table) is not exists in hospital1 (Left table). Hence ID is empty in the resulted rows.
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with RIGHT JOIN in XAMPP Server by running PHP Script. It can be possible to use WHERE Clause with RIGHT 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
