PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE tbltc ( id INT AUTO_INCREMENT PRIMARY KEY, pn INT NOT NULL, y INT NOT NULL, scenario VARCHAR(50) NOT NULL, billed DATE NOT NULL, cat CHAR(1) NOT NULL ); INSERT INTO tbltc (pn, y, scenario, billed, cat) VALUES (1, 2011, 'Scenario1', '2011-11-18', 'C'), (1, 2011, 'Scenario1', '2011-11-18', 'C'), (1, 2011, 'Scenario1', '2011-11-18', 'I'), (1, 2011, 'Scenario1', '2011-11-22', 'C'), (1, 2011, 'Scenario1', '2011-11-22', 'C');
Copy Clear
Copy Format Clear
<?php $sql = <<<SQL SELECT DATE_FORMAT(tbltc.billed, '%b %d, %Y') billed_date, tbltc.cat, COUNT(*) count FROM tbltc WHERE tbltc.pn = ? AND tbltc.y = ? AND tbltc.scenario = ? GROUP BY billed_date, tbltc.cat ORDER BY billed_date, tbltc.cat SQL; $params = [1, 2011, 'Scenario1']; $result = []; foreach ($mysqli->execute_query($sql, $params) as $row) { $date = $row['billed_date']; $result[$date][$row['cat']] = $row['count']; } var_export($result);
Show:  
Copy Clear