PHPize Online / SQLize Online  /  SQLtest Online

<?php $query = "WITH counts AS ( SELECT c.name AS category, f.title AS most_rented_films, COUNT(DISTINCT rental_id) rentals_count FROM film f JOIN inventory i USING(film_id) JOIN rental r USING(inventory_id) JOIN film_category USING (film_id) JOIN category c USING(category_id) WHERE rental_date between '2005-01-01' AND '2006-01-01' GROUP BY f.title, c.name ), counts_ordered AS ( SELECT category, most_rented_films, rentals_count, rank() over (partition by category order by rentals_count desc) rental_rank FROM counts ) SELECT category, GROUP_CONCAT(most_rented_films ORDER BY most_rented_films ASC) most_rented_films, rentals_count FROM counts_ordered WHERE rental_rank = 1 GROUP BY category, rentals_count ORDER BY category ASC;"; // get DB version using PDO $stmt = $pdo->prepare($query); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_NUM); $columns = []; for ($i = 0; $i < $stmt->columnCount(); $i++) { $col = $stmt->getColumnMeta($i); $columns[] = [ 'header'=>$col['name'], 'pdo_type'=>$col['pdo_type'] ]; } echo json_encode([[ 'headers' => $columns, 'data' => $rows ]], JSON_PRETTY_PRINT );