English
English
In PHP, the fetch_all() function is typically used to fetch all the results of a database query. It is part of the mysqli extension library. When using this function, you can set the type of the result set to handle the query data as needed. The two most common result set modes are MYSQLI_ASSOC and MYSQLI_NUM, and the difference between them lies in how they convert the rows from the database into PHP arrays.
When you set the result set mode of the fetch_all() function to MYSQLI_ASSOC, the result will return an associative array where the array keys are the field names. For example, assume you have a table with user data and the query results look like this:
SELECT id, username, email FROM users;
Using the MYSQLI_ASSOC mode, the returned data will look like this:
Array(
[0] => Array(
[id] => 1,
[username] => john_doe,
[email] => john@example.com
),
[1] => Array(
[id] => 2,
[username] => jane_smith,
[email] => jane@example.com
)
)
In this mode, the data is organized by the field names of the columns, and you can access each column's data using the field name. For example:
echo $result[0]['username']; // Outputs john_doe
If you want the result set to be a numerically indexed array, you can choose to use the MYSQLI_NUM mode. This mode will convert each row of data into an array indexed by numbers, with the index values corresponding to the position of the columns in the database. For example, using the MYSQLI_NUM mode to query the user table would give the following result:
Array(
[0] => Array(
[0] => 1,
[1] => john_doe,
[2] => john@example.com
),
[1] => Array(
[0] => 2,
[1] => jane_smith,
[2] => jane@example.com
)
)
In this mode, the data is ordered by the position of the columns, with indexes starting from 0. For example, you can access the data using the numeric index of the array:
echo $result[0][1]; // Outputs john_doe
The second parameter of the fetch_all() function is used to specify the result set mode, which can be either MYSQLI_ASSOC or MYSQLI_NUM. If you want to use an associative array (with field names as keys), set the second parameter to MYSQLI_ASSOC. If you want to use a numerically indexed array (with column positions as indexes), set the second parameter to MYSQLI_NUM.
<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
<p>if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>$query = "SELECT id, username, email FROM users";<br>
$result = $mysqli->query($query);</p>
<p>// Set to MYSQLI_ASSOC<br>
$assoc_result = $result->fetch_all(MYSQLI_ASSOC);<br>
print_r($assoc_result);</p>
<p>// Set to MYSQLI_NUM<br>
$num_result = $result->fetch_all(MYSQLI_NUM);<br>
print_r($num_result);</p>
<p>$mysqli->close();<br>
?><br>
Whether you choose MYSQLI_ASSOC or MYSQLI_NUM mode depends on how you want to access the data:
If you prefer referencing data by field names and want the result to be more readable, use MYSQLI_ASSOC.
If you need a more memory-efficient way or care more about the order of the data rather than the field names, MYSQLI_NUM might be a better choice.
In real-world development, you may encounter situations where you need to output data to a webpage and include URLs. Suppose you need to generate a link by dynamically embedding a database field in the URL. For example, if your user table contains the URL to each user's profile, you could do something like this:
$domain = "http://m66.net/profile/";
$user_id = $result[0]['id'];
$user_url = $domain . $user_id;
<p>echo "<a href='$user_url'>Visit Profile</a>";<br>
In this way, you can generate a link to the user's profile page.
fetch_all() is a very useful tool for handling query result sets. By choosing the correct MYSQLI_ASSOC or MYSQLI_NUM mode, you can access and process data more conveniently. Selecting the appropriate result set mode will make your code more concise, readable, and maintainable.
