dongshiru5913 2017-04-03 23:11
浏览 27

基于每周数据的图表

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:

enter image description here

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

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 求差集那个函数有问题,有无佬可以解决
    • ¥15 【提问】基于Invest的水源涵养
    • ¥20 微信网友居然可以通过vx号找到我绑的手机号
    • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
    • ¥15 解riccati方程组
    • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
    • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
    • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
    • ¥50 树莓派安卓APK系统签名
    • ¥65 汇编语言除法溢出问题