Creation of Charts with PHP, HTML5 and Javascript

2016

Creation of Charts with PHP, HTML5 and Javascript

Fernando Almeida, PhD.

This book offers a brief reference guide for web programmers and undergraduate students intends to implement dynamic and interactive charts in web based pages. This book is organized in 6 chapters and provides a brief reference how different type of graphs can be easily included in a HTML5 file. Some code extracts, related to the development of HTML5, Javascript, PHP and SQL scripts, are given and discusses along the book.

INESC TEC Campus da FEUP Rua Dr. Roberto Frias 4200 - 465 Porto Portugal Tel. +351 222 094 000 Fax +351 222 094 050

Creation of Charts with PHP, HTML5 and Javascript 2016 Table of Contents Index of Figures ............................................................................................................................. 4 Acronyms....................................................................................................................................... 5 Glossary ......................................................................................................................................... 6 1. Introduction............................................................................................................................... 7 1.1 Contextualization .................................................................................................................................. 7 1.2 Objectives .............................................................................................................................................. 7 1.3 Book Structure....................................................................................................................................... 8 2. Creation of Tables ................................................................................................................... 10 3. Populating Tables .................................................................................................................... 11 4. Code Structure ........................................................................................................................ 13 5. Chart Elements ........................................................................................................................ 14 6. Chart Types.............................................................................................................................. 15 6.1 Column Chart ...................................................................................................................................... 15 6.2 Line Chart ............................................................................................................................................ 17 6.3 Bar Chart.............................................................................................................................................. 21 6.4 Pie Chart .............................................................................................................................................. 24 6.5 Hybrid Chart ........................................................................................................................................ 25 Bibliography ................................................................................................................................ 29 Annex I - Script of "listemployees.php" ...................................................................................... 30 Annex II - Script of "listemployees.html" .................................................................................... 31 Annex III - Script of "showrevenue.php" ..................................................................................... 32 Annex IV - Script of "showrevenue.html" ................................................................................... 33 Annex V - Script of "showrevcashflow.php" ............................................................................... 34 Annex VI - Script of "showrevcashflow.html" ............................................................................. 35 Annex VII - Script of "listsectors.php" ......................................................................................... 36 Annex VIII - Script of "listsectors.html" ....................................................................................... 37

Page 2

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex IX - Script of "listcountries.php" ....................................................................................... 38 Annex X - Script of "listcountries.html"....................................................................................... 39 Annex XI - Script of "showempcashflow.php" ............................................................................ 40 Annex XII - Script of "showempcashflow.html" .......................................................................... 41

Page 3

Creation of Charts with PHP, HTML5 and Javascript 2016 Index of Figures Figure 1 - Inclusion of CanvasJS in a HTML file ............................................................................................. 13 Figure 2 - Overview of the structure of a chart ............................................................................................. 14 Figure 3 - JSON file created by listemployees.php ........................................................................................ 16 Figure 4 - Column chart of distribution of employees by year ..................................................................... 17 Figure 5 - JSON file created by showrevenue.php ........................................................................................ 18 Figure 6 - Line chart of revenue evolution .................................................................................................... 19 Figure 7 - JSON file created by showrevcashflow.php .................................................................................. 20 Figure 8 - Multi-series line chart of revenue and cash flow evolution ......................................................... 21 Figure 9 - JSON file created by listsectors.php .............................................................................................. 22 Figure 10 - Bar chart of distribution of companies by sector........................................................................ 23 Figure 11 - JSON file created by listcountries.php ........................................................................................ 24 Figure 12 - Pie chart of distribution of companies by country...................................................................... 25 Figure 13 - JSON file created by listempcashflow.php .................................................................................. 26 Figure 14 - Hybrid chart regarding the evolution of the number of employees and cashflow .................... 28

Page 4

Creation of Charts with PHP, HTML5 and Javascript 2016 Acronyms CSS - Cascading Style Sheets GPL - GNU General Public License JSON - JavaScript Object Notation MS - Microsoft SQL - Structured Query Language UML - Unified Modeling Language URL - Uniform Resource Locator WAMP - Windows/Apache/MySQL/PHP, Python, (and/or) PERL XML - Extensible Markup Language

Page 5

Creation of Charts with PHP, HTML5 and Javascript 2016 Glossary CanvasJS - it is an easy to use HTML5 and Javascript Charting library. It runs across devices including iPhone, iPad, Android, Windows Phone, Microsoft Surface, Desktops, etc. This allows you to create Rich Dashboards that work across devices without compromising on maintainability or functionality.

Cashflow - the net amount of cash and cash-equivalents moving into and out of a business.

CSS - it describes how HTML elements are to be displayed on screen, paper, or in other media.

HTML5 - latest version of Hypertext Markup Language, the code that describes web pages. It's actually three kinds of code: HTML, which provides the structure; Cascading Style Sheets (CSS), which take care of presentation; and JavaScript, which is responsible for functions execution.

Javascript - programming language used to make web pages interactive. It runs on your visitor's computer and doesn't require constant downloads from clients' website. JavaScript support is built right into all the major web browsers, including Internet Explorer, Firefox and Safari.

JSON - it is a way to store information in an organized, easy-to-access manner. In a nutshell, it gives us a human-readable collection of data that we can access in a really logical manner.

