How get all values in a column using PHP?

How get all values in a column using PHP?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Extracting Column Values into Arrays in PHP

Retrieving all values from a single MySQL column and storing them in a PHP array is a common data aggregation task. However, the approach you take depends heavily on whether you are using legacy functions or modern database drivers, as well as how your application architecture handles database interactions. The specific query SELECT names FROM Customers is valid SQL and will return a result set containing only that column. The challenge lies in iterating over that result set efficiently and safely.

If you are working with older PHP versions or legacy codebases, you might encounter the deprecated mysql_* functions like mysql_fetch_array(). These functions are no longer supported as of PHP 7.1 and will trigger fatal errors in modern environments. Attempting to use them today is a significant pitfall that leads to immediate application failure. Instead, you should migrate to either MySQLi or PDO, which provide active support and better security features against SQL injection.

When using the MySQLi extension, the most straightforward method involves executing your query and looping through the resulting rows. You can initialize an empty array, then iterate until there are no more rows. Inside the loop, you append the value to your collection. This manual iteration ensures you have full control over the data structure but requires explicit loop management.

$connection = new mysqli($host, $user, $password, $database);

// Check connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

$query = "SELECT names FROM Customers";
$result = $connection->query($query);

$namesArray = [];

while ($row = $result->fetch_assoc()) {
    // Ensure the column exists to avoid undefined index notices
    if (isset($row['names'])) {
        $namesArray[] = $row['names'];
    }
}

// Free result set and close connection
$result->free();
$connection->close();

There is a more concise alternative available in MySQLi: fetch_array(). This function can be called directly inside the while loop without assigning it to a variable first. While this reduces code verbosity, it still requires the same iteration logic. The core mechanism remains the same: fetch one row at a time until the cursor reaches the end of the result set.

PDO offers a similar pattern but with a slightly different syntax for fetching rows. You can use PDO::FETCH_ASSOC as the second argument to fetch() to ensure you are retrieving associative arrays, which map column names to keys. This is generally preferred for readability and type safety in modern PHP applications.

$pdo = new PDO("mysql:host=localhost;dbname=customers", $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->query("SELECT names FROM Customers");
$namesArray = [];

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $namesArray[] = $row['names'];
}

A critical consideration when performing this operation is performance on large datasets. If the Customers table contains millions of rows, fetching them one by one in PHP and building an array in memory can lead to significant memory consumption. While this approach is fine for small tables (like the 5-row example in your scenario), it may not scale well without optimization. In such cases, you might consider using SQL aggregation functions like GROUP_CONCAT if your database engine supports string concatenation of column values directly on the server side before sending data to PHP.

However, be aware that GROUP_CONCAT has a default length limit (typically 1024 characters) and can produce very long strings that might not be suitable for direct array manipulation in all contexts. It is often better to stick to row-based iteration unless you specifically need the concatenated string format.

For developers building modern applications, integrating this logic into an ORM or a framework like Laravel simplifies the process further. Frameworks often provide query builders that handle result set mapping automatically, allowing you to define the model and retrieve the attribute collection without writing raw loops. This abstraction reduces boilerplate code but requires understanding how the framework maps database columns to class properties.

Ultimately, the choice between manual iteration with MySQLi/PDO or using a framework depends on your project's complexity and architectural constraints. Regardless of the method chosen, always validate that the data exists before accessing keys to prevent runtime errors in production environments.

Our network