mysqli_fetch_assoc() in Detail

The mysqli_fetch_assoc() function is a crucial tool that turns a row from your database into a PHP array, where the keys are the names of your columns. This means you do not have to guess where the data is; you simply call it by its name.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

// Select fruits and vegetables from our table
$result = mysqli_query($connection, "SELECT fruit, vegetable FROM mysqli_table");

echo "Data list:<br>";

// The magic happens here: the row becomes an associative array
// NOTE: The mysqli_fetch_assoc() function automatically creates keys 
// in the $row array that match the SQL column names. Therefore, we can 
// access the data directly via text keys: $row["fruit"] and $row["vegetable"].
while ($row = mysqli_fetch_assoc($result)) {
    echo "Fruit: " . $row["fruit"] . " | Vegetable: " . $row["vegetable"] . "<br>";
}

mysqli_free_result($result);
mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Why is this even important? When working on larger projects, database tables change frequently. If you used numerical indexes ($row[0]), your code would break when a new column is added. With associative fetching, your code remains safe and readable.

Using a WHILE Loop

This function is most commonly found inside a while loop. Why? Because with each call, the function "grabs" one row and moves the invisible pointer to the next. When there are no more rows left, the function returns null, and the loop safely stops.

Simplicity: Instead of $row[3], you use $row["lastname"].
Fewer errors: Even if you change the order of columns in the SQL statement (e.g., SELECT firstname, lastname instead of SELECT lastname, firstname), your output will still be correct.
Memory: It uses half the memory of mysqli_fetch_array() because it does not create duplicate indexes.