MySQL - open source relational database management system (RDBMS) based on Structured Query Language (SQL).

PHP - PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open source generalpurpose scripting language that is especially suited for web development and can be embedded into HTML. WampServer - it is a Windows web development environment. It allows you to create web applications with Apache, PHP and the MySQL database. It also comes with PHPMyAdmin to easily manage your databases. WampServer is an open source project, free to use (GPL licence).

Revenue - the amount of money that a company actually receives during a specific period, including discounts and deductions for returned merchandise.

XML - it is used to describe data. The XML standard is a flexible way to create information formats and electronically share structured data via the public Internet, as well as via corporate networks.

Page 6

Creation of Charts with PHP, HTML5 and Javascript 2016 1. Introduction 1.1 Contextualization The adoption of PHP, HTML5 and Javascript offers a technology ground for the creation of interactive and data-driven websites, by combining open source technologies and web standards.

PHP offers a simple way to embed dynamic activity in web pages. PHP is a flexible language that offers to developers a script language that, although not as fast as compiling the source code in C or similar languages, it is significantly fast and also integrates seamlessly with HTML markup.

Typically programmers need to change HTML output dynamically. For that we can used a relational database with structured querying. MySQL, beings free to use and installed on vast numbers of Internet web servers, is a great choice. It is robust and enough fast database management system.

Javascript was created to enable scripting access to all the elements of an HTML document. It provides a means for dynamic user interaction such as checking email address validity in input forms, without invoking the server side. Combined with CSS, Javascript is the power behind dynamic web pages that change in front of user rather than when a new page is returned by the server. However, and like referred

by Nixon (2014), Javascript can also be tricky to use, due to some major differences in the ways different browsers designers have chosen to implement it. This mainly came about when some manufacturers tried to put additional functionality into their browsers at the expense of compatibility with their rivals.

In order to guide students through the elementary syntax of SQL guide, a relational model is presented below.

Company (cod, name, country, sector) Account (id, year, revenue, cashflow, employees, cod->Company) This proposed relational model is normalized in 3NF and it is composed by four tables, which one identified by their primary keys (underlined) and the foreign keys are identified by "->" symbol.

The relational model describes generally a simple scenario that describes financial analysis of a company.

The financial analysis is performed for several years.

1.2 Objectives This mini books intends to provide a brief reference guide for undergraduate students that intend to implement dynamic and interactive charts based in PHP, HTML5 and Javascript languages. This mini book considers that students or developers have already basic knowledge about these three languages.

The scripts that will be shown in next chapters were testes using the following technological infrastructure:  WampServer 2.1;  PHP;  MySQL 5.1;  CanvasJS 1.8.

Page 7

Creation of Charts with PHP, HTML5 and Javascript 2016 1.3 Book Structure The book is organized in 6 chapters as follow:  Chapter 1 "Introduction" - gives a brief overview about PHP, HTML5 and Javascript languages and briefly presents the organization of the book;  Chapter 2 "Creation of Tables" - presents the script use for the creation of relational model;  Chapter 3 "Populating Tables" - presents the script used to populated tables created in previous chapter;  Chapter 4 "Code Structure" - gives a brief overview how the code is structured in order to use CanvasJS;  Chapter 5 "Chart Elements" - gives a concise overview about what are the main elements that describe the structure of a chart;  Chapter 6 "Chart Types" - details how each chart is implemented and how to connect the data between PHP, CanvasJS, HTML5 and MySQL;  "Bibliography" - presents the adopted bibliography for this book;  "Annex I - Script of listemployees.php" - presents the full script of PHP file responsible for extract information regarding the employees and generates the corresponding JSON file;  "Annex II - Script of listemployees.html - presents the full script of HTML file responsible for the presentation layer and implementation of javascript code;  "Annex III - Script of showrevenue.php" - presents the full script of PHP file responsible for extract information regarding the revenue and generates the corresponding JSON file;  "Annex IV - Script of showrevenue.html" - presents the full script of HTML file responsible for the presentation layer and implementation of javascript code;  "Annex V - Script of showrevcashflow.php" - presents the full script of PHP file responsible for extract information regarding the revenue/cashflow and generates the corresponding JSON file;  "Annex VI - Script of showrevcashflow.html" - presents the full script of HTML file responsible for the presentation layer and implementation of javascript code;  "Annex VII - Script of listsectors.php" - presents the full script of PHP file responsible for extract information regarding the sectors of each company and generates the corresponding JSON file;  "Annex VIII - Script of listsectors.html" - presents the full script of HTML file responsible for the presentation layer and implementation of javascript code;  "Annex IX - Script of listcountries.php" - presents the full script of PHP file responsible for extract information regarding the countries of each company and generates the corresponding JSON file;  "Annex X - Script of listcountries.html" - presents the full script of HTML file responsible for the presentation layer and implementation of javascript code;

Page 8

Creation of Charts with PHP, HTML5 and Javascript 2016  "Annex XI - Script of showempcashflow.php" - presents the full script of PHP file responsible for extract information regarding number of employees and cash flow, and finally generates the corresponding JSON file;  "Annex XII - Script of showempcashflow.html" - presents the full script of HTML file responsible for the presentation layer and implementation of javascript code;

Page 9

Creation of Charts with PHP, HTML5 and Javascript 2016 2. Creation of Tables The first step that need to be performed is the creation of the relational model in MySQL. In our example we have 2 tables to be created: Company and Account. We will start by the creation of the table Company.

The table Company has basic information regarding each company. The script for the creation of this table is given below.

CREATE TABLE Company ( cod char(3) primary key, name varchar(35), country varchar(25), sector varchar(25) ); The primary key of the Company table is its code. Each company has information regarding its name, country and activity sector. It is considered that each company is only placed in one country (each is typically not so common) and it is placed only in one activity sector. In real situations it is also common that a company could be included in several activity sectors.

Then, we will also create the table Account. The script is given below.

CREATE TABLE Account ( id int primary key, year int, revenue float, cashflow float, employees int, cod char(3), CONSTRAINT fk_Company FOREIGN KEY (cod) REFERENCES Company(cod) ); The primary key of the Account table is its id. Each account is associated to a different year and for each year we record the revenue, cash flow and number of employees. The revenue and cashflow are declared as float, because their values can contain decimal places. Finally, it is associated the cod of the company that is a foreign key.

Page 10

Creation of Charts with PHP, HTML5 and Javascript 2016 3. Populating Tables After we have declared the two tables (Company and Account) we need to populate the tables. For demonstration purposes we only have 21 records for Company table and 7 records for Account table. The Account table only records information for one company during seven years.

We start by populating the Company table. The script is given below.

Insert into Company values ('ABL', 'Albteim Laboratories', 'Australia', 'Health'); Insert into Company values ('ACY', 'ACY Manufacturing', 'USA', 'Manufacturing'); Insert into Company values ('ADP', 'Adelp Communications Corporation', 'USA', 'Communications'); Insert into Company values ('AMC', 'Amic Financial Corporation', 'USA', 'Financial'); Insert into Company values ('BRA', 'Bank of Real America Corporation', 'USA', 'Financial'); Insert into Company values ('BEE', 'Benchmark Electronics, Inc.', 'USA', 'Communications'); Insert into Company values ('BSC', 'Bethlehem Steel Corporation', 'UK', 'Manufacturing'); Insert into Company values ('BJS', 'BJs Wsale Club, Inc.', 'UK', 'Retail'); Insert into Company values ('CWS', 'CablevisionWide Systems Corp', 'UK', 'Communications'); Insert into Company values ('CGS', 'Caseys Gen Stores Inc.', 'USA', 'Retail'); Insert into Company values ('CHF', 'Champ Financial Corp.', 'USA', 'Financial'); Insert into Company values ('CHC', 'Charty Communications Inc.', 'USA', 'Communications'); Insert into Company values ('CCS', 'Circuit Cilly Stores', 'UK', 'Retail'); Insert into Company values ('CMD', 'Comdisk Inc.', 'UK', 'Communications'); Insert into Company values ('CMM', 'Commercial Metals Co', 'UK', 'Manufacturing'); Insert into Company values ('CPW', 'Compuware Corp.', 'UK', 'Information Technology'); Insert into Company values ('COO', 'Cooper Industries Ltd.', 'USA', 'Manufacturing'); Insert into Company values ('DKH', 'D&K Healthcare Resources', 'USA', 'Health'); Insert into Company values ('DCC', 'Dell Computer Corporation', 'USA', 'Information Technology'); Insert into Company values ('DST', 'DST Systems, Inc.', 'UK', 'Information Technology'); Insert into Company values ('EAA', 'Electronic Arts Inc.', 'USA', 'Information Technology'); Insert into Company values ('EMC', 'EMCCY Corporation', 'USA', 'Financial'); Insert into Company values ('FPS', 'FairProdutty Semiconductor International Inc.', 'UK', 'Manufacturing'); Insert into Company values ('FRI', 'Farmland Industries Inc.', 'UK', 'Manufacturing'); Insert into Company values ('FOB', 'Fornuty Brands Inc.', 'USA', 'Financial'); Insert into Company values ('GSG', 'Golky Samy Group Inc.', 'USA', 'Financial'); Insert into Company values ('GPF', 'GreenPoint Financial Corp.', 'USA', 'Financial'); Insert into Company values ('HHS', 'Hershey Foods Corp.', 'USA', 'Retail'); Insert into Company values ('HMU', 'Hwesth & Munchen', 'Germany', 'Communications'); Insert into Company values ('BRS', 'Brausweith', 'Germany', 'Retail'); Insert into Company values ('NMS', 'Norhaus & Misch', 'Germany', 'Retail'); It is important to highlight that we carefully check that each company code is different to guarantee the conditions for a primary key. Then we placed each company in different activity sectors. The data is not real and it is only used for demonstration purposes.

Then we populated the table Account. It is important to mention that the table Company must be populated before the Account table.

Insert into Account values (1, 2016, 45000, 2570, 32, 'ABL'); Insert into Account values (2, 2015, 42800, 1450, 21, 'ABL');

Page 11

