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

// 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 "";
echo "";
foreach ($merged_array as $row) {
    echo "";
    echo "";
    echo "";
    echo "";
    echo "";
    echo "";
    echo "";
}
echo "
Order ID
Order Date
Customer Name
Customer Address
Customer Phone
" . $row['order_id'] . "
" . $row['order_date'] . "
" . $row['customer_name'] . "
" . $row['customer_address'] . "
" . $row['customer_phone'] . "
";

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!