php encode to json help with morris.js please

bambinou1980 · August 29, 2015

Hello,

I would like to populate my first morris.js graph from here:

http://morrisjs.github.io/morris.js/

But, I am not sure where to start, I understand that we can encode php in a way that we get a json output using json_encode()

Where I am lost is here.

I will have in mysql a columin with a date yyyy/mm/dd and another column with the "sales_values", let's say I pull those 2 columns with a SELECT as

$sql = "SELECT from orders

sales_values,

dates

dates BETWEEN yyyy/mm/dd AND NOW()

ORDER BY dates";

How to I know take those results to make them work so they can get applied to the below code from morris.js please?

new Morris.Line({
// ID of the element in which to draw the chart.
element: 'myfirstchart',
// Chart data records -- each entry in this array corresponds to a point on
// the chart.
data: [
{ year: '2008', value: 20 },
{ year: '2009', value: 10 },
{ year: '2010', value: 5 },
{ year: '2011', value: 5 },
{ year: '2012', value: 20 }
],
// The name of the data record attribute that contains x-values.
xkey: 'year',
// A list of names of data record attributes that contain y-values.
ykeys: ['value'],
// Labels for the ykeys -- will be displayed when you hover over the
// chart.
labels: ['Value']
});

Thank you,

Ben

Barand · August 29, 2015

$sql = "SELECT from orders

sales_values,

dates

dates BETWEEN yyyy/mm/dd AND NOW()

ORDER BY dates";

Your first task is create a select query with the correct syntax instead of the rubbish above. Look at the order of the statement parts in your other select queries that you have posted, and the use of important keywords, like WHERE.

When you process the query, store in an array then json_encode() the array.

What will you show on the graph when you have multiple values for the same date?

bambinou1980 · August 29, 2015

$sql = "SELECT 
sales_values,
dates
FROM orders
WHERE dates BETWEEN yyyy/mm/dd AND NOW()
ORDER BY dates";

Sorry about the sql statement, I had kids running around while I was writing the post and could not concentrate properly.....

On one single page I am wishing to show this as live graphs:

1 graph with the current month and daily sales(so this will correspond to days on the X axis and prices on the Y axis).

1 graph with the current week from Monday to Sunday and daily sales(so this will correspond to days on the X axis and prices on the Y axis).

1 graph with the current Year and Weekly sales(so this will correspond to weeks on the X axis and prices on the Y axis).

Thank you Barand,

Barand · August 29, 2015

When is "yyyy/mm/dd" ?

You probably want three queries, on for each graph. Store results in array with date as the key and sales as the value

1 graph with the current month and daily sales(so this will correspond to days on the X axis and prices on the Y axis).

SELECT
SUM(sales_values) as dailysales,
DAY(dates) as day
FROM orders
WHERE YEAR(dates) = YEAR(CURDATE()) AND MONTH(dates) = MONTH(CURDATE())
GROUP BY day
ORDER BY dates

2 graph with the current week from Monday to Sunday and daily sales(so this will correspond to days on the X axis and prices on the Y axis).

SELECT
SUM(sales_values) as dailysales,
WEEKDAY(dates) as day
FROM orders
WHERE YEARWEEK(dates) = YEARWEEK(CURDATE())
GROUP BY day
ORDER BY dates

3 graph with the current Year and Weekly sales(so this will correspond to weeks on the X axis and prices on the Y axis).

SELECT
SUM(sales_values) as weeklysales,
WEEK(dates) as weekno
FROM orders
WHERE YEAR(dates) = YEAR(CURDATE())
GROUP BY weekno
ORDER BY dates

Edited August 29, 2015 by Barand

bambinou1980 · August 29, 2015

Thank you so much Barand, I will try all of this now :-)

bambinou1980 · August 29, 2015

Ok, so here is where I am, based on my current table(Those are the real names as previously they were just samples). I am getting a good json encoded data but nothing is showing up in the graph:

<?php
$query = "SELECT
SUM(cust_order_total) as daily_gross_sales,
DAY(due_date) as day
FROM orders
WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE())
GROUP BY day
ORDER BY due_dates";


$rows = '';
$query = "SELECT cust_order_total,due_date FROM orders ORDER BY due_date";
$result = mysqli_query($connection,$query);
$total_rows = mysqli_num_rows($result);
if($result)
{
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
}  
?>

