Creating pie chart by taking values from the database

Question

my3h 0 Light Poster

11 Years Ago

Hi,

i want to create a pie chart in my website by taking values from database. I should display the top 10 most purchased product in the form of pie chart in my website. But i dont know how to do it. I have no clue how to start.

Please someone help me in this.

php

2 Contributors
8 Replies
3K Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by Biiim

All 8 Replies

Biiim 182 Junior Poster

11 Years Ago

can use the google charts api, which creates a very nice interactive graph but might be bit steep a gradient to understand it:
https://google-developers.appspot.com/chart/interactive/docs/gallery/piechart

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Task', 'Hours per Day'],
          ['Work',     11],
          ['Eat',      2],
          ['Commute',  2],
          ['Watch TV', 2],
          ['Sleep',    7]
        ]);

        var options = {
          title: 'My Daily Activities'
        };

        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

basically you want to output through php into javascript your top 10 data at this point:

 var data = google.visualization.arrayToDataTable([
          ['Task', 'Hours per Day']
          <?php 
          $i = 0;
          //start loop
          //while or foreach
          foreach($data as $v){
            echo ",['{$name}'],{$count}]\r\n";
          }
          ?>
        ]);

Biiim 182 Junior Poster

11 Years Ago

test prod sold table(didnt have yours)

create table `product_sold` (
    `transid` int (7),
    `prodid` int (5),
    `amtsold` int (4)
); 
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('1','1','2');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('2','1','1');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('3','2','8');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('4','3','3');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('5','4','5');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('6','5','12');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('7','6','4');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('8','7','3');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('9','8','8');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('10','9','8');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('11','10','2');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('12','2','4');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('13','3','12');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('14','4','1');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('15','5','5');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('16','6','2');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('17','7','32');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('18','8','5');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('19','9','18');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('20','10','4');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('21','4','1');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('22','5','14');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('23','8','34');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('24','9','3');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('25','6','12');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('26','1','21');
insert into `product_sold` (`transid`, `prodid`, `amtsold`) values('27','10','16');

Mysql query for the data:

SELECT p.product_id,ps.prodid,SUM(ps.amtsold) AS `total_sold` FROM product p LEFT JOIN product_sold ps ON p.product_id = ps.prodid
GROUP BY p.product_id
ORDER BY SUM(ps.amtsold) DESC
LIMIT 10;

php page that pulls the data and displays it:

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
         var data = google.visualization.arrayToDataTable([
          ['Prodid', 'Num Sold']
          <?php 
          $DB = mysqli_connect('localhost','user','pass','bim');
          $Q = "SELECT p.product_id,SUM(ps.amtsold) AS `total_sold` FROM product p LEFT JOIN product_sold ps ON p.product_id = ps.prodid
            GROUP BY p.product_id
            ORDER BY SUM(ps.amtsold) DESC
            LIMIT 10";
          $R = mysqli_query($DB,$Q);
          //start loop
          //while or foreach
          while($row = mysqli_fetch_assoc($R)){
            echo ",['{$row['product_id']}',{$row['total_sold']}]\r\n";
          }
          ?>
        ]);
        var options = {
          title: 'Top Sold Products'
        };
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

my3h 0 Light Poster · Answer 1 · 2012-07-11T12:22:08+00:00

Hi thanks for the replay. I went through this. But i did not understand how to use it. Actually i have top10 products in 1 column and its name , i have to display these two in pie. I dont know know how to relate to this with my data. Can u please explain me :(

my3h 0 Light Poster · Answer 2 · 2012-07-11T12:32:24+00:00

What i shopuld put in my foreach or while loop. please suggest me

Biiim 182 Junior Poster · Answer 3 · 2012-07-11T13:04:35+00:00

is the data in mysql? whats the table structure

my3h 0 Light Poster · Answer 4 · 2012-07-11T18:54:43+00:00

Yes. its in MySql database and the strusture is like this

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) COLLATE utf8_bin NOT NULL,
  `sku` varchar(64) COLLATE utf8_bin NOT NULL,
  `upc` varchar(12) COLLATE utf8_bin NOT NULL,
  `location` varchar(128) COLLATE utf8_bin NOT NULL,
  `quantity` int(4) NOT NULL DEFAULT '0',
  `stock_status_id` int(11) NOT NULL,
  `image` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `shipping` tinyint(1) NOT NULL DEFAULT '1',
  `shippingamount` varchar(50) COLLATE utf8_bin NOT NULL,
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `points` int(8) NOT NULL DEFAULT '0',
  `tax_class_id` int(11) NOT NULL,
  `date_available` date NOT NULL,
  `weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `weight_class_id` int(11) NOT NULL DEFAULT '0',
  `length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `length_class_id` int(11) NOT NULL DEFAULT '0',
  `subtract` tinyint(1) NOT NULL DEFAULT '1',
  `minimum` int(11) NOT NULL DEFAULT '1',
  `sort_order` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `viewed` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=56 ;

In this i want to display top 10 most viewed products in pie chart format.

my3h 0 Light Poster · Answer 5 · 2012-07-11T21:51:43+00:00

my3h 0 Light Poster

11 Years Ago

please help me...

my3h 0 Light Poster · Answer 6 · 2012-07-12T03:12:36+00:00

Here is my code, i tried like this. But nothing is getting displayed. I am not getting how to loop it

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Task', 'Hours per Day'],
          <?php 
          $stringResult;
          $i = 0;
          $sql = "select * from product inner join product_description on product.product_id = product_description.product_id order by product.viewed desc limit 10";
          $results1 = $this->db->query($sql);
          //for($i=0; $i<10; ++$i)
          {
          $stringResult .= "data[" .$i. "] = { label:" .$results['name'].", data: ". $results['quantity'] ."}";
           $i++;

         // foreach($data as $v){
           // echo ",['{$name}'],{$count}]\r\n";
            return $stringResult;
          }
          ?>
        ]);

        var options = {
          title: 'My Daily Activities'
        };

        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>

Suggest me where i am doing wrong and how to correct it.

Creating pie chart by taking values from the database

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers