Search code examples
phpmysqljsonchartsmorris.js

How to convert MySQL query to Morris chart readable json

I have a MySQL query that exports json data after some php intervention.

The json data is needed to activate a morris.js charts.

This is the code I have now:

$lp = array('35', '95', '96');

print '<script>$(document).ready(function() { var day_data = ';
foreach ($lp as $value) {
    $data_array = array();
    foreach ($this->dbh->query("
        SELECT c.datefield AS DATE, IFNULL(COUNT(l.insertDate),0) AS TASK
        FROM calendar c
        LEFT JOIN lead l ON ( DATE( l.insertDate ) = c.datefield )
            AND l.lpid =  '$value'
        GROUP BY DATE
        ORDER BY c.datefield DESC
        LIMIT 30
    ") as $row) {
         //$data_array .=  $row['TASK'];   
         //echo '{"date": "'.$row['DATE'].'", "page 1": '.$row['TASK'].', "page 2": 5},';

         array_push($data_array, $row['DATE'], $row['TASK']);
         //$data_array = array_merge($data_array, array('date' => $row['DATE'],'val' => $row['TASK']));
    }
    echo json_encode($data_array);      
}

What I want is to be able to see in the chart dynamic data for more than one id. this is the json I want to achieve:

{"date": "01-01-2017", "page 35": 3, "page 95": 5, "page 96": 5},
{"date": "02-01-2017", "page 35": 4, "page 95": 3, "page 96": 3},
{"date": "02-01-2017", "page 35": 9, "page 95": 5, "page 96": 4},
{"date": "03-01-2017", "page 35": 0, "page 95": 8, "page 96": 5};

Maybe a better suggestion is to export the json data by id and then combine it by date in the frontend.

Solution

  • Here is my full working code:

    <!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>
<meta charset=utf-8 />
<title>Morris.js Bar Chart For DavSev</title>   
</head>
<body>
<?php
$pdo=new PDO("mysql:host=host;dbname=DB;charset=utf8","username","password");
$params=array(35,95,96);  // lps list
$js_cols="'".implode("','",$params)."'";
$placeholders=array_fill(0,sizeof($params),'?');
$sql="SELECT C.datefield AS `date`,L.lpid AS `lp`,IFNULL(COUNT(L.insertDate),0) AS `task` 
      FROM `calendar` C 
      LEFT JOIN `lead` L ON DATE(L.insertDate)=C.datefield
      WHERE L.lpid IN (".implode(',',$placeholders).")
      GROUP BY `DATE`,L.lpid
      ORDER BY C.datefield,L.lpid;";
$stmt=$pdo->prepare($sql);
$stmt->execute($params);
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    if(!isset($data[$row["date"]])){
        $data[$row["date"]]=array_replace(array("D"=>$row["date"]),array_fill_keys($params,0));
    }
    $data[$row["date"]][$row["lp"]]=$row["task"];
}           
?>
<div id="bar-example"></div>
</body>
<script type="text/javascript">
Morris.Bar({
    element: 'bar-example',
    data: <?=json_encode(array_values($data))?>,  // shorthand echo
    xkey: 'D',
    ykeys: [<?=$js_cols?>],  // shorthand echo
    labels: [<?=$js_cols?>]  // shorthand echo
});
</script>
</html>
    • I changed your external script links.
    • I used pdo and a parameterized statement in case the lps are user input.
    • I changed your query to suit my needs. Please review and compare so that you understand the query changes.
    • I temporarily use date as keys to properly build the $data array.
    • I default all lp's values-per-date to zero, then overwrite it (last line of while loop) if the lp has a value on the date.
    • I use array_values() before json_encode() to strip the date-keys from the array.

    Rendered Output:

    enter image description here

    Source code Output:

    <!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>
<meta charset=utf-8 />
<title>Morris.js Bar Chart Example</title>  
</head>
<body>
<div id="bar-example"></div>
</body>
<script type="text/javascript">
Morris.Bar({
    element: 'bar-example',
    data: [{"D":"2017-04-09","35":0,"95":0,"96":"1"},{"D":"2017-04-10","35":0,"95":0,"96":"1"},{"D":"2017-04-11","35":"1","95":"4","96":"1"},{"D":"2017-04-12","35":0,"95":0,"96":"1"},{"D":"2017-04-15","35":"2","95":0,"96":"1"},{"D":"2017-04-16","35":0,"95":0,"96":"1"}],
    xkey: 'D',
    ykeys: ['35','95','96'],
    labels: ['35','95','96']
});
</script>
</html>

    I think I may still have the labels portion of the Morris.Bar function wrong, but I'll let you play with that if you wish. I've put in a fair amount of time on this.