The json output is:

[{"cust_order_total":"112.50","due_date":"2015-08-27"},{"cust_order_total":"70.00","due_date":"2015-08-28"},{"cust_order_total":"75.00","due_date":"2015-08-28"},{"cust_order_total":"112.50","due_date":"2015-08-28"},{"cust_order_total":"41.25","due_date":"2015-08-28"},{"cust_order_total":"166.25","due_date":"2015-08-28"},{"cust_order_total":"105.00","due_date":"2015-08-28"},{"cust_order_total":"120.00","due_date":"2015-08-28"},{"cust_order_total":"95.00","due_date":"2015-08-28"},{"cust_order_total":"112.50","due_date":"2015-08-28"},{"cust_order_total":"40.00","due_date":"2015-08-28"}]

Now here is how I have added the data according to this example:

http://www.codediesel.com/visualization/display-line-and-area-charts-in-php-and-mysql/

The graph shows nothing at the moment, but it should show daily sales.

I am not understand 2 things, why the array still shows"cust_order_total" when we have an alias called "daily_gross_sales", and why don't we have at least 1 tiny result in the graph using the x and y names shown in the array, any idea please?

                    
       <!--Content-->
<script>
 
Morris.Line({
    // ID of the element in which to draw the chart.
    element: 'morris-line-chart',
 
    // Chart data records -- each entry in this array corresponds to a point
    // on the chart.
    data: <?php echo json_encode($rows);?>,
 
    // The name of the data record attribute that contains x-values.
    xkey: 'due_date',
 
    // A list of names of data record attributes that contain y-values.
    ykeys: ['cust_order_total'],
 
    // Labels for the ykeys -- will be displayed when you hover over the
    // chart.
    labels: ['Total Sales'],
 
    lineColors: ['#0b62a4'],
    xLabels: 'Days',
 
    // Disables line smoothing
    smooth: true,
    resize: true
});
</script>

Thank you!

Barand · August 29, 2015

the query you are executing is

$query = "SELECT cust_order_total,due_date FROM orders ORDER BY due_date";

bambinou1980 · August 29, 2015

Oups, my apology, I forgot to remove my sample....

Here is my new code:

<?php
$query = "SELECT
SUM(cust_order_total) as daily_gross_sales,
DAY(due_date) as day
FROM orders
WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE())
GROUP BY day
ORDER BY due_date";


   
$rows = '';
$result = mysqli_query($connection,$query);
$total_rows = mysqli_num_rows($result);
if($total_rows > 0)
{
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
    echo json_encode($rows);
}  
?>

json output

[{"daily_gross_sales":"112.50","day":"27"},{"daily_gross_sales":"937.50","day":"28"}]

<script>

Morris.Line({
    // ID of the element in which to draw the chart.
    element: 'morris-line-chart',

    // Chart data records -- each entry in this array corresponds to a point
    // on the chart.
    data: <?php echo json_encode($rows);?>,

    // The name of the data record attribute that contains x-values.
    xkey: 'day', <-----I also tried with ['day'] but it did not make a difference.

    // A list of names of data record attributes that contain y-values.
    ykeys: ['daily_gross_sales'],

    // Labels for the ykeys -- will be displayed when you hover over the
    // chart.
    labels: ['Total Sales'],

    lineColors: ['#0b62a4'],
    xLabels: 'Days',

    // Disables line smoothing
    smooth: true,
    resize: true
});
</script>

Barand · August 29, 2015

It looks like x values have to be dates or datetimes for Morris time series plots, day numbers don't work.

I think this should do it for the month chart

$query = "SELECT
        SUM(cust_order_total) as daily_gross_sales,
        due_date as day
        FROM orders
        WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE())
        GROUP BY due_date
        ORDER BY due_date";

$rows = '';
$result = mysqli_query($connection,$query);
$total_rows = mysqli_num_rows($result);

if($total_rows > 0)
{
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
    echo json_encode($rows);
}  
?>
<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>
<script type='text/javascript'>

