How to join table query in php to get 3-dimensional array

In Web development, joint table query is a common operation. In PHP language, we can implement queries by using joins between multiple tables. For more complex query operations, when the data to be obtained is relatively complex, we can usually use a 3-dimensional array to store the data. This article will introduce how to use PHP to perform joint table queries and how to obtain a 3-dimensional array.

1. What is joint table query?

Joint table query refers to the operation of using multiple tables at the same time in the query. When a table lacks certain field information, or some information needs to be obtained from multiple tables, we need to use a joint table query.

When performing a joint table query, we need to use the JOIN keyword to connect multiple tables together. Connection keywords include LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL OUTER JOIN, etc. Choose according to your needs, usually using INNER JOIN can meet your needs.

2. How to use PHP to perform joint table query?

In PHP, we can use PDO (PHP Data Objects) for database operations. The following is a simple example where we will use the INNER JOIN keyword to perform a joint query on two tables.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO: :ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID";

foreach ($conn->query($sql) as $row) {

print $row['OrderID'] . "\t";
print $row['CustomerName'] . "\t";
print $row['OrderDate'] . "\n";

}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>

In the above code, we first connect to the database. Next, we used the INNER JOIN keyword to perform a joint query on the Orders table and the Customers table, and used a foreach loop to traverse the query results.

As you can see, we used the SELECT statement to specify the fields to be queried, and also used the INNER JOIN keyword to connect the two tables. In the next loop, we use print to output the query results.

3. How to get a 3-dimensional array?

If the data we need to obtain is relatively complex, then after the joint table query is completed, we need to convert the query results into a 3-dimensional array for storage. Below is a sample code that we modified based on this.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO: :ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID";

$result = $conn->query($sql)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

$output = array();
foreach ($result as $key=>$val) {

$output[] = array("OrderID"=>$key, "OrderDetail"=>$val);

}

print_r($output);
} catch(PDOException $e) {
echo " Error: " . $e->getMessage();
}
$conn = null;
?>

In the above code, we used PDO::FETCH_GROUP and PDO::FETCH_ASSOC two constants to get the grouped associative array. Next, we convert the result into a 3-dimensional array through a loop, and finally use the print_r function for array output.

4. Summary

In Web development, joint table query is a very common operation. By using the INNER JOIN keyword, we can easily join multiple tables for querying. When the data is complex, we can use a 3-dimensional array to store the query results for subsequent processing. When using PDO for database operations, you need to pay attention to security and handle exceptions.

The above is the detailed content of How to join table query in php to get 3-dimensional array. For more information, please follow other related articles on the PHP Chinese website!