Let’s discuss how to apply the INTERSECT function with the SQL SELECT Query through PHP to connect with the MySQL database in the XAMPP Server.
Consider the tables - hospital1, hospital2 present in the database - hospital snapshot with some records.
1. hospital1: 4 rows
2. hospital2: 6 rows
INTERSECT
INTERSECT function is used to return only the common rows from two tables. Rows from second table are appended to rows in first table. make sure that while performing the intersection , same columns with same data types have to be specified.
SQL INTERSECT - Syntax
SELECT columns… from FIRST_TABLE INTERSECT SELECT columns… from SECOND_TABLE
FIRST_TABLE is the table1 and SECOND_TABLE is the table2.
Example 1-
Let's combine two tables using INTERSECT using PHP.
<?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);
// Mention the SQL Query to perform INTERSECTION on two tables - hospital,hospital2.
$my_query = "SELECT name,city,type FROM hospital1 INTERSECT SELECT name,city,type FROM hospital2";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "First Hospital name: " . $record_data["name"]. ", City: " . $record_data["city"]. ", Type: " . $record_data["type"]. "<br>";
echo "Second Hospital name: " . $record_data["name"]. ", City: " . $record_data["city"]. ", Type: " . $record_data["type"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
We can see that only commom rows from both the tables were returned.
Example 2-
Let's combine two tables using INTERSECT with WHERE clause using PHP.
Here we will get rows from two tables with Type='pharma'.
<?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);
// Mention the SQL Query to perform Intersection on two tables - hospital,hospital2 with Type-pharma.
$my_query = "SELECT name,city,type FROM hospital1 WHERE type='pharma' INTERSECT SELECT name,city,type FROM hospital2 WHERE type='pharma'";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "First Hospital name: " . $record_data["name"]. ", City: " . $record_data["city"]. ", Type: " . $record_data["type"]. "<br>";
echo "Second Hospital name: " . $record_data["name"]. ", City: " . $record_data["city"]. ", Type: " . $record_data["type"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
So the common rows with type-'pharma' were returned from two tables.
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with INTERSECT in XAMPP Server by running PHP Script. We discussed two different examples .It can be possible to use WHERE Clause with INTERSECT.
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
