How to query information from two tables through PHP

PHP is a common server-side programming language often used in web development. For the task of interacting with the database, PHP's MySQLi extension provides powerful functions that can easily query, add, modify, and delete database data. This article will introduce how to query the information of two tables through PHP.

In a MySQL database, sometimes data needs to be retrieved from two or more tables. For example, in a simple order management system, you may need to retrieve information from the orders table and customer table to display order and customer details. At this point, you need to join the two tables for query.

Suppose we have two tables, one that stores customer information and one that stores order information. The customer table includes customer ID, customer name, address, city and zip code fields. The order table includes order ID, customer ID, order date, and total fields. Our goal is to retrieve order and customer information from these two tables.

1. Use INNER JOIN to join two tables

We can use the INNER JOIN keyword to join the two tables so that both tables can be retrieved at the same time when retrieving data. table data. The INNER JOIN keyword returns rows that match the same value in the two tables, which can be achieved through the following code:

SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This query statement will return all rows with the same customer ID in the customer table and order table, and the result set contains customers Four fields: ID, customer name, order ID and order date.

2. Use LEFT JOIN to join two tables

The LEFT JOIN keyword will return all the rows in the left table (i.e. the first table), as well as the rows on the right Matching rows in the table (that is, the second table) will be filled with NULL if there is no match. For example, if we also want to display customers who have not placed an order, we can achieve this through the following code:

SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This query statement will return all rows in the customer table, as well as rows with the same customer ID in the order table. If a customer has not placed an order, the query results will display NULL values.

3. Use RIGHT JOIN to join two tables

The RIGHT JOIN keyword is the opposite of LEFT JOIN. It will return all the rows in the right table, as well as the rows in the left table Matching lines. For example, if we want to display all customers who have placed orders, we can achieve this through the following code:

SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

This query statement will return all rows in the orders table, as well as rows with the same customer ID in the customer table. If a customer has not placed an order, the query results will display NULL values.

Summary

To query the information of two tables through PHP, you can use the INNER JOIN, LEFT JOIN and RIGHT JOIN keywords to join the two tables. By joining two tables, we can easily retrieve data from multiple tables and merge them into a single result set. When writing query statements, special attention should be paid to the association conditions between tables to ensure that the query results are correct.

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