Creating Dashboards Using Google Charts. PHP, and MySQL Database (Subtitles Added)

Creating Dashboards Using Google Charts. PHP, and MySQL Database (Subtitles Added)


Welcome to My Channel and to the creating dashboard video. Open a Browser Start Google Search and type “Google Charts” Click the first link as developers.google.com/chart and open Google Charts. You will see the available properties and chart gallery. For example, when you select bar charts, you are gonna see available list of bar charts and related methods, CODES and properties that can be done with bar charts. As you see in the video, a lot of attributes of a chart or chart components can be customized such as colors, legends etc. As an another example, select pie chart from the list, you will see available options and relevant CODES to embed it into your development environment. You can get the codes and send it to JsFiddle (By the way I recommend you to have an account at jsfiddle.net.) If jsfidle is open, you can take these related codes in that medium. In the jsfiddle tooli there are seperate containers that define the codes (i.e., upper container) , chart related CODES (bottom container), and a chart object as My Daily Activities (right container). In the chart related CODE section, you see a title as “My Daily Activities”. You can change it to something else. You can change the activity categories and their values as well. And click Run Button to see the effects of your editing. Back to developers.google.com now. Copy the CODE section. Paste this CODE into DW (Dreamweaver) as a php file. Click on Live button for Front End view of the CODE. As in jsfidle, change some data values and click Reload button of built in browser of DW As figured out in the guide for google developers section, there are many OPTIONS that can be customized with respect to your preferences. As an example, we can change the title or colors of pies by adding “colors” attribute within “options” (Note that “blue” is written as “bleu” wrongly. Let us remove/modify some slices! Bleu!!! Corrected as Blue! Now, let us arrange the color of the background as yellow! In our project we created an html file named as “dashboard.html” and we have added various chart types by copying these CODES prepared before. As can be seen in the video and Google Development Guide, each chart has different specifications, attributes and parameters whereas the general framework is same. For example, stack type bar charts showing the proportions of categories horizontally, require four arguments as Utilization as Name, Used as proportion, Free as 1-proportion and role as role. Let us jump to phpMyAdmin now. You will see unique table for each chart. Each chart as a table, has its own data fields. Let us look at our bar chart and try to create related field and fill data in for each categories. We can arrange each category and their value dynamically instead of statically embedding into code part. We create a separate html file as “dashboard FINAL.php” to show the difference. For this dynamic setting, firstly we need to have a connection string to our database from the code part. Here we must specify servername, username and password, and name of database. It can be seen in the video. After having created a bridge to the database, it is time to retrieve the relevant data through simple SQL query sentence. After that point, foreach loop enables us to get all data and insert them into a pie chart like in the previous case before connecting this bridge to the database. The next part of this CODE for the bar chart is very similar to the CODE part for pie chart. This is the same for candle stick chart and other types of chart examples. After having filled all chart examples with data, we are closing the database connection. After setting charts and data arrangements, it is time to create our HTML page. We create our links to reference relevant libraries of google/jsapi. Next step is creating simple CODE parts of jquery Here we have similar code parts for other charts. Later, each chart is inserted into a separate cell in a table. Finally we terminate the php code section for HTML part. We have eventually finished all the structure. We can also update all charts anytime upon updating the database and refreshing front end view… Many thanks to Google for providing such an interactive development environment. We would also like to thank to Dreamweaver for providing us product demo version. Special thanks to phpMyAdmin and MySql developers and open source and free to use enablers.

Danny Hutson

62 thoughts on “Creating Dashboards Using Google Charts. PHP, and MySQL Database (Subtitles Added)

  1. This video is generated by Kacem Abdullah for demonstrationg some outputs of in-Semester studies of Decision Support Systems Design and Implementation PhD course.

  2. i had 2 problems the first was nowhere was JQuery integrated the second was this: data.addRows({$pie_chart_data}); there we are trying to use a php variable in javascript and it dind't worked for me here is the (for me ) working code for a pieChart made with a xampp server:

    <head>

    <?php

    $servername = 'localhost';

    $username = 'root';

    $password = '';

    $dbname = 'charts';

    $mysqli = new mysqli($servername, $username, $password, $dbname);
    if($mysqli->connect_error){
    die("Connection failed: ". $mysqli->connect_error);
    }

    $query = "select * from piechart";
    $qresult= $mysqli->query($query);
    $results = array();

    while ($res = $qresult->fetch_assoc()) {
    $results[] = $res;
    }

    $pie_chart_data = array();
    foreach ($results as $result) {
    $pie_chart_data[] = array($result['status'],(int)$result['num_patients']);

    }

    $pie_chart_data = json_encode($pie_chart_data);

    mysqli_free_result($qresult);

    mysqli_close($mysqli);

    ?>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

    google.load('visualization', '1.0',{'packages':['corechart'] } );

    google.setOnLoadCallback(drawChart);

    function drawChart(){

    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Age Range');
    data.addColumn('number', 'Number');
    data.addRows(<?php echo $pie_chart_data?>);

    var options = {
    title: ' '

    };

    var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));
    chart.draw(data, options);
    }

    jQuery(document).ready(function(){
    jQuery(window).resize(function(){
    drawChart();
    });
    });
    </script>
    </head>

    <body>

    <div id='pie_chart_div'> </div>
    </body>

  3. nice tutorial~! but the background music drove me sleepy within the first 5 min.. let me advice a more dynamic song given that there's no voice explaining/following the instructions.

  4. how to download dashboard that youve downloaded under your screen? im little bit confused when you open your application (i mean dreamwaver) and then there are so many chart. how can be like that? thankyou for respon sir.

  5. Please. Can you help me.
    I want to draw another air line, but I do not know where wrong, but could not draw chart.
    <?php
    $servername = 'localhost';

    $username = 'abc';

    $password = 'xxx';

    $dbname = 'abc';

    $mysqli = new mysqli($servername, $username, $password, $dbname);
    if($mysqli->connect_error){
    die("Connection failed: ". $mysqli->connect_error);
    }

    $query = "SELECT * FROM data_zigbee";
    $qresult= $mysqli->query($query);
    $results = array();

    while ($res = $qresult->fetch_assoc()) {
    $results[] = $res;
    }

    $line_chart_data = array();
    foreach ($results as $result) {
    $line_chart_data[] = array($result['timeupload'],$result['humidity'],$result['temperature']);

    }

    $line_chart_data = json_encode($line_chart_data);

    mysqli_free_result($qresult);

    mysqli_close($mysqli);

    ?>
    <head>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

    google.load('visualization', '1',{'packages':['corechart'] } );

    google.setOnLoadCallback(drawAnnotations);

    function drawAnnotations(){

    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Date');
    data.addColumn('number', 'Humidity');
    data.addColumn('number', 'Temperature');
    data.addRows(<?php echo $line_chart_data?>);

    var options = {
    width: 600,
    height: 400,
    legend: { position: 'top', maxLines: 3},
    line : {groupWidth: '75%'},

    isStacked: true

    };

    var chart = new google.visualization.LineChart(document.getElementById('line_chart_div'));
    chart.draw(data, options);
    }

    </script>

    </head>

    <body>a
    <div id='line_chart_div'> </div>

    </body>

  6. how did u link mysql data in ur html website?? you just had ready made databases and showed how to add vales(we all know that) show us how to link them

  7. Hi,This tutorial really helped me a lot  but I was unable to align those graphs to rightside all they were lying on the same side. Can you please help me regarding this??

Leave a Reply

Your email address will not be published. Required fields are marked *