Creation of Charts with PHP, HTML5 and Javascript 2016 Insert into Account values (3, 2014, 32000, 350, 18, 'ABL'); Insert into Account values (4, 2013, 27000, -560, 25, 'ABL'); Insert into Account values (5, 2012, 43000, 860, 22, 'ABL'); Insert into Account values (6, 2011, 96000, 4500, 21, 'ABL'); Insert into Account values (7, 2010, 25800, -1200, 7, 'ABL'); The first element used ("id") is the primary key. In fact, it is only an internal code. Then we present the revenue, cash flow and number of employees for each year. Finally, the last attribute is used to connect to Company table. In this example we only have data for 'Albteim Laboratories. Like in previous example all the data is simulated and used for demonstration purposes.

Page 12

Creation of Charts with PHP, HTML5 and Javascript 2016 4. Code Structure The first step that programmers should do is to download the CanvasJS from their website (http://canvasjs.com/docs/charts/intro/installation/). CanvasJS offers two source files:  Uncompressed file - canvasjs.js;  Compressed file - canvasjs.min.js The CanvasJS can be easily included in a web page like a common javascript file in the head section. An example how to do it is given in Figure 1.

Figure 1 - Inclusion of CanvasJS in a HTML file

In fact it is very easy to include CanvasJS in a HTML use and use it with static data. A description of this process is given in the CanvasJS website using the above url. However, the process of using CanvasJS with dynamic data from a MYSQL is not so easy and, therefore, we will focus our attention to this situation in this work.

In order to use CanvasJS with a relational database such as MySQL we will need to integrate two mandatory components:  PHP file - it is responsible to establish the connection and query the database. The extracted data from database should be processed in order to create a JSON file. The JSON is a good alternative for XML particularly due to its simplicity, extensibility, interoperability and openness (JSON, 2016);  HTML file - it is responsible to call the CanvasJS in head section (javascript code), define the properties of the chart and the visualization area of the chart.

Additionally, we can use other elements and technologies such as CSS. The CSS can be used to style web pages and to define how web pages should look when viewed in other media than a web browser.

CanvasJS uses canvas elements to render charts, which is part of HTML5 standard and allows for dynamic, scriptable rendering of shapes and bitmap images, supported in all modern browsers, such as Chrome, Firefox, Opera, IE, Safari, iPhone, iPad or Android.

The code is organized in order to be autonomous, which means that the process of creating the SQL connection string is replicated by all PHP files. In the context of a project it is highlight recommend to define an independent that is responsible for this operation.

Page 13

Creation of Charts with PHP, HTML5 and Javascript 2016 5. Chart Elements The elements of a chart can be different depending of the type of chart. However, there are several common elements that are shown in Figure 2.

Figure 2 - Overview of the structure of a chart

We briefly describe the elements shown in Figure 2:  Chart title - a chart title is used to describe the chart;  Legend - chart legend can be positioned in 8 different places;  Grid lines - it can be used to improve chart readability. The programmer can choose between horizontal and vertical grid lines;  Tool-tip - it displays additional information about a datapoint/dataseries upon hovering;  Label - labels that appear on an axis are either auto-generated or set by the user;  Axis - X&Y axis for chart. CanvasJS supports one X Axis and Two Y Axis (primary and secondary);  Axis Title - title of an axis. Each axis has its own title property;  Index label - it can be used to display additional information about a datapoint. Its orientation can be either horizontal or vertical;  Interlaced color - an alternating band of color that appears on the plot area.

Page 14

Creation of Charts with PHP, HTML5 and Javascript 2016 6. Chart Types CanvasJS supports a wide variety of chart types that are fully presented in its official Web page (http://canvasjs.com/docs/charts/chart-types/). In the context of this book we will describe the use of column charts, line charts, bar charts and pie charts.

6.1 Column Chart Column charts are rectangular bars with lengths proportional to the values that they represent. A column chart is useful to compare dataPoints in one or more dataSeries. Column charts in CanvasJS contains the following four properties: bevelEnabled, indexLabelPlacement, indexLabelorientation, fillOpacity.

In order to demonstrate the use of columns charts we will consider a situation where we want to present information regarding the number of employees for each year, considering only the "Albteim Laboratories" company. For that we used the SQL script below.

SELECT year, employees FROM Account Where cod='ABL' Order by year The script returns for each year the number of employee for "ABL" company. The elements are ordered by each year.

The PHP file (listemployees.php) is responsible to submit the query to the database and construct the JSON file. The syntax of this process is given below. $data_points = array(); $sql = "SELECT year, employees FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['employees']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); As first step we start by define the SQL query instruction. Then we fetch its result and we start to creating an array composed by two elements: year and employees. Finally we create a new JSON file based in the array.

The execution of the PHP file lets the system to create a new JSON File. Using the Google Chrome we can easily see its content, that is show in Figure 3.

Page 15

Creation of Charts with PHP, HTML5 and Javascript 2016

Figure 3 - JSON file created by listemployees.php

In the HTML file (listemployees.html) we create a new javascript function that is responsible to invoke the CanvasJS chart. The syntax of this process is given below. <script type="text/javascript"> $(document).ready(function () { $.getJSON("listemployees.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { animationEnabled: true, animationDuration: 5000, exportEnabled: true, title:{ text: "Number of employees by year" }, data: [ { type: "column", bevelEnabled: true, indexLabel: "{y}", indexLabelPlacement: "inside", dataPoints: result } ] }); chart.render(); }); }); </script> The function starts by reading the JSON file created by the execution of listemployees.php file.

After that, we create a new CanvasJS chart. Then we define that the export method is enable and we create a simple animation on it. Then we define the title of the chart and data elements. For data elements we define the type of chart, the indexLabel that will be used and if the bevel is enabled or not. The indexLabel will be placed inside each block.

The execution of the previous HTML file will display to the user the graph chart, as depicted in Figure 4.

Page 16

Creation of Charts with PHP, HTML5 and Javascript 2016

Figure 4 - Column chart of distribution of employees by year

6.2 Line Chart Line charts are type of charts which display information as a series of dataPoints connected by straight line segments. Each dataPoint has x variable determining the position on the horizontal axis and y variable determining the position of the vertical axis. Line charts in CanvasJS offers the following six properties: markerType, markerColor, markerBorderColor, markerSize, markerBorderTrickness and lineThickness.

In order to demonstrate the use of line charts we will consider a situation where we want to present information regarding the evolution of revenue along the years, considering only the "Albteim Laboratories" company. For that we used the SQL script below.

SELECT year, revenue FROM Account Where cod='ABL' Order by year The script returns the revenue of "ABL" company for each year. The revenue is ordered by each year.

The PHP file (showrevenue.php) is responsible to submit the query to the database and construct the JSON file. The syntax of this process is given below. $data_points = array(); $sql = "SELECT year, revenue FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['revenue']); array_push($data_points, $point); }

Page 17

Creation of Charts with PHP, HTML5 and Javascript 2016 echo json_encode($data_points, JSON_NUMERIC_CHECK); As first step we start by define the SQL query instruction. Then we fetch its result and we start to creating an array composed by two elements: year and revenue. Finally we create a new JSON file based in the array.

The execution of the PHP file lets the system to create a new JSON File. Using the Google Chrome we can easily see its content, that is show in Figure 5.

Figure 5 - JSON file created by showrevenue.php

In the HTML file (showrevenue.html) we create a new javascript function that is responsible to invoke the CanvasJS chart. The syntax of this process is given below. <script type="text/javascript"> $(document).ready(function () { $.getJSON("showrevenue.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Evolution of revenue" }, data: [ { type: "line", markerType: "cross", markerSize: 10, dataPoints: result } ] }); chart.render(); }); }); </script> The function starts by reading the JSON file created by the execution of showrevenue.php file.

After that, we create a new CanvasJS chart. Then we define the title of the chart and data elements. For data elements we define the type of chart, the marker type, its size and we associate the data points to the variable result.

The execution of the previous HTML file will display to the user the graph chart, as depicted in Figure 6.

Page 18

Creation of Charts with PHP, HTML5 and Javascript 2016

Figure 6 - Line chart of revenue evolution

It is also common the need of use multiple charts in the same page. In order to perform such operation we will need to slight adapt the SQL query and associated PHP and HTML files.

In order to demonstrate the use of multiple line charts we will consider a situation where we want to present information regarding the evolution of revenue and cash flow along the years, considering only the "Albteim Laboratories" company. For that we used the SQL script below.

SELECT year, revenue, cashflow FROM Account Where cod='ABL' Order by year The script returns the revenue and cash flow of "ABL" company for each year. All the data is ordered by year.

The PHP file (showrevcashflow.php) is responsible to submit the query to the database and construct the JSON file. The syntax of this process is given below. $data_points = array(); $sql = "SELECT year, revenue, cashflow FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['revenue'], "y2" => $row['cashflow']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); As first step we start by define the SQL query instruction. Then we fetch its result and we start to creating an array composed by three elements: year, revenue and cash flow. Finally we

Page 19

Creation of Charts with PHP, HTML5 and Javascript 2016 create a new JSON file based in the array.

The execution of the PHP file lets the system to create a new JSON File. Using the Google Chrome we can easily see its content, that is show in Figure 7.

Figure 7 - JSON file created by showrevcashflow.php

In the HTML file (showrevcashflow.html) we create a new javascript function that is responsible to invoke the CanvasJS chart. The syntax of this process is given below. <script type="text/javascript"> $(document).ready(function () { var dataPointsRevenue = []; var dataPointsCashFlow = []; $.getJSON("showrevcashflow.php", function (result) { for( var i = 0; i < result.length; i++) { dataPointsRevenue.push({ label: result[i].label, y: result[i].y }); dataPointsCashFlow.push({ label: result[i].label, y: result[i].y2 }); } var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Evolution of revenue and cashflow" }, axisY: { title: "Revenue" }, axisY2: { title: "Cashflow" }, data: [{ type: "line", markerType: "cross", markerSize: 10, dataPoints: dataPointsRevenue }, { type: "line", markerType: "triangle",

Page 20

Creation of Charts with PHP, HTML5 and Javascript 2016 markerSize: 10, dataPoints: dataPointsCashFlow }] }); chart.render(); }); }); </script> The function starts by reading the JSON file created by the execution of showrevcashflow.php file. For that, we create two arrays to put the information regarding revenue and cash flow.

