create array from mysql query php

create array from mysql query php

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

Stefan Bogdanescu

Founder & Senior Architect

Fetching Multiple Values from MySQL into a PHP Array

When you run a query like SELECT type FROM cars WHERE owner='...', your database engine returns a result set, not a single scalar value. If you attempt to process this immediately using mysql_fetch_array($result), you only grab the first row. That is why your script printed just one array with a single entry. To get a list of all matching types—whether there are two, five, or fifty—you must iterate through the entire result set and aggregate the data into a PHP structure.

The most straightforward approach in legacy PHP environments using the mysql_* extension (which is deprecated but still referenced here) involves looping through the rows and pushing values into an array. You can initialize an empty array and append each value as you fetch it:

$types = [];
while ($row = mysql_fetch_assoc($result)) {
    $types[] = $row['type'];
}

Alternatively, using array_push works exactly the same way, though the concatenation operator ([]) is generally preferred in modern PHP for its readability. This loop ensures you capture every row returned by the query until mysql_fetch_assoc returns false. Once the loop completes, $types will contain a clean array of integers like [19, 18, 17, 16], ready for further processing.

Using Column Aliases for Clarity

In your source material, the column selected is named type. When you fetch the row as an associative array (using mysql_fetch_assoc), PHP uses the column name as the key. This is useful for debugging, but it can sometimes lead to confusion if you are mixing indexed and associative access or if column names clash with PHP keywords.

If you want to ensure your code remains robust even if the database schema changes slightly, consider renaming the column in your SQL query using an alias:

$sql = "SELECT type AS car_type FROM cars WHERE owner='" . mysql_real_escape_string($_SESSION['username']) . "' AND selling='0' ORDER BY id DESC";
$result = mysql_query($sql, $con);

$types = [];
while ($row = mysql_fetch_assoc($result)) {
    $types[] = $row['car_type'];
}

By aliasing the column to car_type, you decouple your PHP logic from the specific database column name. If you ever decide to rename the database column to something else, your PHP code won't break immediately. This is a best practice that reduces technical debt.

Modernizing with PDO or MySQLi

It is important to note that the mysql_* functions are deprecated and removed in newer versions of PHP. In production environments today, you should migrate to either MySQLi or PDO. These drivers support prepared statements, which prevent SQL injection attacks by separating the query logic from user input.

Here is how you would achieve the same result using MySQLi:

$stmt = $mysqli->query("SELECT type AS car_type FROM cars WHERE owner = ? AND selling = 0 ORDER BY id DESC", [$username]);

$types = $stmt->fetch_all(MYSQLI_ASSOC);
// Now $types is an array of associative arrays. If you only need values:
$types = array_column($types, 'car_type');

The fetch_all method returns a two-dimensional array by default. To flatten it into a simple list of types, the array_column function is extremely efficient. It extracts a single column from a multidimensional array and returns it as a simple indexed array. This approach is often faster and cleaner than manually looping and pushing values in older PHP versions.

Performance Considerations

If you are querying a large dataset with thousands of rows, loading them all into memory at once might not be ideal for every use case. However, for fetching a list of types associated with a user, the result set is typically small. In such scenarios, fetching all data and transforming it in PHP is usually faster than making multiple round-trips to the database or using complex cursor-based streaming logic.

The trade-off here is memory usage versus network latency. If you need to process these types immediately and discard them, keeping them in a simple indexed array is efficient. If you need to maintain the association between other columns (like car names), you would keep the multidimensional structure and access it via keys.

For developers looking to streamline their workflow or find more patterns for handling database results, resources like https://laravelcompany.com can offer insights into modern PHP practices, though specific implementation details often depend on the framework or legacy stack you are maintaining.

Our network