mysqli_fetch_assoc() Function Explained with Examples

The mysqli_fetch_assoc() function fetches a result row as an associative array. It returns an array where the column names are the keys and the row data are the values.

Basic Syntax

array mysqli_fetch_assoc(mysqli_result $result)

• $result: A result set identifier returned by mysqli_query()
• Returns: An associative array representing the fetched row, or NULL if there are no more rows

Example 1: Basic Usage

<?php
// Create connection
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Execute query
$sql = "SELECT id, name, email FROM users";
$result = $mysqli->query($sql);

// Check if query was successful
if ($result === false) {
    die("Query failed: " . $mysqli->error);
}

// Fetch data as associative array
echo "<h2>User List</h2>";
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>";

while ($row = mysqli_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['id']) . "</td>";
    echo "<td>" . htmlspecialchars($row['name']) . "</td>";
    echo "<td>" . htmlspecialchars($row['email']) . "</td>";
    echo "</tr>";
}

echo "</table>";

// Free result set
mysqli_free_result($result);

// Close connection
$mysqli->close();
?>

Example 2: Procedural Style

<?php
// Create connection (procedural style)
$link = mysqli_connect("localhost", "username", "password", "database");

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

// Execute query
$sql = "SELECT * FROM products WHERE category = 'electronics'";
$result = mysqli_query($link, $sql);

// Check if any rows were returned
if (mysqli_num_rows($result) > 0) {
    echo "<h2>Electronic Products</h2>";
    
    // Fetch each row as associative array
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Product: " . htmlspecialchars($row['product_name']) . "<br>";
        echo "Price: $" . htmlspecialchars($row['price']) . "<br>";
        echo "Stock: " . htmlspecialchars($row['stock_quantity']) . "<br>";
        echo "Description: " . htmlspecialchars($row['description']) . "<br>";
        echo "<hr>";
    }
} else {
    echo "No electronic products found.";
}

// Free result set
mysqli_free_result($result);

// Close connection
mysqli_close($link);
?>

Example 3: Working with Specific Columns

<?php
$mysqli = new mysqli("localhost", "username", "password", "company_db");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Get employees with their department information
$sql = "SELECT e.emp_id, e.first_name, e.last_name, e.salary, d.dept_name 
        FROM employees e 
        JOIN departments d ON e.dept_id = d.dept_id 
        WHERE e.salary > 50000";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    echo "<h2>Highly Paid Employees</h2>";
    
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['emp_id'] . "<br>";
        echo "Name: " . $row['first_name'] . " " . $row['last_name'] . "<br>";
        echo "Department: " . $row['dept_name'] . "<br>";
        echo "Salary: $" . number_format($row['salary'], 2) . "<br>";
        echo "-------------------<br>";
    }
} else {
    echo "No employees found with salary greater than $50,000";
}

$mysqli->close();
?>

Example 4: Building a Data Array for JSON

<?php
$mysqli = new mysqli("localhost", "username", "password", "blog_db");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Fetch blog posts
$sql = "SELECT post_id, title, content, author, created_at FROM posts WHERE published = 1";
$result = $mysqli->query($sql);

$posts = array();

if ($result->num_rows > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        // Add each post to the array
        $posts[] = array(
            'id' => $row['post_id'],
            'title' => $row['title'],
            'content' => $row['content'],
            'author' => $row['author'],
            'date' => $row['created_at']
        );
    }
}

// Output as JSON
header('Content-Type: application/json');
echo json_encode($posts);

$mysqli->close();
?>

Example 5: Form Processing with Database Results

