Let’s discuss how to perform CROSS 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
CROSS JOIN
CROSS JOIN is performed on two or more tables. It will return the rows by joining each row from FIRST_TABLE with all rows from SECOND_TABLE. For example if there are 4 rows in first table and 6 rows in second table, then the total number of rows returned is 24 I.E (4*6 = 24 )
SQL CROSS JOIN - Syntax
SELECT columns
FROM FIRST_TABLE
CROSS JOIN SECOND_TABLE
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 CROSS JOIN on two tables - hospital1 and 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 CROSS Join on hospital1 and hospital2.
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
CROSS JOIN hospital2;";
$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 total 24 rows were returned. As there are 4 rows in hospital1 and 6 rows in hospital2.
Example 2:
Now we will perform CROSS JOIN on hospital1 and hospital2 based on ID Column.
<?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 CROSS Join on hospital1 and hospital2 based on ID columns.
$my_query = "SELECT hospital1.ID,hospital1.name,hospital2.city,hospital2.type
FROM hospital1
CROSS 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 The functionality seens to be an INNER JOIN. Only Macthed rows from both the tables were returned.
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with CROSS JOIN in XAMPP Server by running PHP Script. It can be possible to implement the INNER JOIN Functionality by specifying the Condition inside CROSS 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
