English
English
fetch_column is a new method added to the mysqli_result class in PHP 8.1. It is used to directly obtain the values of a single column in the result set and avoid additional array access. It has a simple syntax and is similar to fetchColumn in PDO. Examples of use are as follows:
<code> $mysqli = new mysqli("gitbox.net", "user", "pass", "database"); $result = $mysqli->query("SELECT name FROM users"); while ($name = $result->fetch_column()) {
echo $name . "\n";
}
</code>
In this example, each loop fetches the first column (the default is the 0th column) data of the current row through fetch_column .
In theory, fetch_column will trigger a row of data to be read from the underlying C code and return the value of the specified column. The overhead of a single call is very small, but if you call a lot of data repeatedly in a loop, the accumulated overhead will be significant.
The main influencing factors include:
Data size : The more data, the more calls, the greater the overhead.
Network latency and database performance : When data is frequently pulled in network request environment, the delay will be superimposed.
Client processing efficiency : PHP's loops and method calls themselves also have overhead.
To give an extreme example, if you want to process tens of thousands of records, each calling fetch_column separately, the overall response time will increase significantly.
Get all data at once
If the data volume allows, it is recommended to use fetch_all or fetch_all (MYSQLI_NUM) at one time and then loop through PHP. This reduces the overhead of multiple method calls and network interactions.
Example:
<code> $result = $mysqli->query("SELECT name FROM users"); $allNames = $result->fetch_all(MYSQLI_NUM); foreach ($allNames as $row) {
echo $row[0] . "\n";
}
</code>
Take only the required columns
Try to select only the required fields in SQL queries to reduce the amount of data transmission and processing.
Avoid repeated queries in loops
If you need to use the same data multiple times, consider cache the results first to avoid executing SQL multiple times.
Use preprocessing statements
Preprocessing statements are safer and perform better for a large number of complex queries.
Frequent use of mysqli_result::fetch_column in loops is not extremely slow, but when the data volume is large, the overhead brought by cumulative calls cannot be ignored. By reducing the number of method calls, obtaining data at one time and designing SQL reasonably, performance and response speed can be significantly improved.
I hope this article can help you better understand and optimize database data extraction in PHP.
