rfc:mysqli_fetch_column

This is an old revision of the document!


PHP RFC: Add fetch_column method to mysqli

Introduction

There are 3 methods in mysqli that return arrays from result set, one that returns stdClass object, and one that returns an array of arrays. However, mysqli doesn't have a method to return a scalar value.

Proposal

As of now, there is no easy way to fetch a scalar value in mysqli. If the SQL statement can return no rows then the simplest way to fetch scalars is to fetch an array and using the null-coalescing operator default the value to false. This is due to all the fetch methods returning false if no more rows are present in the result set.

$result = $mysqli->query('SELECT username FROM users WHERE id = 123');
echo $result->fetch_row()[0] ?? false;

This RFC borrows the idea from PDO to add another method to mysqli_result class. The method would be called fetch_column to keep with the existing mysqli naming convention.

The above example can then be simplified to a single method call:

$result = $mysqli->query('SELECT username FROM users WHERE id = 123');
echo $result->fetch_column();

The new method will also accept an optional integer parameter to specify which column to fetch from the current row. The index is 0-based just like in PDO.

Just like with other fetch_* methods this one will also move the internal result pointer to the next row when called.

Difference between PDO and mysqli

There will be only two differences from PDO: the name of the method, and the fact that MySQL doesn't have boolean types thus this method can never return a boolean. However, the method can still return false, which indicates that no row could be fetched from the result.

Backward Incompatible Changes

None.

Proposed PHP Version(s)

Next PHP 8.X (8.1)

RFC Impact

This RFC only adds a new method to an existing class. It is just syntactic sugar to avoid the null-coalescing operator.

New Constants

None

php.ini Defaults

None.

Future Scope

It would be nice to have similar functionality available with mysqli_result::fetch_all(). This is not included in the current proposal due to the fact that this change would involve adding a new optional parameter, which would have to be an integer or a string. The parameter couldn't be limited in scope to only column index, but the method would have to be extended to handle classes with the MYSQLI_CLASS constant as well. This is certainly possible but would be much more work.

Of course, it is very simple to fetch single column values into an array with the current functionality:

$result = $mysqli->query('SELECT username FROM users');
$usernames = [];
foreach ($result as ['username' => $usernames[]]);

Proposed Voting Choices

Simple yes/no vote.

Patches and Tests

The implementation is available at https://github.com/php/php-src/pull/6798

Implementation

After the project is implemented, this section should contain

  1. the version(s) it was merged into
  2. a link to the git commit(s)
  3. a link to the PHP manual entry for the feature
  4. a link to the language specification section (if any)

References

rfc/mysqli_fetch_column.1616946444.txt.gz · Last modified: 2021/03/28 15:47 by dharman