PHP/MySQL Cant figure out how to group and print data based on my current tables

Question

I have this table, with some sample data.

CREATE TABLE IF NOT EXISTS ooscount (
   id int(10) NOT NULL AUTO_INCREMENT,
   agcid int(3) NOT NULL,
   ooscount int(10) NOT NULL,
   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=246045 ;

--
-- Dumping data for table 'ooscount'
--

INSERT INTO `ooscount` (`id`, `agcid`, `ooscount`, `date`) VALUES
(209855, 4, 2869, '2018-03-01 18:00:02'),
(209856, 2, 3183, '2018-03-01 18:00:02'),
(209857, 1, 4906, '2018-03-01 18:00:02'),
(209860, 3, 5596, '2018-03-01 18:00:02'),
(209863, 14, 6019, '2018-03-01 18:00:02'),
(209864, 16, 7564, '2018-03-01 18:00:02'),
(209873, 4, 2870, '2018-03-01 18:05:01'),
(209874, 2, 3182, '2018-03-01 18:05:01'),
(209876, 1, 4899, '2018-03-01 18:05:01'),
(209877, 3, 5598, '2018-03-01 18:05:01'),
(209879, 14, 6018, '2018-03-01 18:05:01'),
(209882, 16, 7557, '2018-03-01 18:05:01');

I'm setting up a different charting/graphing system for existing and new data and was hoping I didn't need to change my storage methods to MySQL.

My Desired result is something like this: the first number is the agcid, second number is ooscount. I sample this data every 5 minutes, and the desire it to graph the last 7 hours of data by 5 minute intervals.

    "dataProvider": [
                        {
    "1": 2055,
    "2": 3845,
    "3": 4455,
    "4": 5051,
    "14": 9012,
    "16": 6522,
    "date": "2018-03-08 02:45"
},
{
    "1": 2077,
    "2": 3841,
    "3": 4450,
    "4": 5055,
    "14": 9033,
    "16": 6524,
    "date": "2018-03-08 02:50"
},
{
    "1": 2076,
    "2": 3821,
    "3": 4452,
    "4": 5057,
    "14": 9064,
    "16": 6525,
    "date": "2018-03-08 02:55"
},
{
    "1": 2071,
    "2": 3814,
    "3": 4460,
    "4": 5059,
    "14": 9011,
    "16": 6521,
    "date": "2018-03-08 03:00"
},
{
    "1": 2064,
    "2": 3832,
    "3": 4490,
    "4": 5052,
    "14": 9013,
    "16": 6496,
    "date": "2018-03-08 03:05"
},

I can't figure out the best way to achieve this though.

From the tables above, the data would be agcid: ooscount for each agcid in the query (currently 6)

SELECT agcid, ooscount, DATE
FROM ooscount
WHERE agcid
IN ( 1, 2, 3, 4, 14, 16 ) 
AND DATE >= DATE_SUB( NOW( ) , INTERVAL 7 HOUR ) 
ORDER BY DATE
LIMIT 0 , 30

Using AmCharts, the goal is to graph these 6 data points on one chart.

Answer 1

One way would be to group the rows in PHP during your fetch_row loop, for example (error checking omitted for brevity):

$db = new mysqli('server', 'user', 'pass','db');
$result = $db->query("SELECT agcid, ooscount, `date` FROM ooscount WHERE agcid IN ( 1, 2, 3, 4, 14, 16 ) AND DATE >= DATE_SUB( NOW( ) , INTERVAL 7 HOUR )  ORDER BY DATE LIMIT 0 , 30");

$res = [];

$current_row = [];
while ($row = $result->fetch_assoc()) {
        if (!isset($current_row['date']))  {
                $current_row['date'] = $row['date'];
        }
        else if ($current_row['date'] != $row['date']) {
                $res[] = $current_row;
                $current_row = ['date'=>$row['date']];
        }

        $current_row[$row['agcid']] = $row['ooscount'];
}
$res[] = $current_row;

echo json_encode($res);

Another approach is to pivot the result set so that each id is a column and group it by date using the technique from this answer.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(distinct
    CONCAT(
      'sum(case when agcid = ',
      agcid,
      ' then ooscount else 0 end) AS ''',
      replace(agcid, ' ', ''),
      ''''
    )
  ) INTO @sql
from (select distinct agcid from ooscount) a;

SET @sql = CONCAT('SELECT `date`, ', @sql, ' from ooscount where `date` >= DATE_SUB( NOW( ) , INTERVAL 7 HOUR ) group by `date`');
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In this case I'm using the dynamic version to pull all ids, but you can tweak it accordingly if you only want specific ids. Using the dump you provided, this will give you the following resultset:

+---------------------+------+------+------+------+------+------+
| date                | 4    | 2    | 1    | 3    | 14   | 16   |
+---------------------+------+------+------+------+------+------+
| 2018-03-01 18:00:02 | 2869 | 3183 | 4906 | 5596 | 6019 | 7564 |
| 2018-03-01 18:05:01 | 2870 | 3182 | 4899 | 5598 | 6018 | 7557 |
+---------------------+------+------+------+------+------+------+

You can use mysqli::multi_query in your script to pull everything together like so:

$sql = <<<SQL
SET @sql = NULL;
SELECT
  GROUP_CONCAT(distinct
    CONCAT(
      'sum(case when agcid = ',
      agcid,
      ' then ooscount else 0 end) AS ''',
      replace(agcid, ' ', ''),
      ''''
    )
  ) INTO @sql
from (select distinct agcid from ooscount) a;

SET @sql = CONCAT('SELECT `date`, ', @sql, ' from ooscount where `date` >= DATE_SUB( NOW( ) , INTERVAL 7 HOUR ) group by `date`');
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SQL;

$db->multi_query($sql);

do {
        if ($result = $db->store_result()) {
                $results = [];
                while ($row = $result->fetch_assoc()) {
                        $results[] = $row;
                }
        }
} while ($db->more_results() && $db->next_result());

echo json_encode($results);

Both of these approaches will give you the desired dataProvider layout:

[{
    "date": "2018-03-01 18:00:02",
    "4": "2869",
    "2": "3183",
    "1": "4906",
    "3": "5596",
    "14": "6019",
    "16": "7564"
}, {
    "date": "2018-03-01 18:05:01",
    "4": "2870",
    "2": "3182",
    "1": "4899",
    "3": "5598",
    "14": "6018",
    "16": "7557"
}]