CODEWITHSUNDEEP
javascriptphpmysqljsonmorris.js
Celerium
Celerium

Reputation: 51

Feed PHP parsed JSON data from MySQL to Morris.js Chart

Diving into a new project and trying to teach myself JSON,PHP,MySQL and Morris.js.

Some background into what I am trying to achieve.

I have a PHP file that goes out to a webpage and grabs JSON data. (Right now I just have it set to grab everything). Then with the data that it grabs it puts it into a mysql database. I have set a Cron job up right now to run this file every 1 minute, so that I can get some data flowing into this database. 

<?php
//connect to mysql db
$con = mysql_connect('localhost','user','password',"") or die('Could not connect: ' . mysql_error());
//connect to the database
mysql_select_db('database', $con);

//read the json file contents
$jsondata = file_get_contents('http://192.168.10.243/j');

//convert json object to php associative array
$data = json_decode($jsondata, true);

//get the device details
$id = $data['data']['id'];
$type = $data['data']['type'];
$detector = $data['data']['detector'];
$cpm = $data['data']['cpm'];
$temperature = $data['data']['temperature'];
$uptime = $data['data']['uptime'];

//insert into mysql table
$sql = "INSERT INTO database_table_1(id, type, detector, cpm, temperature, uptime)
VALUES('$id', '$type', '$detector', '$cpm', '$temperature', '$uptime')";
if(!mysql_query($sql,$con))
{
    die('Error : ' . mysql_error());
} 
?>

After this, I then use PHP again to parse the information out of MySQL into a JSON array. Right now it will parse all the MySQL data it has (which I'm not sure if this is a good thing right now or if I should figure out a way to just parse the most recent data from MySQL.) Let me know what you think.

<?php
//open connection to mysql db
$connection = mysqli_connect('localhost','user','password','database_table_1') or die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "select * from database_table_1";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array
$emparray[] = array();
while($row =mysqli_fetch_assoc($result))
{
   array_push(
    $emparray,
    array(
            'a' => $row['timestamp'],
            'w' => $row['id'],
            'x' => $row['cpm'],
            'y' => $row['temperature'],
            'Z' => $row['uptime']
         )
    );
} 
// $emparray[] = $row;
echo json_encode($emparray);
//close the db connection
mysqli_close($connection); 
?>

Now if I was to run this PHP file by itself manually I would receive a lot of JSON data formatted just like this.

[{"a":"2015-08-17 21:34:01","w":"110000","x":"16","y":"28","Z":"112094"}]

Now my plan is to have this information update a chart or charts on a webpage using the morris.js charts. Here is my current index.html page with the PHP script and morris.js sections being near the bottom. 

<!DOCTYPE html>
<html lang="en">

<!-- morris.js dependencies -->
<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>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>Chart V0.1</title>

    <!-- Bootstrap Core CSS -->
    <link href="css/bootstrap.min.css" rel="stylesheet">

    <!-- Custom CSS -->
    <link href="css/simple-sidebar.css" rel="stylesheet">

    <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
        <script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
        <script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->

</head>

<body>

    <div id="wrapper">

    <!-- Sidebar -->
    <div id="sidebar-wrapper">
        <ul class="sidebar-nav">
            <li class="sidebar-brand">
                <a href="#">
                    Start Bootstrap
                </a>
            </li>
            <li>
                <a href="#">Dashboard</a>
            </li>
        </ul>
    </div>
    <!-- /#sidebar-wrapper -->

    <!-- Page Content -->
    <div id="page-content-wrapper">
  <div id="myfirstchart" style="height: 300px;"></div>
        <div class="container-fluid">
            <div class="row">
                <div class="col-lg-12">
                    <h1>Simple Sidebar</h1>
                    <p> This template has a responsive menu toggling system.</p>
                    <a href="#menu-toggle" class="btn btn-default" id="menu-toggle">Toggle Menu</a>
                </div>
            </div>
        </div>
    </div>
    <!-- /#page-content-wrapper -->

</div>
<!-- /#wrapper -->

<!-- PHP from Mysql to Json array -->
<?php
//open connection to mysql db
$connection = mysqli_connect('localhost','user','password','database_table_1') or die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "select * from database_table_1";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array
$emparray[] = array();
while($row =mysqli_fetch_assoc($result))
{
   array_push(
    $emparray,
    array( 
       'a' => $row['timestamp'],
       'w' => $row['id'],
       'x' => $row['cpm'],
       'y' => $row['temperature'],
       'Z' => $row['uptime']
         )
              );
 }

   // $emparray[] = $row;
   echo json_encode($emparray);

   //close the db connection
   mysqli_close($connection);
?>
<!-- Json Pull -->
<script>
     Morris.Area({
  // 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: <?php echo json_encode($emparray);?>, 
  // The name of the data record attribute that contains x-values.
  xkey: 'a',
  // A list of names of data record attributes that contain y-values.
  ykeys: ['x'],
  // Labels for the ykeys -- will be displayed when you hover over the chart.
  labels: ['x-test'] 
  });
</script>

<!-- jQuery -->
<script src="js/jquery.js"></script>

<!-- Bootstrap Core JavaScript -->
<script src="js/bootstrap.min.js"></script>

<!-- Menu Toggle Script -->
<script>
$("#menu-toggle").click(function(e) {
    e.preventDefault();
    $("#wrapper").toggleClass("toggled");
});
</script>

So far I have figure most of this new project, though I am currently stumped on how to take the data that I can parse from my PHP script and feed it to a morris.js chart or charts. I would like to have it so the chart or charts will update themselves every 1 minute for now and that the morris.js charts will get their data from my PHP script.

Any help, ideas, links, or best practices would help out a lot. My coding experience is little to none so I do apologize in advance.

Thank you

UPDATE:

I migrated the PHP script out of the HTML page and tried calling the PHP results via $.getJSON in the HTML page. Though I still am not able to get morris.js to use the parsed data. I dont even get a chart Any ideas?

    <!-- Ajax  -->
<script>
$.getJSON('export_php_test_1.php', function( data ){
     Morris.Area({
  // 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: data,  
  // The name of the data record attribute that contains x-values.
  xkey: 'a',
  // A list of names of data record attributes that contain y-values.
  ykeys: 'x',
  // Labels for the ykeys -- will be displayed when you hover over the chart.
  labels: 'x-test' 
  });
});
</script>

Upvotes: 1

Views: 3336

Answers (1)

Mamun Zaman
Mamun Zaman

Reputation: 41

You can use meta refresh. Following code will refresh the full html page automatically after 60 seconds.

<meta http-equiv="refresh" content="60">

If you want to refresh only the chart section, then you have to remove the inline php codes to a separate source, and use Ajax to fetch the data for morris. If you do so, then you can use JS function setInterval to run the Ajax in regular interval.

Upvotes: 2

Related Questions