Bayesean Blog - Desktop, Mobile and IOT Developer Blog


Morris Charts and PHP-PDO

Posted on 23rd Jan 2017 in Bootstrap 3


morrisjsheader.png 

Working with PHP PDO and MySQL, there comes a time when you require a Graphical Outcome of your stored data.

After researching the server-side graphical libraries, I could not identify an open-source library that outputted good performance including being fully responsive using Bootstrap 3/4.  So I went on to research the open source JavaScript available libraries. There are many really excellent libraries available that produce top class Graphics. Most libraries are not easy to link up with a database and will take a lot of data manipulation to produce an acceptable output.

I ended up choosing Morris.js library linked with the Raphael.js graphic library to which produces a really good Client professional output.

What I liked about Morris, is its easy output from a Json (string) input. So all it requires is a Json encoded output from the Database at the server then sent to the client-side web browser with the output.

Using the Source examples that I provide as a download, demonstrates how to easily populate Morris Charts dynamically with PDO-PHP using Json with little markup code required. The Source includes examples for a Bar chart, Line chart, Area chart and a Donut chart. 

Two methods are found in the source code.

           A Static method connecting directly using only Json static data.

           A dynamic MySQL data connection from the Database with php using a PDO Connection. This provides a simple solution of extracting data from the server and efficiently populating the JavaScript / JQuery graph in the browser using Json.

Getting Started

Here we assume that you are familiar with loading a backed up MySQL database, working with PHP, HTML, CSS, JavaScript and JQuery. If not then a google search will provide all the answers.

Download the file folder and extract the files. Extract the morris.sql file with PhpMyAdmin or similar into your MySQL database server. Run the index.php file in a XAMPP or WAMP stack. Full Source Code is provided includes Bootstrap 3 versioned files.

JSON JavaScript Object Notation, is syntax for storing and exchanging data. And is written in JavaScript Object Notation and is a lightweight data –interchanging format.

Morris requires the data in Json Text (string) format and does not need to parse it into a JavaScript Object using var JavaScript Object = JSON.parse(myJSONtext);

I noticed that many PDO-PHP to Morris solutions wrapped the Morris JavaScript into a foreach statement to process the information creating HUGE OVERHEAD. Just don’t do this as for every time the foreach statement is made the link between the Server and Client is created. 

The best solution is to convert the data into Json at the Server- Level (php).  Echo the Json string as a variable into the Morris’s JavaScript (Client) once, making it an efficient transfer of Server /Client data information with a quick graph uptime in the Client browser.

Using a PDO connection, PDO - php easily pulls the Server- Side data and using a built in features, convert the data to Json efficiently with minimal code.

The trick is to create the correct Aliases in the SQL statement when calling the Database. The result is a quick and easy way to parse the results into the required Json format to be sent to the Morris Chart.

Let work with the Donut Chart which requires the label and value for each input. We change the sql statement to reflect the ‘ip as label’ and ‘count(*) as value’. This will allow us to prepare and execute the PDO statement. PDO performs all the magic in FETCH_ASSOC using fetchAll.

<?php
$sql ="SELECT ip as label, count(*) as value from totalview GROUP by label ORDER BY value DESC LIMIT 5";
$stmt = $PDO->prepare($sql);
$stmt ->execute();
$data=$stmt->fetchAll(PDO::FETCH_ASSOC);
$phpobj =json_encode($data);
?> 

 

Once the data has been fetched we use the standard json_encode function. To transfer the data from the server to the browser and populate the donut chart , a JavaScript function is created.

 

<script language="JavaScript" type="text/javascript">
$(function() {
// Send the PHP Json_encoded data to a JavaScript Variable once.
var jsobj = <?php echo $phpobj; ?>;
// Donut Chart
Morris.Donut({
element: 'morris-donut-chart',
// populate the Morris Donut chart with the Json Data.
data:jsobj,
resize: true
});
});
</script>

 

Pulling the data from the Database requires low overhead. The conversion to Json is quick and easy and sending light- weight data from the Server does not slow the response in the browser.

 

Prerequisites

This project uses MorrisChart.js. The code uses PHP 5.6 and higher.

License

This project is licensed under the MIT license agreement. This means that you can use the code as you wish commercially or modify as needed.

Live View  Download Code




Reg      Commented   2 years ago Reply

I found in Stack Overflow that they use a foreach statement to pull the data. Your method is different. Which is the best method?

Admin      Replied    Moderator   2 years agoReply


Yes I am aware of the Stack Overflow method suggested. The problem is that we are dealing with server side and client side script. The foreach method updates the Client on every call. So if you has 500 rows of data, then you will be persistently updating the client 500 times and as it is a single threaded call, the client will simply stop working until the process is complete. Loading the data into Json in the Server creates a lightweight data object. This is only sent once to the client and the Json object (string) is quickly unpacked in the client allowing a quicker response. I hope that this clears up your concerns.

Add a Comment

9+5

Recent News

Delphi VCL Buttons in DBGrid
Two Helper Apps for Delphi LibUSB
Delphi Libusb Library Introduction
Delphi Object directly to a Json string in a REST Client
Delphi using Environment Variables in your App
Delphi FMX Leaflet Plotter using OSM Maps
C2PAS32 Convertor Application
C to Delphi Open Source Convertors Shootout

Categories

Bootstrap 4
Delphi VCL
Delphi FMX
Ajax
Bootstrap 3
CSS
XE4>Delphi > XE4
Delphi < XE4

Archives

October 2018

Delphi VCL Buttons in DBGrid
Two Helper Apps for Delphi LibUSB

September 2018

Delphi Libusb Library Introduction

August 2018

Delphi Object directly to a Json string in a REST Client
Delphi using Environment Variables in your App

July 2018

Delphi FMX Leaflet Plotter using OSM Maps

June 2018

C2PAS32 Convertor Application
C to Delphi Open Source Convertors Shootout
Delphi command-line programs with DOSCommand

May 2018

Delphi PDF Embedded viewer with PDF.js

March 2018

Delphi FMX - Changing TCharacter to TCharHelper
Make Your Delphi App POP using Javascript!

January 2018

Delphi FMX Dashboard using Chart.JS
Delphi FMX Form Docking

December 2017

PHP Slim REST Server & Delphi Auth Part 5

November 2017

Delphi FMX REST Client App Part 4

October 2017

Delphi VCL REST Pricing Client App Part 3

September 2017

Delphi REST VCL Client Basic Auth Part 2B

August 2017

Delphi REST Client Part 2A
PHP PDO REST Server Part 1

July 2017

PHP REST Server and Delphi Client Intro

June 2017

Delphi SQLite Encryptor-Decryptor Tool
Updating Applications Manifest using Delphi

May 2017

Create a Visual IP Address Geolocation with PHP

March 2017

PHP Downloader using Countdown timer
PHP File Downloader from a Inbox Selection

February 2017

Javascript Image-File Uploader with ThumbViewer

January 2017

Morris Charts and PHP-PDO

December 2016

CSS to create a functional Toggle Button