PHP two tables merge query

In PHP development, we often encounter situations where we need to perform joint queries in multiple data tables. At this time, we can merge the two tables by using the union keyword in the SQL statement. This article will introduce how to use the union keyword in PHP to merge two tables.

1. Basic syntax

We can use the following syntax to merge the two tables:

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

Among them, column1, column2, ... is the name of the column that needs to be queried, table1 and table2 are the names of the data tables that need to be queried. The Union keyword is used to combine the results of two SELECT statements into a query result set. When performing a merge query, you need to ensure that the number and type of columns selected in the two SELECT statements must be consistent, otherwise an error will occur.

2. Use the UNION ALL keyword

In addition to using the UNION keyword for data table merge queries, we can also use the UNION ALL keyword for merge queries. Unlike the UNION keyword, the UNION ALL keyword will merge all records in the two query result sets, including duplicate records. This needs special attention because in real situations we may need to keep duplicate records.

The following is the basic syntax for merging data tables using the UNION ALL keyword:

SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2;

3. Sample code

In order to better understand how to The union keyword is used in PHP to perform data table merge queries. We can try to use code to demonstrate. Suppose we have two data tables user and employee. Their data structures are as follows:

user表：
id    name     age    city
1     Tom      22     北京
2     Jerry    23     上海
3     Peter    24     广州

employee表：
id    name     age    salary
1     Mary     25     2000
2     Lucy     26     2500
3     Jack     27     3000

We now need to query all records in the user table and employee table. We can use the following code to achieve this operation:

<?php
  try {
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', 'password');
    $sql = "SELECT id, name, age, city FROM user
            UNION ALL
            SELECT id, name, age, salary as city FROM employee";
    $stmt = $pdo->query($sql);
    echo "<table>";
    echo "<tr><td>ID</td><td>Name</td><td>Age</td><td>City</td></tr>";
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      echo "<tr>";
      echo "<td>".$row['id']."</td>";
      echo "<td>".$row['name']."</td>";
      echo "<td>".$row['age']."</td>";
      echo "<td>".$row['city']."</td>";
      echo "</tr>";
    }
    echo "</table>";
  } catch (PDOException $e) {
    echo $e->getMessage();
  }
?>

In the code, we first create a PDO connection object, and then use the UNION ALL keyword in the SQL statement to merge the user table and employee table for query. Finally, we obtain the query results row by row through the fetch function and output the results to an HTML table.

4. Summary

By using the union keyword, we can easily perform joint queries on multiple data tables to get the desired data results. When performing merge queries, you need to note that the number and type of columns selected in the two SELECT statements must be consistent, otherwise an error will occur. In addition, we can also use the UNION ALL keyword to merge data tables to retain all duplicate records. In actual development, we should choose the most suitable type according to needs to perform data table merge query.

The above is the detailed content of PHP two tables merge query. For more information, please follow other related articles on the PHP Chinese website!