After that, we can define the title and the other properties of the chat, such as type of the chart, marker type and size and the datapoints. We perform this operation for both series that are included in the same graph.

The execution of the previous HTML file will display to the user the graph chart, as depicted in Figure 8.

Figure 8 - Multi-series line chart of revenue and cash flow evolution

It is relevant to highlight that each offer has its own axis. The canvasJS automatically resizes the scale in order to let user properly visualize the graph. The programmer can also define the max and min values of each axis and its interval.

6.3 Bar Chart A bar chart is a chart with rectangular bars with lengths proportional to the values that they represent. A bar Chart is typically useful for comparing dataPoints in one or more dataSeries. Bar charts in CanvasJS contains the following four properties: bevelEnabled, indexLabelPlacement, indexLabelOrientation and fillOpacity.

In order to demonstrate the use of bar charts we will consider a situation where we want to present information regarding the activity sectors of each company. For that we used the SQL script below.

SELECT sector, count(*) as total_companies FROM Company Page 21

Creation of Charts with PHP, HTML5 and Javascript 2016 Group by sector Order by count(*) The script counts the number of companies by each activity sector. The returned elements are ordered.

The PHP file (listsectors.php) is responsible to submit the query to the database and construct the JSON file. The syntax of this process is given below. $data_points = array(); $sql = "SELECT sector, count(*) as total_companies FROM Company Group by sector order by count(*)"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['sector'] , "y" => $row['total_companies']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); As first step we start by define the SQL query instruction. Then we fetch its result and we start to creating an array composed by two elements: sectors and total of companies. Finally we create a new JSON file based in the array.

