How can you efficiently count the occurrences of each category in a database using PHP?

To efficiently count the occurrences of each category in a database using PHP, you can use a SQL query to group the results by category and then fetch the counts for each category. This can be achieved by using the COUNT function in SQL along with a GROUP BY clause. Once the data is fetched, you can store the counts in an associative array for easy access. 

<?php

// Connect to your database
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');

// SQL query to count occurrences of each category
$sql = "SELECT category, COUNT(*) AS count FROM your_table GROUP BY category";
$stmt = $pdo->query($sql);

// Fetch the results and store counts in an associative array
$categoryCounts = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $categoryCounts[$row['category']] = $row['count'];
}

// Output the counts
foreach ($categoryCounts as $category => $count) {
    echo "Category: $category, Count: $count" . PHP_EOL;
}

?>

Keywords

PHP database count occurrences category

Related Questions