How to query two tables and merge them using PHP

PHP is a programming language widely used in web development. When developing web applications, you generally need to connect to a database to obtain and store data, and querying data from multiple tables is a very common requirement. This article will explain how to query two tables and merge them using PHP.

First, we need to create two tables and insert some data into the tables. Suppose one table is named "orders" and the other is named "customers". The orders table includes order data, including order number, order date, order customer, etc. The customers table is customer data, including customer name, customer address, customer phone number, etc. To show how to query these two tables, we create the following table structure:

orders table

##12021-01-01122021-01-022##34customers table

order_id	order_date	customer_id
2021-01-03	1
2021-01-04	3

customer_id1234Next, we will use PHP to query these two tables and merge them into one table so that we can operate on the data. The following is the sample code:

customer_name	customer_address	customer_phone
Alice	New York	123-456-7890
Bob	Los Angeles	234-567-8901
Charlie	Chicago	345-678-9012
David	Houston	456-789-0123

// Connect to database
$host = "localhost";
$user = "username";
$password = "password";
$database = "database";
$connect = mysqli_connect($host, $user, $password, $database);

// Query orders table
$query = "SELECT * FROM orders";
$result = mysqli_query($connect, $query);

// Query customers table
$query = "SELECT * FROM customers";
$result2 = mysqli_query($connect, $query);

// Merge two tables
$merged_array = array();
while ($row = mysqli_fetch_assoc($result)) {
    $customer_id = $row['customer_id'];
    $customer_query = "SELECT * FROM customers WHERE customer_id = $customer_id";
    $customer_result = mysqli_query($connect, $customer_query);
    $customer_row = mysqli_fetch_assoc($customer_result);
    $merged_array[] = array_merge($row, $customer_row);
}

// Show merged table
echo "<table>";
echo "<tr><th>Order ID</th><th>Order Date</th><th>Customer Name</th><th>Customer Address</th><th>Customer Phone</th></tr>";
foreach ($merged_array as $row) {
    echo "<tr>";
    echo "<td>" . $row['order_id'] . "</td>";
    echo "<td>" . $row['order_date'] . "</td>";
    echo "<td>" . $row['customer_name'] . "</td>";
    echo "<td>" . $row['customer_address'] . "</td>";
    echo "<td>" . $row['customer_phone'] . "</td>";
    echo "</tr>";
}
echo "</table>";

In the above code, first connect to the database, then query the orders and customers tables, and store the results into the $result and $result2 variables. Next, we merge the two tables using a while loop and the array_merge function. In each loop, we first get the customer_id of a specific row from the orders table and then query the customers table to get the details of that customer. Finally, we merge the two arrays and add them to the $merged_array variable. Finally, we use a foreach loop to display the merged table in an HTML table.

Summary: In web application development, querying data from multiple tables is a common requirement. By using PHP we can easily query two tables and merge them. The code example above shows how to do this by connecting to the database, querying each table, and merging them.

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