The execution of the PHP file lets the system to create a new JSON File. Using the Google Chrome we can easily see its content, that is show in Figure 9.

Figure 9 - JSON file created by listsectors.php

In the HTML file (listsectors.html) we create a new javascript function that is responsible to invoke the CanvasJS chart. The syntax of this process is given below. <script type="text/javascript"> $(document).ready(function () { $.getJSON("listsectors.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { animationEnabled: true, animationDuration: 5000, exportEnabled: true, title:{ text: "Companies by sector" Page 22

Creation of Charts with PHP, HTML5 and Javascript 2016 }, axisY: { title: "Number of companies", indexLabelPlacement: "outside", titleFontSize: 10, intervalType: "number", labelFontSize: 14 }, data: [ { type: "bar", indexLabel: "{y}", indexLabelPlacement: "outside", bevelEnabled: true, dataPoints: result } ] }); chart.render(); }); }); </script> The function starts by reading the JSON file created by the execution of listsectors.php file.

After that, we create a new CanvasJS chart. Then we define that the export method is enable and we create a simple animation on it. Then we define the title of the chart, axisy elements and data elements. For axisY we define its title, the label font and size. For data we define the type of chart, the indexLabel that will be used and if the bevel is enabled or not.

The execution of the previous HTML file will display to the user the graph chart, as depicted in Figure 10.

Figure 10 - Bar chart of distribution of companies by sector

Page 23

Creation of Charts with PHP, HTML5 and Javascript 2016 6.4 Pie Chart A pie chart is a circular chart divided into sectors, each sector (and consequently its central angle and area), is proportional to the quantity it represents. Together, the sectors create a full disk. Pie charts in CanvasJS contains the following four properties: exploded, startAngle, indexLabelLineColor and fillOpacity.

In order to demonstrate the use of pie charts we will consider a situation where we want to present information regarding the location of each company. For that we used the SQL script below.

SELECT country, count(*) as total_companies FROM Company Group by country The script counts the number of companies available in the database for each country.

The PHP file (listcountries.php) is responsible to submit the query to the database and construct the JSON file. The syntax of this process is given below. $data_points = array(); $sql = "SELECT country, count(*) as total_companies FROM Company Group by country"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['country'] , "y" => $row['total_companies']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); As first step we start by define the SQL query instruction. Then we fetch its result and we start to creating an array composed by two elements: countries and total of companies. Finally we create a new JSON file based in the array.

The execution of the PHP file lets the system to create a new JSON File. Using the Google Chrome we can easily see its content, that is show in Figure 11.

Figure 11 - JSON file created by listcountries.php

In the HTML file (listcountries.html) we create a new javascript function that is responsible to invoke the CanvasJS chart. The syntax of this process is given below. <script type="text/javascript"> $(document).ready(function () { $.getJSON("listcountries.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Companies by country"

Page 24

Creation of Charts with PHP, HTML5 and Javascript 2016 }, data: [ { type: "pie", exploded: true, toolTipContent: "{y} - #percent %", dataPoints: result } ] }); chart.render(); }); }); </script> The function starts by reading the JSON file created by the execution of listcountries.php file.

After that, we create a new CanvasJS chart. Then we define the title of the chart and the data elements: type of chart, if it explodes, tool tips conents and the datapoints that are associated to the result variable.

The execution of the previous HTML file will display to the user the graph chart, as depicted in Figure 12.

Figure 12 - Pie chart of distribution of companies by country

6.5 Hybrid Chart Hybrid charts can be made in CanvasJS by combining in a single graph representation several multi-series.

In order to demonstrate the use of hybrid charts we will consider a situation where we want to present a comparative analysis regarding the evolution of number of employees and cash flows along several years, considering only the "Albteim Laboratories" company. For that we used the SQL script below.

Page 25

