PhpHow-ToBeginner · 4 min read

How to Fetch Data as Array in PHP: Simple Guide

In PHP, you can fetch data as an array using functions like mysqli_fetch_assoc() for MySQLi or PDOStatement::fetch() with PDO::FETCH_ASSOC for PDO. These functions return each row of the result as an associative array, making it easy to access data by column names.

📐

Syntax

Here are the common ways to fetch data as an array in PHP:

MySQLi: Use mysqli_fetch_assoc($result) to get a row as an associative array.
PDO: Use $stmt->fetch(PDO::FETCH_ASSOC) to fetch a row as an associative array.

$result or $stmt is the result set from a database query.

php

<?php
// MySQLi syntax
$row = mysqli_fetch_assoc($result);

// PDO syntax
$row = $stmt->fetch(PDO::FETCH_ASSOC);
?>

💻

Example

This example connects to a MySQL database using MySQLi, runs a query, and fetches all rows as associative arrays.

php

<?php
$mysqli = new mysqli('localhost', 'user', 'password', 'database');

if ($mysqli->connect_error) {
    die('Connect Error: ' . $mysqli->connect_error);
}

$sql = 'SELECT id, name FROM users';
$result = $mysqli->query($sql);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        echo 'ID: ' . $row['id'] . ', Name: ' . $row['name'] . "\n";
    }
    $result->free();
} else {
    echo 'Query Error';
}

$mysqli->close();
?>

Output

ID: 1, Name: Alice ID: 2, Name: Bob ID: 3, Name: Carol

⚠️

Common Pitfalls

Common mistakes when fetching data as arrays include:

Using mysqli_fetch_array() without specifying fetch type, which returns both numeric and associative keys and can cause confusion.
Not checking if the query result is valid before fetching.
Forgetting to free the result set or close the connection.

Always prefer mysqli_fetch_assoc() or PDO::FETCH_ASSOC for clear, associative arrays.

php

<?php
// Wrong: fetch_array returns both numeric and associative keys
$row = mysqli_fetch_array($result);
// Right: fetch_assoc returns only associative keys
$row = mysqli_fetch_assoc($result);
?>

📊

Quick Reference

Function/Method	Description	Returns
mysqli_fetch_assoc($result)	Fetches a result row as an associative array	Associative array
mysqli_fetch_array($result, MYSQLI_ASSOC)	Fetches a result row as an associative array (explicit)	Associative array
PDOStatement::fetch(PDO::FETCH_ASSOC)	Fetches next row as an associative array	Associative array
PDOStatement::fetchAll(PDO::FETCH_ASSOC)	Fetches all rows as an array of associative arrays	Array of associative arrays

✅

Key Takeaways

Use mysqli_fetch_assoc() or PDO fetch with FETCH_ASSOC to get data as associative arrays.

Always check if the query result is valid before fetching data.

Avoid mysqli_fetch_array() without specifying MYSQLI_ASSOC to prevent mixed keys.

Free result sets and close database connections to avoid resource leaks.

PDO offers flexible methods like fetchAll() to get all data at once as arrays.