Use PHP code to implement related query display of two tables

In many applications, data needs to be retrieved from multiple database tables to obtain the required information. In this case, a join query between two tables can be used as a very powerful tool to query the required data. In this article, we will introduce how to use PHP code to implement related query display of two tables.

First, we need to create a pair of associated database tables. We will simulate the scenario of a simple e-commerce website. The first table is the products table, which contains all products available for sale. The second table is the orders table, which contains details of existing orders. The association between the two tables is the association between the products.id field and the orders.product_id field.

In the products table, we need to add the following fields:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

In the orders table, we need to add the following fields :

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

Now, we have two related tables in our database. Next is the process of using PHP code to query this data and display it on a web page.

First, we need to connect to the database. Use the following code to connect to your database:

// 使用 mysqli 连接到数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

Now that we have successfully connected to the database, here comes the critical part: querying the two tables and combining them to display them. Use the following code to query two tables and display them together:

$sql = "SELECT products.name, products.price, orders.customer_name, orders.quantity, orders.total_price, orders.order_date
        FROM products
        INNER JOIN orders
        ON products.id = orders.product_id";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "产品名称: " . $row["name"] . "<br>";
        echo "价格: " . $row["price"] . "<br>";
        echo "客户姓名: " . $row["customer_name"] . "<br>";
        echo "数量: " . $row["quantity"] . "<br>";
        echo "总价: " . $row["total_price"] . "<br>";
        echo "订单日期: " . $row["order_date"] . "<br><br>";
    }
} else {
    echo "没有数据！";
}

In the above code, we have used the SELECT statement and INNER JOIN to Combine two tables. Using this query, we can retrieve data from the products table and the orders table and join them together based on the association between them. We use the mysqli_fetch_assoc() function to iterate through the data retrieved from the database and output the results to the web page.

Now, we have successfully used PHP code to implement a correlation query between two tables and display the results on the web page.

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