Graph based on weekly data

Question

Graph based on weekly data

Navigation

#1 by (0 votes)

0

Colleagues programmers, good night. I'm having a hard time setting up a chart of weekly records using PHP, MySQL, and the Highcharts library.

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

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

I can not figure out how to mount this data on the backend side, especially the SQL query.

Edit: I forgot to put 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 .

Image:

php mysql highcharts

asked by anonymous 03.04.2017 / 22:55

1 answer

Pass variable Javascript POST does not work Doubt monitoring TomCat with Java VisualVM

score 0 · Answer 1

You must execute a SELECT by doing a count of connection, disconnection records, join them in separate columns and finally sum them, eg:

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)