Chart based on weekly data

Question

Fellow programmers, good evening. I’m having a hard time as build a chart of weekly records using PHP, Mysql and the Highcharts library.

I have records of connections and disconnections, which is saved in the format 2017-03-30 16:43:04, but in the line chart need to display this information in a 7-day period.

For example with the beginning of the week today: the axis of abscissas would contain 7 values 03/04, 04/04, 05/04, 06/04, 07/04, 08/04 and 09/04 and the ordinate axis would have integer values representing a certain amount of connection or disconnection.

I’m not getting to know how to mount this data on the backend side, mainly the SQL query.

Edit: I forgot to put the image and table data...

---

Table columns:

id | username | event | date

Where id is the event id, username the user name, Event can assume connection or disconnection values and date is the date in the format 2017-03-30 16:43:04.

Imagery:

Answer 1

you must execute a SELECT by making a count of connection records, disconnect records, join them in separate columns and finally add them, example:

mysql> select * from evento;
+----------+------------+------------+
| username | event      | date       |
+----------+------------+------------+
| joao     | conexao    | 2017-04-04 |
| joao     | desconexao | 2017-04-04 |
| maria    | conexao    | 2017-04-05 |
| maria    | desconexao | 2017-04-05 |
| joao     | conexao    | 2017-04-06 |
| joao     | desconexao | 2017-04-07 |
| ana      | conexao    | 2017-04-07 |
| ana      | desconexao | 2017-04-07 |
| pedro    | conexao    | 2017-04-05 |
| pedro    | desconexao | 2017-04-06 |
| carla    | conexao    | 2017-04-07 |
| carla    | desconexao | 2017-04-07 |
| felipe   | conexao    | 2017-04-07 |
| felipe   | desconexao | 2017-04-08 |
+----------+------------+------------+
14 rows in set (0,00 sec)

mysql> select x.date, sum(x.qtde_conexao) qtde_conexao, sum(x.qtde_desconexao) qtde_desconexao
    -> from (
    -> select date, count(1) qtde_conexao, 0 qtde_desconexao
    -> from evento
    -> where event='conexao'
    -> group by date
    -> union all
    -> select date, 0, count(1)
    -> from evento
    -> where event='desconexao'
    -> group by date) x
    -> group by x.date;
+------------+--------------+-----------------+
| date       | qtde_conexao | qtde_desconexao |
+------------+--------------+-----------------+
| 2017-04-04 |            1 |               1 |
| 2017-04-05 |            2 |               1 |
| 2017-04-06 |            1 |               1 |
| 2017-04-07 |            3 |               3 |
| 2017-04-08 |            0 |               1 |
+------------+--------------+-----------------+
5 rows in set (0,00 sec)