PHP MySQL - union operation
Let’s discuss how to apply the UNION, UNION ALL functions 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
UNION
UNION function is used to return the rows from two tables. Rows from second table are appended to rows in first table. make sure that while performing union same columns with same data types were selected.
SQL UNION - Syntax
SELECT columns… from FIRST_TABLE UNION SELECT columns… from SECOND_TABLE
FIRST_TABLE is the table1 and SECOND_TABLE is the table2.
If return the records only once. (Unique records were returned).
Example 1-
Let's combine two tables using UNION 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 UNION on two tables - hospital,hospital2.
$my_query = "SELECT name,city,type FROM hospital1 UNION 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 all the records are returned uniquely, duplicated records were not returned.
Example 2-
Let's combine two tables using UNION 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 UNION on two tables - hospital,hospital2 with Type-pharma.
$my_query = "SELECT name,city,type FROM hospital1 WHERE type='pharma' UNION 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 rows with type-'pharma' were returned from two tables.
UNION ALL
UNION ALL function is used to return the rows from two tables. Rows from second table are appended to rows in first table. make sure that while performing UNION ALL same columns with same data types were selected. The difference between UNION and UNION ALL is UNION will return only unique rows but UNION ALL will return all rows.
SQL UNION ALL - Syntax
SELECT columns… from FIRST_TABLE UNION ALL SELECT columns… from SECOND_TABLE
FIRST_TABLE is the table1 and SECOND_TABLE is the table2.
If return the records only once. (Unique records were returned).
Example 1-
Let's combine two tables using UNION ALL 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 UNION on two tables - hospital,hospital2.
$my_query = "SELECT name,city,type FROM hospital1 UNION ALL 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"]. " --> Second Hospital name: " . $record_data["name"]. ", City: " . $record_data["city"]. ", Type: " . $record_data["type"]."<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
Example 2-
Let's combine two tables using UNION ALL 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 UNION on two tables - hospital,hospital2 where type is pharma.
$my_query = "SELECT name,city,type FROM hospital1 WHERE type='pharma' UNION ALL 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"]. " --> Second Hospital name: " . $record_data["name"]. ", City: " . $record_data["city"]. ", Type: " . $record_data["type"]."<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with UNION, UNION ALL in XAMPP Server by running PHP Script. We discussed two different examples for both.It can be possible to use WHERE Clause with UNION and UNION ALL.
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