Creation of Charts with PHP, HTML5 and Javascript 2016 SELECT year, employees, cashflow FROM Account Where cod='ABL' Order by year The script returns for each year the number of employee and cash flow for "ABL" company.

The elements are ordered by each year.

The PHP file (listempcashflow.php) is responsible to submit the query to the database and construct the JSON file. The syntax of this process is given below. $data_points = array(); $sql = "SELECT year, employees, cashflow FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['employees'], "y2" => $row['cashflow']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); As first step we start by define the SQL query instruction. Then we fetch its result and we start to creating an array composed by three elements: year, employees and cashflow. Finally we create a new JSON file based in the array.

The execution of the PHP file lets the system to create a new JSON File. Using the Google Chrome we can easily see its content, that is show in Figure 13.

Figure 13 - JSON file created by listempcashflow.php

In the HTML file (listempcashflow.html) we create a new javascript function that is responsible to invoke the CanvasJS chart. The syntax of this process is given below. <script type="text/javascript"> $(document).ready(function () { var dataPointsEmployees = []; var dataPointsCashFlow = []; Page 26

Creation of Charts with PHP, HTML5 and Javascript 2016 $.getJSON("showrevcashflow.php", function (result) { for( var i = 0; i < result.length; i++) { dataPointsEmployees.push({ label: result[i].label, y: result[i].y }); dataPointsCashFlow.push({ label: result[i].label, y: result[i].y2 }); } var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Evolution of the number of employees and cashflow" }, axisY: { title: "Employees" }, axisY2: { title: "Cashflow" }, data: [{ type: "column", fillOpacity: .3, axisYType: "secondary", dataPoints: dataPointsEmployees }, { type: "area", dataPoints: dataPointsCashFlow }] }); chart.render(); }); }); </script> The function starts by reading the JSON file created by the execution of showempcashflow.php file. For that, we create two arrays to put the information regarding number of employees and cash flow. After that, we can define the title and the other properties of the chat, such as type of the chart, behavior of each axis and the fill opacity that might be important when we combine multi-series elements.

The execution of the previous HTML file will display to the user the graph chart, as depicted in Figure 14.

Page 27

Creation of Charts with PHP, HTML5 and Javascript 2016

Figure 14 - Hybrid chart regarding the evolution of the number of employees and cashflow

Page 28

Creation of Charts with PHP, HTML5 and Javascript 2016 Bibliography Cash Flow. (n.d.).

Retrieved 02 http://www.investopedia.com/terms/c/cashflow.asp

17,

2016,

Chapman, S. (n.d.). What Is JavaScript? Retrieved http://javascript.about.com/od/reference/p/javascript.htm CSS Introduction. (n.d.).

Retrieved http://www.w3schools.com/css/css_intro.asp

01

21,

02

17,

2016,

from

2016,

Investopedia:

from

aboutTech:

from

W3Schools:

HTML5 JavaScript Charts. (n.d.). Retrieved 01 06, 2016, from CanvasJS: http://canvasjs.com JSON: The Fat-Free Alternative to XML. (n.d.). Retrieved 02 03, 2016, from http://www.json.org/xml.html Lengstorf, J. (n.d.). JSON: What It Is, How It Works, & How to Use It. Retrieved 02 11, 2016, from Coper Labs: http://www.copterlabs.com/json-what-it-is-how-it-works-how-to-use-it/ Marshall, G. (2011, 12 13). HTML5: what is it? Retrieved 01 23, 2016, from Techradar: http://www.techradar.com/news/internet/web/html5-what-is-it-1047393 Nixon, R. (2014). Learning PHP, MYSQL, Javascript, CSS & HTML5. Sebastopol: O'Reilly Media.

Revenue. (n.d.).

Retrieved 02 http://www.investopedia.com/terms/r/revenue.asp

17,

2016,

from

Investopedia:

What is PHP? (n.d.). Retrieved 01 15, 2016, from http://php.net/manual/en/intro-whatis.php What is Wamp? (n.d.). Retrieved 01 11, 2016, from http://myphpscriptz.com/php-scripts-tutorials/whatis-wamp-and-how-to-install-and-use-it/

Page 29

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex I - Script of "listemployees.php" <?php //header('Content-Type: application/json'); $servername = 'localhost:3306'; $username = 'root'; $password = ''; $dbname = "book"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $data_points = array(); $sql = "SELECT year, employees FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['employees']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); } $conn->close(); ?>

Page 30

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex II - Script of "listemployees.html" <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title></title> <script src="content/jquery.canvasjs.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function () { $.getJSON("listemployees.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { animationEnabled: true, animationDuration: 5000, exportEnabled: true, title:{ text: "Number of employees by year" }, data: [ { type: "column", bevelEnabled: true, indexLabel: "{y}", indexLabelPlacement: "inside", dataPoints: result } ] }); chart.render(); }); }); </script> </head> <body> <div id="chartContainer" style="height: 300px; width: 50%;"></div> </body> </html>

Page 31

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex III - Script of "showrevenue.php" <?php //header('Content-Type: application/json'); $servername = 'localhost:3306'; $username = 'root'; $password = ''; $dbname = "book"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $data_points = array(); $sql = "SELECT year, revenue FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['revenue']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); } $conn->close(); ?>

