Example to explain how to use php to merge two table queries

PHP is a very popular programming language used for developing web applications. In web development, the database is a very important component. A common application scenario is to extract information from multiple tables and combine them to display to the user. In PHP, we can use multiple methods to achieve this goal. This article will discuss how to merge two table queries using PHP.

1. Use the JOIN statement to merge tables

The JOIN statement is a method used to merge multiple tables in SQL. In PHP, we can extract information from multiple tables by using the JOIN statement. There are several types of JOIN statements, the most common ones are INNER JOIN and LEFT JOIN.

INNER JOIN will return the jointly owned records in the two tables, and LEFT JOIN will return all the records in the left table and all the records in the associated right table. When using the JOIN statement, we need to specify the name of each table and the association conditions between them.

The following is an example of using INNER JOIN to merge two tables:

$sql = "SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出每一行数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

In this example, we use INNER JOIN to merge two tables named table1 and table2. We specified an association condition, table1.id=table2.id. This condition instructs MySQL to return only rows that meet this condition.

2. Use the JOIN statement and the comma operator to merge tables

If you want to extract information from multiple tables, there is another way to use the comma operator and the JOIN statement. This method is less clear than INNER JOIN and LEFT JOIN, but may be more convenient in some situations.

Here is an example of using comma operator and JOIN statement to extract information from two tables:

$sql = "SELECT table1.*, table2.email FROM table1,table2 WHERE table1.id = table2.id";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出每一行数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

In this example, we use comma operator to extract information from two tables at the same time Select all columns. We also specify a WHERE clause to indicate that only rows that meet this condition will be returned.

3. Use the UNION statement to merge query results

In addition to the JOIN statement, you can also use the UNION statement to merge the query results. The UNION statement can combine the results of multiple SELECT statements into a single result set. The UNION statement requires that the number and column types of the SELECT statement must be the same.

The following is an example of query results using the UNION statement to merge two tables:

$sql = "(SELECT id, name, email FROM table1) UNION (SELECT id, name, email FROM table2)";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出每一行数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

In this example, we use the UNION statement to merge two tables named table1 and table2 . We selected the id, name and email columns, which come from two tables respectively. With UNION statement we can combine them into one result set and process it in PHP code.

Conclusion

Extracting information from multiple tables is a very common operation when developing web applications. In PHP, we can use JOIN statement, comma operator and UNION statement to achieve this goal. Choosing the right approach can greatly improve the performance and maintainability of your application.

The above is the detailed content of Example to explain how to use php to merge two table queries. For more information, please follow other related articles on the PHP Chinese website!