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
)
);