Page 32

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex IV - Script of "showrevenue.html" <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title></title> <script src="content/jquery.canvasjs.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function () { $.getJSON("showrevenue.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Evolution of revenue" }, data: [ { type: "line", markerType: "cross", markerSize: 10, dataPoints: result } ] }); chart.render(); }); }); </script> </head> <body> <div id="chartContainer" style="height: 300px; width: 50%;"></div> </body> </html>

Page 33

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex V - Script of "showrevcashflow.php" <?php //header('Content-Type: application/json'); $servername = 'localhost:3306'; $username = 'root'; $password = ''; $dbname = "book"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $data_points = array(); $sql = "SELECT year, revenue, cashflow FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['revenue'], "y2" => $row['cashflow']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); } $conn->close(); ?>

Page 34

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex VI - Script of "showrevcashflow.html" <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title></title> <script src="content/jquery.canvasjs.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function () { var dataPointsRevenue = []; var dataPointsCashFlow = []; $.getJSON("showrevcashflow.php", function (result) { for( var i = 0; i < result.length; i++) { dataPointsRevenue.push({ label: result[i].label, y: result[i].y }); dataPointsCashFlow.push({ label: result[i].label, y: result[i].y2 }); } var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Evolution of revenue and cashflow" }, axisY: { title: "Revenue" }, axisY2: { title: "Cashflow" }, data: [{ type: "line", markerType: "cross", markerSize: 10, dataPoints: dataPointsRevenue }, { type: "line", markerType: "triangle", axisYType: "secondary", markerSize: 10, dataPoints: dataPointsCashFlow }] }); chart.render(); }); }); </script> </head> <body> <div id="chartContainer" style="height: 300px; width: 50%;"></div> </body> </html>

Page 35

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex VII - Script of "listsectors.php" <?php //header('Content-Type: application/json'); $servername = 'localhost:3306'; $username = 'root'; $password = ''; $dbname = "book"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $data_points = array(); $sql = "SELECT sector, count(*) as total_companies FROM Company Group by sector order by count(*)"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['sector'] , "y" => $row['total_companies']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); } $conn->close(); ?>

Page 36

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex VIII - Script of "listsectors.html" <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title></title> <script src="content/jquery.canvasjs.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function () { $.getJSON("listsectors.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { animationEnabled: true, animationDuration: 5000, exportEnabled: true, title:{ text: "Companies by sector" }, axisY: { title: "Number of companies", indexLabelPlacement: "outside", titleFontSize: 10, intervalType: "number", labelFontSize: 14 }, data: [ { type: "bar", indexLabel: "{y}", indexLabelPlacement: "outside", bevelEnabled: true, dataPoints: result } ] }); chart.render(); }); }); </script> </head> <body> <div id="chartContainer" style="height: 300px; width: 50%;"></div> </body> </html>

Page 37

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex IX - Script of "listcountries.php" <?php //header('Content-Type: application/json'); $servername = 'localhost:3306'; $username = 'root'; $password = ''; $dbname = "book"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $data_points = array(); $sql = "SELECT country, count(*) as total_companies FROM Company Group by country"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['country'] , "y" => $row['total_companies']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); } $conn->close(); ?>

Page 38

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex X - Script of "listcountries.html" <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title></title> <script src="content/jquery.canvasjs.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function () { $.getJSON("listcountries.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Companies by country" }, data: [ { type: "pie", exploded: true, toolTipContent: "{y} - #percent %", dataPoints: result } ] }); chart.render(); }); }); </script> </head> <body> <div id="chartContainer" style="height: 300px; width: 50%;"></div> </body> </html>

Page 39

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex XI - Script of "showempcashflow.php" <?php //header('Content-Type: application/json'); $servername = 'localhost:3306'; $username = 'root'; $password = ''; $dbname = "book"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } else { $data_points = array(); $sql = "SELECT year, employees, cashflow FROM Account Where cod='ABL' order by year"; $result = $conn->query($sql); while($row = mysqli_fetch_array($result)) { $point = array("label" => $row['year'] , "y" => $row['employees'], "y2" => $row['cashflow']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); } $conn->close(); ?>

Page 40

Creation of Charts with PHP, HTML5 and Javascript 2016 Annex XII - Script of "showempcashflow.html" <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title></title> <script src="content/jquery.canvasjs.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function () { var dataPointsEmployees = []; var dataPointsCashFlow = []; $.getJSON("showrevcashflow.php", function (result) { for( var i = 0; i < result.length; i++) { dataPointsEmployees.push({ label: result[i].label, y: result[i].y }); dataPointsCashFlow.push({ label: result[i].label, y: result[i].y2 }); } var chart = new CanvasJS.Chart("chartContainer", { title:{ text: "Evolution of the number of employees and cashflow" }, axisY: { title: "Employees" }, axisY2: { title: "Cashflow" }, data: [{ type: "column", fillOpacity: .3, axisYType: "secondary", dataPoints: dataPointsEmployees }, { type: "area", dataPoints: dataPointsCashFlow }] }); chart.render(); }); }); </script> </head> <body> <div id="chartContainer" style="height: 300px; width: 50%;"></div> </body> </html>

Page 41