Using mysqli_fetch_assoc to Retrieve Query Results in PHP

In PHP development, database operations are essential, and the mysqli extension is a common way to interact with MySQL. This guide shows how to use the mysqli_fetch_assoc function to obtain query results.

1. Connect to the Database

First, establish a connection with mysqli_connect. The following code demonstrates the connection setup and error handling.

$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

Next, run an SQL statement with mysqli_query:

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

After executing the query, fetch each row using mysqli_fetch_assoc, which returns the row as an associative array. The function is called repeatedly until no more rows are left.

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

3. Complete Example

The full example below combines connection, query execution, result fetching, and closing the connection. Remember to replace the database credentials and table structure with your own.

$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);

Note: Adjust the database name, table, and column names to match your actual schema.

Conclusion

The article demonstrated how to use mysqli_fetch_assoc to retrieve query results efficiently. By connecting to the database, executing a query, and iterating over the result set with mysqli_fetch_assoc, developers can easily access data and improve backend development productivity.