How to Use fetch_all() to Retrieve Results from Multiple Table Join Queries

M66 2025-06-15

In PHP, handling database queries is a common task in daily development. When we need to fetch data from multiple tables, we usually perform a join query. After executing the query, how to conveniently retrieve the query results becomes crucial. This article will introduce how to use fetch_all() method to retrieve results from multiple table join queries, along with example code.

1. Introduction to Multiple Table Join Queries

Multiple table join queries allow you to associate data from multiple tables and return results based on specific conditions. For example, if there are two tables users and orders, and we want to query all users and their corresponding order information, we can use JOIN.

SELECT users.id, users.name, orders.order_id, orders.amount 
FROM users 
JOIN orders ON users.id = orders.user_id;

2. Using mysqli to Execute Multiple Table Join Queries in PHP

PHP provides various ways to interact with MySQL databases. Here, we will use the mysqli extension and the fetch_all() method to retrieve all results.

1. Establish Database Connection

<?php
$mysqli = new mysqli("m66.net", "username", "password", "database");
<p>if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}<br>
?><br>

2. Execute Multiple Table Join Query

<?php
$sql = "SELECT users.id, users.name, orders.order_id, orders.amount 
        FROM users 
        JOIN orders ON users.id = orders.user_id";
<p>$result = $mysqli->query($sql);</p>
<p>if (!$result) {<br>
die("Query failed: " . $mysqli->error);<br>
}<br>
?><br>

3. Use fetch_all() to Retrieve Results

fetch_all() retrieves all rows of the result set at once and returns a two-dimensional array. By default, the return type is MYSQLI_NUM, which is an indexed array. We can also use MYSQLI_ASSOC to fetch an associative array.

<?php
$data = $result->fetch_all(MYSQLI_ASSOC);
<p>foreach ($data as $row) {<br>
echo "User ID: " . $row['id'] . ", Name: " . $row['name'] . ", Order ID: " . $row['order_id'] . ", Amount: " . $row['amount'] . "<br>";<br>
}<br>
?><br>

4. Close the Connection

<?php
$mysqli->close();
?>

3. Complete Example Code

<?php
$mysqli = new mysqli("m66.net", "username", "password", "database");
<p>if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>$sql = "SELECT users.id, users.name, orders.order_id, orders.amount<br>
FROM users<br>
JOIN orders ON users.id = orders.user_id";</p>
<p>$result = $mysqli->query($sql);</p>
<p>if (!$result) {<br>
die("Query failed: " . $mysqli->error);<br>
}</p>
<p>$data = $result->fetch_all(MYSQLI_ASSOC);</p>
<p>foreach ($data as $row) {<br>
echo "User ID: " . $row['id'] . ", Name: " . $row['name'] . ", Order ID: " . $row['order_id'] . ", Amount: " . $row['amount'] . "<br>";<br>
}</p>
<p>$mysqli->close();<br>
?><br>

4. Conclusion

Multiple table join queries are an effective way to retrieve related data.
Using mysqli's query() to execute queries and fetch_all() to retrieve all results at once simplifies data processing.
By setting the parameter of fetch_all() to MYSQLI_ASSOC, you can easily access data using field names.

We hope this article helps you better understand and use fetch_all() to retrieve results from multiple table join queries.