Monday 27 March 2017

How to use Morris.js chart with PHP & Mysql



This post cover one of the advance topic like how to implement Mysql data with Morris.js Chart to make stylish chart by using PHP Script. In one of the our previous post in which we have seen how to use Google Chart Library with PHP and Mysql database. But now we have seen one more Chart library and how to use this Morris.js chart library with PHP and Mysql.

We have already know most of data in any web based application are comes from the sourced of database and based on that data we want required to display that data in chart or graph format on web page. So display of database data under graph or chart visualization on web page is the headache for most of the programmer and this task required lost of steps to display data in graph or chart format. But after coming of Morris.js charts, it makes this work more easier than previously which we have done for make chart from data. We have only make simple Mysql query and execute query and pass query result to Morris charts javascript and after this it will make chart as per our requirement. Morris.js Charts library is our best loved library for displaying data in charts format on web page.

In this post we will first make simple query and make data for Morris.js chart, based on that data we will make Line chart, Area chart, Bar chart and Bar chart with stacked option. For making this chart we have take data of last 10 years profit, purchase and sale data from Mysql table.




First we have load required cdn library link for use Morris.js chart with PHP and Mysql


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


After defining required CDN library link, we have create on division tag and we will display chart under this tag.


<div id="chart"></div>


For fetch data from table we have write php code for fetch data from Mysql table and arrange them that data in format which are allowed in Morris.js charts javascript library.


<?php 
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM account";
$result = mysqli_query($connect, $query);
$chart_data = '';
while($row = mysqli_fetch_array($result))
{
 $chart_data .= "{ year:'".$row["year"]."', profit:".$row["profit"].", purchase:".$row["purchase"].", sale:".$row["sale"]."}, ";
}
$chart_data = substr($chart_data, 0, -2);
?>


After fetching data from Mysql table now we have write javscript code for load Morris.js chart library for different chart like Line chart, Area chart, Bar chart and Bar chart with stacked option.


<script>
Morris.Bar({
 element : 'chart',
 data:[<?php echo $chart_data; ?>],
 xkey:'year',
 ykeys:['profit', 'purchase', 'sale'],
 labels:['Profit', 'Purchase', 'Sale'],
 hideHover:'auto',
 stacked:true
});
</script>


This is something new web tutorial make by Webslesson, with this post you can get source code of this tutorial and you can also get video tutorial on this topic also.

Source Code


index.php



<?php 
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM account";
$result = mysqli_query($connect, $query);
$chart_data = '';
while($row = mysqli_fetch_array($result))
{
 $chart_data .= "{ year:'".$row["year"]."', profit:".$row["profit"].", purchase:".$row["purchase"].", sale:".$row["sale"]."}, ";
}
$chart_data = substr($chart_data, 0, -2);
?>


<!DOCTYPE html>
<html>
 <head>
  <title>Webslesson Tutorial | How to use Morris.js chart with PHP & Mysql</title>
  <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>
  
 </head>
 <body>
  <br /><br />
  <div class="container" style="width:900px;">
   <h2 align="center">Morris.js chart with PHP & Mysql</h2>
   <h3 align="center">Last 10 Years Profit, Purchase and Sale Data</h3>   
   <br /><br />
   <div id="chart"></div>
  </div>
 </body>
</html>

<script>
Morris.Bar({
 element : 'chart',
 data:[<?php echo $chart_data; ?>],
 xkey:'year',
 ykeys:['profit', 'purchase', 'sale'],
 labels:['Profit', 'Purchase', 'Sale'],
 hideHover:'auto',
 stacked:true
});
</script>


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `account`
--

CREATE TABLE IF NOT EXISTS `account` (
  `id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `purchase` int(11) NOT NULL,
  `sale` int(11) NOT NULL,
  `profit` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `account`
--

INSERT INTO `account` (`id`, `year`, `purchase`, `sale`, `profit`) VALUES
(1, 2007, 550000, 800000, 250000),
(2, 2008, 678000, 1065000, 387000),
(3, 2009, 787000, 1278500, 491500),
(4, 2010, 895600, 1456000, 560400),
(5, 2011, 967150, 1675600, 708450),
(6, 2012, 1065850, 1701542, 635692),
(7, 2013, 1105600, 1895000, 789400),
(8, 2014, 1465000, 2256500, 791500),
(9, 2015, 1674500, 2530000, 855500),
(10, 2016, 2050000, 3160000, 1110000);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `account`
--
ALTER TABLE `account`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `account`
--
ALTER TABLE `account`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

19 comments:

  1. Nice Tutorial I have ever seen before

    ReplyDelete
  2. Nice video. This is what i have been looking for. Thumbs up

    ReplyDelete
  3. Why do I get this error?
    chart.php:24 Uncaught ReferenceError: sho11 is not defined

    ReplyDelete
  4. i cant fetch datetime column from mysql.
    Appreciate a lot for help.

    ReplyDelete
  5. it is a crazy example
    burn it!

    ReplyDelete
  6. thanksssssssssssssssssssssssssssssssss

    ReplyDelete
  7. Hi, I am getting error 'Expected semicolon ; after "],".'

    ReplyDelete
    Replies
    1. You're probably missing a ";" somewhere :-)

      Delete
  8. How to change x-keys to month?

    ReplyDelete
  9. i did not understand this one
    $chart_data = substr($chart_data, 0, -2);
    can you please explain

    ReplyDelete