Fetch Database Rows with mysqli_fetch_assoc

This article explains how to retrieve a single row of data from a MySQL database as an associative array using the mysqli_fetch_assoc() function in PHP. You will learn the basic syntax, see a practical code example for fetching a specific record, and understand how to access the returned data using column names as keys.

Understanding mysqli_fetch_assoc()

The mysqli_fetch_assoc() function fetches a result row from a database query and returns it as an associative array. In this array, the keys correspond directly to the table’s column names, making it easy to access specific field values. Each time you call this function, it returns the next row from the active result set, or null if there are no more rows.

To fetch just a single row (for example, retrieving a specific user by their ID), you only need to call the function once on your query result without placing it inside a loop.

Step-by-Step Code Example

Below is a complete PHP example demonstrating how to connect to a database, execute a query, and fetch a single row of data.

<?php
// 1. Establish a database connection
$connection = mysqli_connect("localhost", "username", "password", "database_name");

// Check connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// 2. Define and execute the SQL query
$id = 1; // Example ID to fetch
$query = "SELECT id, username, email FROM users WHERE id = $id LIMIT 1";
$result = mysqli_query($connection, $query);

if ($result) {
    // 3. Fetch the single row as an associative array
    $row = mysqli_fetch_assoc($result);

    if ($row) {
        // 4. Access the data using column names as keys
        echo "ID: " . $row['id'] . "<br>";
        echo "Username: " . $row['username'] . "<br>";
        echo "Email: " . $row['email'] . "<br>";
    } else {
        echo "No record found with that ID.";
    }

    // Free the result set memory
    mysqli_free_result($result);
} else {
    echo "Query failed: " . mysqli_error($connection);
}

// 5. Close the connection
mysqli_close($connection);
?>

Key Points to Remember

• Associative Keys: The keys of the returned array are case-sensitive and must match the column names defined in your SQL query or database table exactly (e.g., $row['username']).
• Single Row vs. Loops: When you only expect one row (such as queries using LIMIT 1 or filtering by a primary key), a single if ($row = mysqli_fetch_assoc($result)) statement is sufficient. If you need to fetch multiple rows, you would instead use a while loop.
• Return Value: If the query returns no records, mysqli_fetch_assoc() returns null. It is best practice to verify that the array is not empty before attempting to access its keys.