How to Extract a Specific Column from a mysqli_result into an Array? Use array_column() for Easy Extraction

M66 2025-07-18

When working with PHP to interact with MySQL databases, many developers use mysqli_query to execute queries, which returns a mysqli_result object. Sometimes, we just want to extract a specific column of data, such as all usernames or IDs, into an array. Doing this manually with a while loop can be cumbersome. However, PHP's built-in array_column() function can make this process much simpler.

This article will show you how to quickly extract a specific column from a mysqli_result using array_column().

Basic Query

Assume we have a user table called users, with fields id and username. We want to extract all usernames into an array.

<?php
$mysqli = new mysqli('localhost', 'db_user', 'db_pass', 'db_name');
if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}
<p>$sql = "SELECT id, username FROM users";<br>
$result = $mysqli->query($sql);<br>
if (!$result) {<br>
die('Query failed: ' . $mysqli->error);<br>
}<br>

At this point, $result is a mysqli_result object. We need to convert it to an array before using array_column().

Extract as an Array

The mysqli_fetch_all() function can convert the result set into a two-dimensional array. Be sure to use the MYSQLI_ASSOC parameter to ensure you're working with an associative array.

$rows = $result->fetch_all(MYSQLI_ASSOC);

At this point, the structure of $rows would look like this:

[
    ['id' => 1, 'username' => 'alice'],
    ['id' => 2, 'username' => 'bob'],
    ['id' => 3, 'username' => 'charlie']
]

Now you can use array_column() to extract the username column:

$usernames = array_column($rows, 'username');

The result of $usernames will be:

['alice', 'bob', 'charlie']

If you want to extract the id column and use id as the key, you can do the following:

$usernamesById = array_column($rows, 'username', 'id');

In this case, $usernamesById will be:

[
    1 => 'alice',
    2 => 'bob',
    3 => 'charlie'
]

Complete Example

<?php
$mysqli = new mysqli('localhost', 'db_user', 'db_pass', 'db_name');
if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}
<p>$sql = "SELECT id, username FROM users";<br>
$result = $mysqli->query($sql);<br>
if (!$result) {<br>
die('Query failed: ' . $mysqli->error);<br>
}</p>
<p>$rows = $result->fetch_all(MYSQLI_ASSOC);<br>
$usernames = array_column($rows, 'username');</p>
<p>print_r($usernames);</p>
<p data-is-last-node="" data-is-only-node="">$mysqli->close();<br>
?><br>

mysqli_result