Google Chart with PHP and MySQL

Home » PHP & MySQL » PHP » Google Chart with PHP and MySQL
19 Jul, 2018 Categories: PHP


Database

Create a new MySQL database named demo and execute the SQL code below:

-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 03, 2016 at 06:55 AM
-- Server version: 5.7.9
-- PHP Version: 5.6.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `demo`
--

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `quantity` int(11) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `price`, `quantity`, `description`) VALUES
(1, 'Product 1', '100', 2, 'Description 1'),
(2, 'Product 2', '200', 3, 'Description 2'),
(3, 'Product 3', '300', 4, 'Description 3'),
(7, 'Product 4', '11', 2, 'vv'),
(8, 'Product 5', '33', 6, 'yy'),
(9, 'Product 6', '4444', 11, 'fff');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;




Database Connection

Create PHP file named connect.php. Use PDO connect to demo database with default account:


Username: root
Password:

<?php
	$conn = new PDO("mysql:host=localhost;dbname=demo", 'root', '');
?>

Create JSON Data

Create new PHP file named data.php. This file read data from product table into json data for displaying in chart

<?php
require_once 'database.php';
$stmt = $conn->prepare('select * from product');
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_OBJ);
echo json_encode($results);
?>

Display Chart

Create new PHP file named index.php. This file display google chart as below:

<html>
<head>
<title>Google Chart in PHP and MySQL</title>
<script
	src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<script type="text/javascript"
	src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript">
	$(document).ready(function() {

		$.ajax({
			url : "data.php",
			dataType : "JSON",
			success : function(result) {
				google.charts.load('current', {
					'packages' : [ 'corechart' ]
				});
				google.charts.setOnLoadCallback(function() {
					drawChart(result);
				});
			}
		});

		function drawChart(result) {

			var data = new google.visualization.DataTable();
			data.addColumn('string', 'Name');
			data.addColumn('number', 'Quantity');
			var dataArray = [];
			$.each(result, function(i, obj) {
				dataArray.push([ obj.name, parseInt(obj.quantity) ]);
			});

			data.addRows(dataArray);

			var piechart_options = {
				title : 'Pie Chart: How Much Products Sold By Last Night',
				width : 400,
				height : 300
			};
			var piechart = new google.visualization.PieChart(document
					.getElementById('piechart_div'));
			piechart.draw(data, piechart_options);

			var barchart_options = {
				title : 'Barchart: How Much Products Sold By Last Night',
				width : 400,
				height : 300,
				legend : 'none'
			};
			var barchart = new google.visualization.BarChart(document
					.getElementById('barchart_div'));
			barchart.draw(data, barchart_options);
		}

	});
</script>

</head>
<body>
<table class="columns">
	<tr>
		<td>
		<div id="piechart_div" style="border: 1px solid #ccc"></div>
		</td>
		<td>
		<div id="barchart_div" style="border: 1px solid #ccc"></div>
		</td>
	</tr>
</table>
</body>
</html>




Demo

References

I recommend you refer to the books below to learn more about the knowledge in this article:


Tags

Related Posts

 PHP CRUD with MySQL

October 10, 2018

Top