Use MySQL UNION in PHP

Introduction Of UNION

A UNION combines the result set of two or more select statements into one result set. The UNION keyword is used to connect two or more SELECT statements but the result of each select statement must have the same number of columns, and the data type of the corresponding columns in each table must be compatible.

Syntax

SELECT statement 1 UNION [ALL] SELECT statement 2 UNION [ALL] SELECT statement 2.........................

For example: SELECT [Column] or [columns] OR [*] from table1 [UNION] OR [UNION ALL] select [Column] or [columns] OR [*] from table2;

Such as in:

• SELECT name from table1 UNION SELECT name from table2;
   
• SELECT name , age from table1 UNION SELECT name , age from table2;
   
• SELECT * from table1 UNION SELECT * from table2;

Suppose you have two tables named "oldempdtl" and "newempdtl". You want to create all employees details in a single result set by merging all columns from the two tables. The following figure shows how to merge two tables into a single result set.





Note: In the figure above we have two tables with the names oldempdtl and newempdtl. The oldempdtl table consists of employee data like id name and his/her age and new one also. Now we want to see all the employees details (in other words PAST + NEW) into a single result set. So the last query tells you how to use a union to doing that.

By default UNION removes duplicate rows from the result set. If that is not what you want, you can include the ALL keyword.



Example of UNION with PHP

<?php
$con=mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("mysql", $con);

print "<h2>MySQL: Union through PHP</h2>";

$result = mysql_query("select * from oldempdtl UNION select * from newempdtl");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Age</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

   echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['name'] . "</td>";

  echo "<td>" . $row['age'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

  mysql_close($con);

  ?>

Note: The example above tells you how to use a union in PHP to combine data from two tables. In this case the oldempdtl table contains the id, name and age of old employees and newempdtl also consists of the id, name and age with additional new employees. This union operation combines all rows of both tables.

Output