rfc:mysqli_fetch_column

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 it would require rewriting that method to support 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.

Voting started on 2021-04-17 17:00:00Z and ends on 2021-05-01 17:00:00Z

Add mysqli_fetch_column to mysqli?
Real name Yes No
alec (alec)  
asgrim (asgrim)  
ashnazg (ashnazg)  
bwoebi (bwoebi)  
dharman (dharman)  
ehrhardt (ehrhardt)  
galvao (galvao)  
geekcom (geekcom)  
girgias (girgias)  
kalle (kalle)  
kguest (kguest)  
kinncj (kinncj)  
kocsismate (kocsismate)  
nicolasgrekas (nicolasgrekas)  
nikic (nikic)  
ocramius (ocramius)  
sammyk (sammyk)  
santiagolizardo (santiagolizardo)  
sergey (sergey)  
trowski (trowski)  
Final result: 18 2
This poll has been closed.

Implementation

References

rfc/mysqli_fetch_column.txt · Last modified: 2021/06/11 11:53 by dharman