<?php
// Process form data and display results
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $search_term = $_POST['search'] ?? '';
    
    $mysqli = new mysqli("localhost", "username", "password", "library");
    
    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }
    
    // Prepared statement for security
    $stmt = $mysqli->prepare("SELECT book_id, title, author, isbn, year_published FROM books WHERE title LIKE ? OR author LIKE ?");
    $search_pattern = "%$search_term%";
    $stmt->bind_param("ss", $search_pattern, $search_pattern);
    $stmt->execute();
    $result = $stmt->get_result();
    
    echo "<h2>Search Results for: " . htmlspecialchars($search_term) . "</h2>";
    
    if ($result->num_rows > 0) {
        echo "<ul>";
        while ($row = $result->fetch_assoc()) {
            echo "<li>";
            echo "<strong>" . htmlspecialchars($row['title']) . "</strong> ";
            echo "by " . htmlspecialchars($row['author']) . " ";
            echo "(" . htmlspecialchars($row['year_published']) . ")";
            echo " - ISBN: " . htmlspecialchars($row['isbn']);
            echo "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p>No books found matching your search criteria.</p>";
    }
    
    $stmt->close();
    $mysqli->close();
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Book Search</title>
</head>
<body>
    <h1>Book Search</h1>
    <form method="POST">
        <input type="text" name="search" placeholder="Search by title or author" required>
        <button type="submit">Search</button>
    </form>
</body>
</html>

Example 6: Pagination with mysqli_fetch_assoc()

<?php
$mysqli = new mysqli("localhost", "username", "password", "ecommerce");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Pagination parameters
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = 10;
$offset = ($page - 1) * $per_page;

// Get total number of products
$total_result = $mysqli->query("SELECT COUNT(*) as total FROM products");
$total_row = $total_result->fetch_assoc();
$total_products = $total_row['total'];
$total_pages = ceil($total_products / $per_page);

// Get products for current page
$sql = "SELECT product_id, name, price, description, image_url 
        FROM products 
        ORDER BY product_id 
        LIMIT $offset, $per_page";
$result = $mysqli->query($sql);

echo "<h2>Products (Page $page of $total_pages)</h2>";

if ($result->num_rows > 0) {
    echo "<div class='products-grid'>";
    while ($row = $result->fetch_assoc()) {
        echo "<div class='product'>";
        echo "<h3>" . htmlspecialchars($row['name']) . "</h3>";
        echo "<img src='" . htmlspecialchars($row['image_url']) . "' alt='" . htmlspecialchars($row['name']) . "'>";
        echo "<p>" . htmlspecialchars($row['description']) . "</p>";
        echo "<p class='price'>$" . number_format($row['price'], 2) . "</p>";
        echo "</div>";
    }
    echo "</div>";
    
    // Pagination links
    echo "<div class='pagination'>";
    for ($i = 1; $i <= $total_pages; $i++) {
        $active = $i == $page ? "class='active'" : "";
        echo "<a href='?page=$i' $active>$i</a> ";
    }
    echo "</div>";
} else {
    echo "<p>No products found.</p>";
}

$mysqli->close();
?>

Example 7: Error Handling and Debugging

<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT user_id, username, email, registration_date FROM users WHERE active = 1";
$result = $mysqli->query($sql);

// Check if query was successful
if ($result === false) {
    die("Query error: " . $mysqli->error);
}

// Check if any rows were returned
if ($result->num_rows === 0) {
    echo "No active users found.";
    $mysqli->close();
    exit;
}

echo "<h2>Active Users</h2>";
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Username</th><th>Email</th><th>Registration Date</th></tr>";

// Fetch data with proper error handling
while ($row = $result->fetch_assoc()) {
    if ($row === false) {
        echo "<tr><td colspan='4'>Error fetching row</td></tr>";
        continue;
    }
    
    echo "<tr>";
    echo "<td>" . ($row['user_id'] ?? 'N/A') . "</td>";
    echo "<td>" . htmlspecialchars($row['username'] ?? 'Unknown') . "</td>";
    echo "<td>" . htmlspecialchars($row['email'] ?? 'No email') . "</td>";
    echo "<td>" . ($row['registration_date'] ?? 'Unknown') . "</td>";
    echo "</tr>";
}

echo "</table>";

// Free result set
$result->free();

// Close connection
$mysqli->close();
?>

Example 8: Complex Data Processing

<?php
$mysqli = new mysqli("localhost", "username", "password", "school_db");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Get students with their grades and course information
$sql = "SELECT s.student_id, s.first_name, s.last_name, 
               c.course_name, g.grade, g.semester
        FROM students s
        JOIN grades g ON s.student_id = g.student_id
        JOIN courses c ON g.course_id = c.course_id
        ORDER BY s.last_name, s.first_name, c.course_name";

$result = $mysqli->query($sql);

$students = array();

if ($result->num_rows > 0) {
    // Organize data by student
    while ($row = $result->fetch_assoc()) {
        $student_id = $row['student_id'];
        
        if (!isset($students[$student_id])) {
            $students[$student_id] = array(
                'name' => $row['first_name'] . ' ' . $row['last_name'],
                'courses' => array()
            );
        }
        
        $students[$student_id]['courses'][] = array(
            'course' => $row['course_name'],
            'grade' => $row['grade'],
            'semester' => $row['semester']
        );
    }
}

// Display organized data
foreach ($students as $student_id => $student_data) {
    echo "<h3>" . htmlspecialchars($student_data['name']) . "</h3>";
    echo "<ul>";
    
    foreach ($student_data['courses'] as $course) {
        echo "<li>" . htmlspecialchars($course['course']) . 
             " - Grade: " . htmlspecialchars($course['grade']) . 
             " (" . htmlspecialchars($course['semester']) . ")</li>";
    }
    
    echo "</ul>";
}

$mysqli->close();
?>

Key Points to Remember

1. Always check if the query was successful before fetching results
2. Use mysqli_num_rows() to check if any rows were returned
3. Always escape output using htmlspecialchars() when displaying data
4. Free the result set with mysqli_free_result() when done
5. Close the connection with mysqli_close()
6. Use prepared statements for queries with user input to prevent SQL injection
7. The function returns NULL when there are no more rows to fetch

Common Mistakes to Avoid

1. Not checking if the query succeeded before fetching
2. Forgetting to free the result set (can cause memory issues)
3. Not closing the database connection
4. Using the same result set multiple times without resetting
5. Not handling potential NULL values from the database

The mysqli_fetch_assoc() function is essential for working with MySQL databases in PHP, providing an easy way to access query results using column names as array keys.