Using mysqli_fetch_assoc to Retrieve Query Results in PHP

In PHP, database operations are essential, and the mysqli extension provides a common way to interact with MySQL. This article demonstrates how to use the mysqli_fetch_assoc function to fetch query results as associative arrays.

1. Connect to the Database

First, establish a connection with mysqli_connect. The example below defines connection parameters and checks for a successful connection.

$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

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

2. Query the Database

Use mysqli_query to execute an SQL SELECT statement, then retrieve rows with mysqli_fetch_assoc.

$sql = "SELECT id, name, email FROM users";
$result = mysqli_query($conn, $sql);

Iterate over the result set:

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row["id"] . ", Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
}

3. Complete Example

The following script combines connection, query, result handling, and cleanup, showing a full workflow for retrieving data with mysqli_fetch_assoc.

$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT id, name, email FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . ", Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);

Remember to adjust database credentials and table structures to match your environment. Using mysqli_fetch_assoc simplifies result processing and improves development efficiency.