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 bymysqli_query()
when executing aSELECT
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 awhile
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.