PHP union query three tables

When developing a website, sometimes it is necessary to combine multiple tables to query and obtain the required data. In PHP, you can use union query statements to achieve this function. This article will introduce how to use PHP to jointly query three tables.

Suppose we have three tables: users, orders and order_items. Among them, the users table records user information, the orders table records order information, and the order_items table records order item information. Now we need to query all orders for each user, and all line items for each order. This can be achieved using the following joint query statement:

$sql = "SELECT u.name, o.order_number, i.product_name, i.price
    FROM users u
    JOIN orders o ON u.id = o.user_id
    JOIN order_items i ON o.id = i.order_id
    WHERE u.id = ?";

This statement first connects the users table, orders table and order_items table through the JOIN keyword, and then filters out the information of the specified user through the WHERE clause. Among them, the JOIN keyword is used to join tables, and the ON keyword is used to specify connection conditions.

In the above query, we need to query all orders of each user and all line items of each order. Therefore, we need to use the JOIN keyword to connect the users table, orders table and order_items table. There are three connection methods:

• INNER JOIN (inner join): Only matching rows from the two tables are returned.
• LEFT JOIN (left join): Returns all rows of the left table and rows of the right table that meet the join conditions. If there are no matching rows in the right table, NULL is returned.
• RIGHT JOIN (right join): Returns all rows of the right table and rows of the left table that meet the join conditions. If there are no matching rows in the left table, NULL is returned.

In this example, we used an INNER JOIN because we only want to return rows that have a match.

Next, we need to specify the connection conditions. Here we use the user ID (users.id) to associate with the order user ID (orders.user_id), and the order ID (orders.id) to associate the order item order ID (order_items.order_id).

Finally, we use the WHERE clause to filter the information of the specified user. Here, we use the placeholder?, and before executing the query, we need to use the bindParam() or bindValue() method to bind the actual parameters to the placeholder.

To summarize, this is a relatively simple PHP statement to jointly query three tables. Union query is a very flexible and powerful query method, which should be used flexibly according to actual needs in actual development.

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