基于每周数据的图表

Colleagues programmers, good night. I'm having a hard time putting together 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 the format 2017-03-30 16:43:04, but in the graph in lines I need to display this information in a period of 7 days.

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 axis of the Ordinates would have integer values representing a certain amount of connection or disconnection.

I do not know how to generate this data in the backend, especially the SQL query.

Table columns:

id | username | event | date

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

Chart example:

The ugly way I did:

Get 6 days ago + today in DD / MM format:

$date['timestamp'] = array( '0' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL - 6 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX, '1' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL - 5 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX, '2' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL - 4 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX, '3' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL - 3 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX, '4' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL - 2 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX, '5' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL - 1 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX, '6' => $this->db->select("DATE_ADD(CURDATE(), INTERVAL + 0 - WEEKDAY(CURDATE()) DAY) AS dateX")->get()->row()->dateX );

Get connection and disconnections based on the dates above:

$date['series'] = array( 'con' => array( '0' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 6 DAY')->get()->row()->dateX,
'1' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 5 DAY')->get()->row()->dateX, '2' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 4 DAY')->get()->row()->dateX, '3' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 3 DAY')->get()->row()->dateX, '4' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 2 DAY')->get()->row()->dateX, '5' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 1 DAY')->get()->row()->dateX, '6' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'conexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 0 DAY')->get()->row()->dateX
), 'dis' => array( '0' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 6 DAY')->get()->row()->dateX,
'1' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 5 DAY')->get()->row()->dateX, '2' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 4 DAY')->get()->row()->dateX, '3' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 3 DAY')->get()->row()->dateX, '4' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 2 DAY')->get()->row()->dateX, '5' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 1 DAY')->get()->row()->dateX, '6' => $this->db->select('COUNT(id_ConnectionLog) AS dateX')->from('connection_log')->where('cl_Event', 'desconexão')->where('cl_Client', $client_name)->where('date(cl_Date) = CURDATE() - INTERVAL 0 DAY')->get()->row()->dateX
) );