Home PHP

How To Create Dynamic Pie Chart in PHP or JavaScript with MySQL?

how to create dynamic pie chart in php or javascript

Graphical or visual representation of data is usually a requirement for a software, mostly, business apps. Today I'm going to show you the two and free ways to generate dynamic Pie Charts for your web applications.

We say "dynamic" because the data that will be shown in the pie chart were pulled from a database which can be updated frequently.

You can also create other types of charts such as bar charts, line charts, and many other types of charts, but in this post, we will cover pie charts only which can give you a good idea on how to create dynamic charts.

In this post, we will cover the following topics:

1.0 Dummy Database for Dynamic Pie Chart
2.0 Using LibChart to Create Dynamic Charts (PHP)
3.0 Using the Google Chart Tools (JavaScript)

The first way I will give you is generating a chart using PHP, and in the second way, we will use JavaScript.

1.0 Dummy Database for Dynamic Pie Chart

This will be our example table and data.

--
-- Table structure for table `programming_languages`
--

CREATE TABLE IF NOT EXISTS `programming_languages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `ratings` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `programming_languages`
--

INSERT INTO `programming_languages` (`id`, `name`, `ratings`) VALUES
(1, 'C', 17),
(2, 'Java', 16),
(3, 'C++', 9),
(4, 'Objective-C', 9),
(5, 'C#', 7),
(6, 'Basic', 6),
(7, 'PHP', 5),
(8, 'Phyton', 3),
(9, 'Pearl', 2),
(10, 'Ruby', 1);

2.0 Using LibChart to Create Dynamic Charts (PHP)

We will do the first way using the LibChart, the simple and free PHP chart drawing library.

Here's a code:

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Pie Chart Demo (LibChart)- https://codeofaninja.com/</title>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-15" />
</head>
<body>

<?php
    //include the library
    include "libchart/libchart/classes/libchart.php";

    //new pie chart instance
    $chart = new PieChart( 500, 300 );

    //data set instance
    $dataSet = new XYDataSet();
   
    //actual data
    //get data from the database
   
    //include database connection
    include 'db_connect.php';

    //query all records from the database
    $query = "select * from programming_languages";

    //execute the query
    $result = $mysqli->query( $query );

    //get number of rows returned
    $num_results = $result->num_rows;

    if( $num_results > 0){
   
        while( $row = $result->fetch_assoc() ){
            extract($row);
            $dataSet->addPoint(new Point("{$name} {$ratings})", $ratings));
        }
   
        //finalize dataset
        $chart->setDataSet($dataSet);

        //set chart title
        $chart->setTitle("Tiobe Top Programming Languages for June 2012");
       
        //render as an image and store under "generated" folder
        $chart->render("generated/1.png");
   
        //pull the generated chart where it was stored
        echo "<img alt='Pie chart'  src='generated/1.png' style='border: 1px solid gray;'/>";
   
    }else{
        echo "No programming languages found in the database.";
    }
?>

</body>
</html>

Some advantage of using the LibChart includes: Free, it will work even offline or if you're just debugging on your localhost, easy to use and can be used for multiple data sets.

Some disadvantages are: It is limited to Line, Bar and Pie charts only and it works with PHP5 only.

3.0 Using the Google Chart Tools (JavaScript)

Google chart tools are powerful, simple to use, and also free.

Our index.php code:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
        <title>Pie Chart Demo (Google VAPI) - https://codeofaninja.com/</title>
    </head>
   
<body style="font-family: Arial;border: 0 none;">
    <!-- where the chart will be rendered -->
    <div id="visualization" style="width: 600px; height: 400px;"></div>

    <?php

    //include database connection
    include 'db_connect.php';

    //query all records from the database
    $query = "select * from programming_languages";

    //execute the query
    $result = $mysqli->query( $query );

    //get number of rows returned
    $num_results = $result->num_rows;

    if( $num_results > 0){

    ?>
        <!-- load api -->
        <script type="text/javascript" src="http://www.google.com/jsapi"></script>
       
        <script type="text/javascript">
            //load package
            google.load('visualization', '1', {packages: ['corechart']});
        </script>

        <script type="text/javascript">
            function drawVisualization() {
                // Create and populate the data table.
                var data = google.visualization.arrayToDataTable([
                    ['PL', 'Ratings'],
                    <?php
                    while( $row = $result->fetch_assoc() ){
                        extract($row);
                        echo "['{$name}', {$ratings}],";
                    }
                    ?>
                ]);

                // Create and draw the visualization.
                new google.visualization.PieChart(document.getElementById('visualization')).
                draw(data, {title:"Tiobe Top Programming Languages for June 2012"});
            }

            google.setOnLoadCallback(drawVisualization);
        </script>
    <?php

    }else{
        echo "No programming languages found in the database.";
    }
    ?>
   
</body>
</html>

The advantages of using Google chart tools include: Free, easy to use, multiple data sets, and has wide range of charts types that you can use, looks good and interactive.

I think the only disadvantage will be: you cannot use it when you don't have internet connection.

Download Source Code

You can download all the code used in this tutorial for only $9.99 $5.55!

THANK YOU!

has been added to your cart!

Powered by Easy Digital Downloads

Thank you!

have been added to your cart!

Powered by Easy Digital Downloads

How about you, do you have any other suggestions or solutions on how to create dynamic pie chart in PHP or JavaScript? Thanks for sharing it in the comments section below!

Home Facebook

Display Facebook Feed on Website Using PullFB Class

Update: Hi guys! I'm working on a new version of this code. Here's a sample LIVE DEMO. Let me know what you think! I'm going to post a new code and tutorial soon!

Hello guys! Today we are going to pull your Facebook page recent updates and display it to your website or to a webpage. This feature looks like a twitter widget (example is “My Twitter Updates” on the right sidebar of this site), but the posts were pulled from your Facebook page. This is good if you don’t want to use this Facebook Like Box social plugin and just want to show your page updates.

I updated the PullFB class a bit, to do this requirement. As I remember, this is one of the most requested feature, haha!
Our page_feeds.php code:

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″/>
        <title>The Code Of A Ninja – Pull Feeds From Facebook</title>
        <link href=”http://demo.codeofaninja.com/images/favicon.ico” rel=”SHORTCUT ICON”>
        <link rel=”stylesheet” type=”text/css” href=”css/style.css” />

    </head>
<body>

<div style=’font-size: 16px; font-weight: bold; margin: 0 0 10px 0;’>
    This demo is synchronized with this
    <a href=’https://www.facebook.com/CodeOfANinja’ target=’_blank’>
        Code of a Ninja Official Facebook Page Feed
    </a>
</div>

<?php
//change as you needed
date_default_timezone_set(‘Asia/Manila’);

echo “<p>Recent Updates from The Code of a Ninja</p>”;

//get page number
//if page num was not set, default to page 1
$page_num = isset( $_GET['page'] ) ? $_GET['page'] : 1;

//include our class
include ‘pull_fb.class.php’;

//pass appId, appSecret, and page number (anyway, we’re not using paging here)
$pull_fb = new PullFb( ‘change_to_your_app_id’, ‘change_to_your_app_secret’, $page_num );

//pass your fan page id
$feeds_result = $pull_fb->getFeeds( ’107786425949934′ );

//echo “<div>{$feeds_result->entries}</div>”;

foreach( $feeds_result->entries as $entries ){
    //just some time manipulation to suit my needs
    $ts = strtotime( “{$entries->published} +60 minutes” );

    echo “<div class=’object_item’ style=’text-align:left;’>”;
        //just for the sake of styling
        echo str_replace( “<br/><br/>”, “<br/>”, $entries->content );
        echo “<div class=’published_details’>”;
            echo “Published on “ . date( ‘m-d-Y H:i:s’, $ts );
            echo ” | <a href=’” . $entries->alternate . “‘ target=’_blank’>FB Link</a>”;
        echo “</div>”;
    echo “</div>”;

}
?>

  </body>
</html>

You can just copy and paste our updated PullFb class if you want. The code looks like this now:

<?php
require ‘fb-sdk/src/facebook.php’;

class PullFb{

    //set properties
    public $facebook;
    public $page_num;

    //number of items you want to see per page
    //you can add for other objects such as videos, etc.
    public $albums_per_page = 2;
    public $photos_per_page = 4;

    //if you want to exclude some albums like the Profile Pictures and Wall Photos, do something like:
    //$excluded_albums = “AND name <> ‘Profile Pictures’ AND name <> ‘Wall Photos’”;
    //in my example, I’m gonna exclude the wall photos only
    public $excluded_albums = “AND name <> ‘Wall Photos’”;

    //same with excluding photos, just state the pid
    public $excluded_photos = “AND pid <> ’221167777906963_1513599′ AND pid <> ’221167777906963_1513596′”;

    public function __construct( $appId, $secret, $page_num ){

        //create facebook instance
        $this->facebook = new Facebook(array(
          ‘appId’  => $appId,
          ‘secret’ => $secret,
          ‘cookie’ => true, // enable optional cookie support
        ));

        //for the page number
        $this->page_num = $page_num;
    }

    //get feeds in json format and then decode it using json_decode function
    public function getFeeds( $id ){
        $url = “https://www.facebook.com/feeds/page.php?id={$id}&format=json”;
        $feeds = json_decode( file_get_contents( $url ) );
        return $feeds;
    }

    //this will get facebook albums based on the $owner or fan page id
    public function getAlbums( $owner ){

        //we have to get the total number of albums first
        //i don’t know why the count function is not working
        $fql = “SELECT aid FROM album WHERE owner = {$owner} {$this->excluded_albums}“;

        //calculatePaging() will give us the paging settings
        //pass the fql and type of object
        $settings = $this->calculatePaging( $fql, ‘album’ );

        //get start and end limit for the next fql query
        $start_limit = $settings['start_limit'];
        $end_limit = $settings['end_limit'];

        //in this query we will include the paging based on the page number
        $fql = “SELECT
                    aid, object_id, owner, cover_pid, cover_object_id, name, created, modified,
                    description, location, size, link, visible, modified_major, edit_link,
                    type, can_upload, photo_count, video_count,
                    like_info, comment_info
                FROM
                    album
                WHERE
                    owner = {$owner} {$this->excluded_albums}
                LIMIT
                    {$start_limit}, {$end_limit}“;

        //set params
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get recordset
        $result = $this->facebook->api( $params );

        //we will include the settings to our result variable
        $result['pull_fb'] = $settings;

        return $result;
    }

    //this function was made for paging
    public function calculatePaging( $fql, $type ){

        //set the params based on passed fql
        //to count the total number of records
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get recordset
        $result = $this->facebook->api( $params );

        //get the total number of items
        $number_of_items = count( $result );

        //we will have the following calculations for the pagination
        //we just need some simple math

        //decide how many albums to show per page
        //values was actually declared as class property
        //you can easily add for other types of object
        if( $type == ‘album’ ){
            $items_per_page = $this->albums_per_page;
        }else if( $type == ‘photo’ ){
            $items_per_page = $this->photos_per_page;
        }

        //this is the current page
        $curr_page = $this->page_num;

        //previous page will be the current page MINUS one
        $prev_page = $curr_page - 1;

        //next page will be the current page PLUS one
        $next_page = $curr_page + 1;

        //no need to calculate for the first page, obviously, it’s 1
        //calculate last page
        $last_page = round( $number_of_items / $items_per_page );

        //detect if prev button will be visible
        if( $curr_page != 1 ){
            $prev_button = true;
        }

        //get $albums_shown value
        //it is the number of photos from the first page up to the current page
        $items_shown = $items_per_page * $curr_page;

        //detect if next button will be visible
        //if the $number_of_albums were still higher than the $albums_shown, show the next page button,
        //but if they are equal, don’t show the next page button
        if( $number_of_items > $items_shown ){
            $next_button = true;
        }

        //get start limit for the fql query
        $start_limit = $items_per_page * $prev_page;

        //get end limit
        //i’m not sure why i had to + 1, maybe it’s a facebook bug?
        $end_limit = $items_per_page + 1;

        //these are the values or settings returned
        //i made it to an array
        $settings = array(
            ‘number_of_items’ => $number_of_items,
            ‘prev_page’ => $prev_page,
            ‘next_page’ => $next_page,
            ‘prev_button’ => $prev_button,
            ‘next_button’ => $next_button,
            ‘start_limit’ => $start_limit,
            ‘end_limit’ => $end_limit,
            ‘last_page’ => $last_page
        );

        return $settings;
    }

    //to get photos of an album, we have to pass the album id
    public function getPhotos( $album_id ){

        //we have to get total number of photos first
        //i don’t know why the count function is not working
        $fql = “SELECT object_id FROM photo WHERE aid = ‘” . $album_id .“‘ ORDER BY position DESC”;

        //calculatePaging() will give us the paging settings
        //pass the fql and type of object, this is ‘photo’
        $settings = $this->calculatePaging( $fql, ‘photo’ );

        //get start and end limit for the next fql query
        $start_limit = $settings['start_limit'];
        $end_limit = $settings['end_limit'];

        //query the photos
        $fql = “SELECT
                        object_id, pid, src_small, src, src_big, link, caption, created, modified, position, like_info, comment_info
                    FROM
                        photo
                    WHERE
                        aid = ‘” . $album_id .“‘ {$this->excluded_photos}
                    ORDER BY
                        position DESC
                    LIMIT
                        {$start_limit}, {$end_limit}“;

        //set the parameters
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get photos recordset
        $result = $this->facebook->api( $params );

        //add the settings to the result
        $result['pull_fb'] = $settings;

        return $result;
    }

    //to get album cover, you need to pass the cover_id or id of the photo
    public function getAlbumCover( $cover_pid ){

        //get album cover query
        $fql = “select src_big from photo where pid = ‘” . $cover_pid . “‘”;

        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //run the query
        $result = $this->facebook->api( $params );

        //the the value and return it
        $value = $result[0]['src_big'];

        return $value;
    }

    //to get comments, you need to pass the object id, it can be a video, photo, album etc
    //check the fql tables to know object id https://developers.facebook.com/docs/reference/fql/
    public function getComments( $object_id ){

        //query the comment
        $fql = “SELECT
                text, time, fromid, likes
            FROM
                comment
            WHERE
                object_id = “ . $object_id;

        //set parameters
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get recordset
        $result = $this->facebook->api( $params );

        return $result;
    }

    //to get profile name, you need to pass fromid or the profile id
    public function getProfileName( $fromid ){
        //query commenter / profile name
        $fql = “SELECT
                name
            FROM
                profile
            WHERE
                id = “ . $fromid;

        //set the paramters
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get the resulting value
        $result = $this->facebook->api( $params );
        $value = $result[0]['name'];

        return $value;
    }

    //this time we will just get the profile name and profile thumbnail
    public function getProfileDetails( $fromid ){

        //select name and pic_square which can be used as profile thumbnail
        $fql = “SELECT
                name, pic_square
            FROM
                profile
            WHERE
                id = “ . $fromid;

        //set parameters
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get and return the result
        $result = $this->facebook->api( $params );

        return $result;
    }

    //to get album name, pass the album id
    public function getAlbumName( $aid ){

        //query album name
        $fql = “SELECT
                name
            FROM
                album
            WHERE
                aid = ‘{$aid}‘”;

        //set parameters
        $params = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //return result value for album name
        $result = $this->facebook->api( $params );
        $value = $result[0]['name'];

        return $value;
    }

    //to get events, pass the uid or you fan page id
    public function getEvents( $uid ){
        //query the events
        //we will eid, name, pic_big, start_time, end_time, location, description  this time
        //but there are other data that you can get on the event table (https://developers.facebook.com/docs/reference/fql/event/)
        //as you’ve noticed, we have TWO select statement here
        //since we can’t just do “WHERE creator = your_fan_page_id”.
        //only eid is indexable in the event table, so we have to retrieve
        //list of events by eids
        //and this was achieved by selecting all eid from
        //event_member table where the uid is the id of your fanpage.
        //*yes, you fanpage automatically becomes an event_member
        //once it creates an event
        $fql = “SELECT
                    eid, name, pic_big, start_time, end_time, location, description
                FROM
                    event
                WHERE
                    eid IN ( SELECT eid FROM event_member WHERE uid = {$uid} )
                ORDER BY start_time asc”;

        //set parameters
        $param = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get recordset and retur results
        $result = $this->facebook->api($param);
        return $result;
    }

    //this function will get the profile invited to the event
    //pass eid or event id
    public function getEventMembers( $eid ){

        //query the members
        $fql = “SELECT
                    eid, uid, rsvp_status
                FROM
                    event_member
                where eid = {$eid}“;

        //set params
        $param = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get result and return it
        $result = $this->facebook->api( $param );
        return $result;
    }

    //get the videos
    //pass the owner or the fan page id
    public function getVideos( $owner ){

        //query to get videos
        //specify you fan page id, I got 221167777906963
        //you can also use the LIMIT clause here if you want to show limited number of videos only
        $fql = “SELECT
                    vid, owner, title, description, thumbnail_link,
                    embed_html, updated_time, created_time, link
                FROM
                    video
                WHERE owner={$owner}“;

        //set parameters
        $param = array(
            ‘method’ => ‘fql.query’,
            ‘query’ => $fql,
            ‘callback’ => ”
        );

        //get recordset and return it
        $result = $this->facebook->api($param);
        return $result;
    }

}
?>

Thanks for reading this [post title here]!