Leaderboard

Create a "valid_ratings" table in your DB containg a row for each valid rating (1-5). Useful for creating dropdowns or radio button lists for the user to choose from. If you don't want to this, you can create it as a temporary table when you want thse totals # # Additional valid ratings table # $pdo->exec("create temporary table valid_rating (rating int unsigned not null primary key)"); $pdo->exec("insert into valid_rating values (1), (2), (3), (4), (5)"); # # get ratings totals # $res = $pdo->query("SELECT v.rating , count(r.id) as total FROM valid_rating v LEFT JOIN reviews r USING (rating) GROUP BY rating; "); printf("<pre>\n| Rating | Total |\n\n"); foreach ($res as $r) { printf("| %6d | %5d |\n", $r['rating'], $r['total']); } | Rating | Total | | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 0 | | 5 | 3 | An alternative solution is the one @gizmola alluded to $results = array_fill_keys(range(1,5), 0); // create array with 0 total for all possible ratings $res = $pdo->query("SELECT rating , count(*) as total FROM reviews GROUP BY rating "); foreach ($res as $r) { $results[$r['rating']] = $r['total']; // put totals from query into the array } printf("<pre>\n| Rating | Total |\n\n"); foreach ($results as $rating => $total) { printf("| %6d | %5d |\n", $rating, $total); }

Find an API for the CRM and write PHP code to use it. You haven't given enough information to be able to answer better than that...