$().ready(function() { 
    Morris.Line({
        // ID of the element in which to draw the chart.
        element: 'month_chart',
     
        // Chart data records -- each entry in this array corresponds to a point
        // on the chart.
        data: <?php echo json_encode($rows);?>,
     
        // The name of the data record attribute that contains x-values.
        xkey: 'day', 
     
        // A list of names of data record attributes that contain y-values.
        ykeys: ['daily_gross_sales'],
     
        // Labels for the ykeys -- will be displayed when you hover over the
        // chart.
        labels: ['Total Sales'],
     
        lineColors: ['#0b62a4'],
        xLabels: 'day',
        xLabelAngle: 45,
        // Disables line smoothing
        smooth: true,
        resize: true

    });
})
</script>
</head>
<body>
<div id='month_chart' style="height: 500px; width: 800px"></div>
</body>
</html>

bambinou1980 · August 31, 2015

Hi Barand,

Thank you so much, it seems to be working very well, quick question for you please, to rearrange the dates directly from mysql to the format yyyy/mm/dd to dd/mm/yyyy I tried this:

FROM ordersWHERE YEAR(due_date) = YEAR(date_format(CURDATE('%d/%m/%Y')))AND MONTH(due_date) = MONTH(date_format(CURDATE('%d/%m/%Y')))GROUP BY due_dateORDER BY day";

I have also tried this:

WHERE YEAR(due_date) = YEAR(CURDATE(date_format('%d/%m/%Y')))
 AND MONTH(due_date) = MONTH(CURDATE(date_format('%d/%m/%Y')))

But I am not getting anymore results now in the graphs, what is the best way of doing this please? I don't think it is possible to change the date format in the "mysqli_fetch_all" is it?

Thank you,

Ben

Edited August 31, 2015 by bambinou1980

Barand · August 31, 2015

Morris time series charts only accept the following formats for datetime values

2012
2012 Q1
2012 W1
2012-02
2012-02-24
2012-02-24 15:00
2012-02-24 15:00:00
2012-02-24 15:00:00.000

If you change them to dd/mm/yyyy in the query the x-axis dates get screwed (see attached)

Barand · August 31, 2015

P.S. you can provide a dateFormat attribute in the call to Morris.Line() but that only the affects the date displayed when hovering. Axis dates remain yyyy-mm-dd

eg

dateFormat: function (x) {
            var date = new Date(x);
            var day = date.getDate();
            var month = date.getMonth()+1;
            var year = date.getFullYear();
            return day + "/" + month + "/" + year;
        }

Barand · August 31, 2015

Google charts will accept the d/m/Y format

$query = "SELECT
        SUM(cust_order_total) as daily_gross_sales,
        date_format(due_date, '%d/%m/%Y') as day
        FROM orders
        WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE())
        GROUP BY due_date";

$rows = '';
$result = mysqli_query($db,$query);
$total_rows = mysqli_num_rows($result);
$data = [];
if($total_rows > 0)
{
    while (list($s, $d) = mysqli_fetch_row($result)) {
        $data[] = "['$d',$s]";
    }
} 
$mdata = join(',', $data);   
?>
<html>
<head>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
    google.load('visualization', '1.1', {packages: ['line']});
    google.setOnLoadCallback(drawChart);

    function drawChart() {

      var data = new google.visualization.DataTable();
      data.addColumn('string', 'Date');
      data.addColumn('number', 'Total Sales');

      data.addRows([<?=$mdata?>]);

      var options = {
        chart: {
          title: 'Current Month',
          subtitle: 'Daily sales'
        },
        width: 800,
        height: 250,
        axes: {
          x: {
            0: {side: 'top'}
          }
        }
      };

      var chart = new google.charts.Line(document.getElementById('month_chart'));

      chart.draw(data, options);
    }
  </script>
</head>
<body>
  <div id="month_chart"></div>
</body>
</html>

bambinou1980 · August 31, 2015

Thank you so much Barand, yes this is what happened to me, I kept getting the year 1900 when trying to convert directly from mysql.

A friend of mine told me to check the highcharts from here:

http://www.highcharts.com/demo

I will give it a go tonight on both highchart and google chart but I do find the highcharts graphics a little bit more sexy:-)

Thanks again for your great help!

I will open a new post if I struggle with the others.

gizmola · August 31, 2015

I've used highcharts in the past, and it's one of the best javascript graphing libraries available these days.

Barand · August 31, 2015

I hadn't heard of it before now. I had a quick look at the site and it does look impressive.

With their zoom option you would only need the annual chart. You could then use the zoom feature to display the last month and last week from that.

Sign In

php encode to json help with morris.js please

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information