PHPize Online / SQLize Online  /  SQLtest Online

<?php $query = "with recursive calendar as ( select '2005-07-01' date union all select date_add(date, interval 1 day) from calendar where date < '2005-07-31' ) select distinct date, count(payment_id) over (order by date) payments_rolling_count, coalesce(sum(amount) over (order by date), 0) payments_rolling_amount from calendar left join payment on date(payment_date) = calendar.date; "; // 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 );