Comparison and selection of mysqli_result::fetch_column and mysqli_fetch_array

When using PHP for database operations, the mysqli extension provides multiple ways to get query results. Among them, mysqli_result::fetch_column and mysqli_fetch_array are two commonly used but different functions. Understanding their differences and applicable scenarios is very important for writing efficient and easy-to-maintain code.

1. Basic introduction

• mysqli_result::fetch_column
  This is a new method added to PHP 8.1 version, which belongs to the object-oriented style mysqli_result class. It is used to get the value of the specified column from the result set and return the data of a single field.

• mysqli_fetch_array
  This is a procedural-oriented function that returns all field data of the current row. It returns an array, either associative array, numeric index array, or even both, depending on the parameter settings.

2. Detailed differences

3. Use examples

mysqli_result::fetch_column example

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
$result = $mysqli->query("SELECT id, name FROM users");

while ($id = $result->fetch_column(0)) {  // Get the first column id
    echo "userID: $id\n";
}

$mysqli->close();
?>

mysqli_fetch_array example

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
$result = $mysqli->query("SELECT id, name FROM users");

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    echo "userID: " . $row['id'] . ", user名: " . $row['name'] . "\n";
}

$mysqli->close();
?>

4. How to choose a more suitable function?

• If you only care about one column in the query result and are using PHP 8.1 and above, fetch_column is a more concise and efficient choice.
  It directly returns the data of the specified column, avoiding unnecessary array overhead and making the code more concise.

• If you need to access multiple fields in the entire row, or your project is still using PHP 8.0 and below, then using mysqli_fetch_array is more appropriate.
  It supports more return types and provides flexible access to fields.

• Object-oriented vs Process-oriented <br> If you are used to object-oriented programming, it is recommended to use $result->fetch_column() and other object-oriented methods of mysqli_result .
  Otherwise, mysqli_fetch_array() is still a classic and widely used process function.

5. Summary

When selecting a function, you should make decisions based on business needs and PHP version. Understanding the difference between the two can help write more concise and efficient database access code.