PHP 8.1 : new fetch_column() method for mysqli
24, October 2021

Besides PDO, many of us still use mysqli to interact with MySQL database from PHP. Compared to PDO, mysqli lacks several functionality that could help us to write less code than it is necessary. Ability to fetch single column value is such missing functionality in mysqli.

To fetch a single column value, you need to use fetchColumn() method in PDO

$stmt = $pdo->prepare("SELECT name FROM persons WHERE id = 10");

echo $stmt->fetchColumn();
// Alice

Until now, mysqli didn't have any simple method to fetch such data. Current fetch related methods in mysqli usually either returns array or stdClass. Fetching a single column data requires us to seemingly unpleasant line of code such as:

$result = $mysqli->query("SELECT name FROM persons WHERE id = 10");

echo $result->fetch_row()[0] ?? false;
// Alice

PHP 8.1 brings fetch_column() method

Now we fetch similar data with mysqli:

$result = $mysqli->query("SELECT name FROM persons WHERE id = 10");

echo $result->fetch_column();
// Alice

fetch_column() will return false if there is no data matched by the query.

This method optionally takes a int parameter that can fetch different column if the query selects multiple column.

// this query selects multiple column
$result = $mysqli->query("SELECT name, city, country FROM persons WHERE id = 10");

echo $result->fetch_column(0); // <-- 0 means we want the first column
// Alice

echo $result->fetch_column(1); // <-- 1 means we want the second column, which is city
// New York

echo $result->fetch_column(2); // <-- 2 means we want the third column, which is country
// USA

Note: fetch_column() only returns column value of a single row. We don't have any such method to fetch single column value of multiple rows. You can loop through result set and use fetch_column() to build such list by yourself.


Write comment about this article: