PHP MySQL mysqli_fetch_assoc() function


The mysqli_fetch_assoc() function in PHP is used to fetch a result row from a MySQLi result set as an associative array. When working with MySQLi in procedural style, this function is commonly used to retrieve rows from the result of a SELECT query. Here’s a detailed explanation:

Syntax

mysqli_fetch_assoc(result)

Parameters

  • result: The mysqli_result object returned by mysqli_query() when executing a SELECT query. This object represents the result set from the query.

Return Values

  • On success: Returns an associative array where the keys are the column names and the values are the corresponding data from the row.
  • On failure: Returns null when there are no more rows to fetch (i.e., when all rows have been retrieved).

Example Usage

Here’s a step-by-step example demonstrating how to use mysqli_fetch_assoc() to fetch and display rows from a MySQL database:

1. Establish a Database Connection

<?php
// Connect to the database $conn = mysqli_connect('localhost', 'username', 'password', 'database'); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }

2. Execute a SELECT Query

// Prepare and execute a SELECT query
$sql = "SELECT username, email FROM users"; $result = mysqli_query($conn, $sql);

3. Fetch and Display Rows

if ($result) {
// Fetch rows one by one while ($row = mysqli_fetch_assoc($result)) { // Access data using column names as array keys echo "Username: " . $row['username'] . " - Email: " . $row['email'] . "<br>"; } // Free the result set mysqli_free_result($result); } else { echo "Error: " . mysqli_error($conn); }

4. Close the Database Connection

// Close the connection
mysqli_close($conn); ?>

How It Works

  • Fetching Rows: Each call to mysqli_fetch_assoc() retrieves the next row from the result set. It returns an associative array representing that row, where the array keys are the column names from the result set.

  • Looping Through Rows: You typically use mysqli_fetch_assoc() in a while loop to iterate over all rows in the result set until there are no more rows to fetch.

  • Freeing Result Set: After you have finished processing the result set, use mysqli_free_result() to free the memory associated with the result set.

Notes

  • Column Names: The keys of the associative array returned by mysqli_fetch_assoc() are the column names from the result set. Ensure that the column names are correctly referenced as they appear in the database schema.
  • Error Handling: Always check the result of mysqli_query() to handle potential errors before attempting to fetch rows.