Home      Products & Services      Contact Us      Links

WebHatchers will design & develop your site for you.

Website Menu Heaven: menus, buttons, etc.

Send us your questions.

site search by freefind

SEO, Google, Privacy
   and Anonymity
Browser Insanity
Popups and Tooltips
Free Website Search
HTML Form Creator
Buttons and Menus
Image Uploading
Website Poll
IM and Texting
   or Not MySQL
Personal Status Boards
Content Management
Article Content
   Management Systems
Website Directory
   CMS Systems
Photo Gallery CMS
Forum CMS
Blog CMS
Customer Records
   Management CMS
Address Book CMS
Private Messaging CMS
Chat Room CMS
JavaScript Charts
   and Graphs

Free Personal Status Boards (PSB™)

Free Standard Free PSB

Free PSB Pro Version

Free Social PSB

Free Social PSB Plus (with Email)

Free Business PSB

Free Business PSB Plus (with Email)

PSB demo

Social PSB demo

Business PSB demo

So what's all this PSB stuff about?

Chart comparing business status boards

PSB hosting diagram

PSB Licence Agreement

Copyright © 2002 -
MCS Investments, Inc. sitemap

PSBs, social networking, social evolution, microcommunities, personal status boards
PSBs, social networking, business personal status boards
website design, ecommerce solutions
website menus, buttons, image rotators
Ez-Architect, home design software
the magic carpet and the cement wall, children's adventure book
the squirrel valley railroad, model railroad videos, model train dvds
the deep rock railroad, model railroad videos, model train dvds

Make Pie Chart from MySQL Table Data

For line drawing in this pie chart creation program, we chose to use Bresenham's Line Algorithm, which is here Make Anti-alias (Almost) Lines Using Bresenham's Line Algorithm) on our site. But we could have used Make Anti-alias (Almost) Lines instead.

JavaScript Charts, Graphs, Graphics, Circles, Ellipses, Arcs, Lines, and Polygons
Grab and Drop, Not Drag and Drop
Add Ids and onClicks to Divs
Add Ids and onClicks and Grab and Drop to Divs
Make Anti-alias (Almost) Lines
Make Anti-alias (Almost) Lines Using Bresenham's Line Algorithm
Good JavaScript Circle Algorithm
Good JavaScript Ellipse Algorithm
Good JavaScript Arc Algorithm
Make JavaScript Irregular Polygon
JavaScript Area of Irregular Polygon Algorithm
Make Line Chart from User-Inputted Data
Make Line Chart from CSV Data
Make Line Chart from MySQL Table Data
Make Bar Chart from User-Inputted Data
Make Bar Chart from CSV Data
Make Bar Chart from MySQL Table Data
Make Pie Chart from User-Inputted Data
Make Pie Chart from CSV Data
Make Pie Chart from MySQL Table Data

Pie Chart made from MySQL Table Data

There are endless reasons to draw lines on browser screens. Our reason was to create pie charts from MySQL table data although elsewhere we made a pie chart with CSV data, and we also used user-inputted data to create pie charts: Make Pie Chart from User-Inputted Data. We used the Bresenham's Line Algorithm for the line drawing in our pie charts.

When considering line drawing algorithms for the pie chart, we chose to avoid jQuery and HTML5's Canvas since the former is unneeded and the latter has unsatisfactory support from browsers, and it's more fun just programming in straight JavaScript. There are JavaScript libraries around that have line routines, but you do not learn programming by cheating! And the same can be said for using filters and transforms which each browser does their own way—there's no standard—so this requires several different functions plus browser or object or function sniffing and IE dumped support for one method and supported another with little warning. (Note: even though you can use the PHP GD library functions to draw lines on images, since the line may cover the screen diagonally, this may mean creating an image as big as the screen, using up RAM memory like a wild man. Even though PHP programmers use the imagedestroy() function to clear the memory BEFORE the script ends, what if image creation itself runs you out of memory? If not, the imagedestroy() function is useful to keep memory usage DURING the script to an acceptable level.)

Data for pie charts can come from any number of sources. We considered putting together a user input form and the numeric data could be charted and the non-numeric data turned into labels, and we did that here: Make Pie Chart from User-Inputted Data. But we decided to also interface with the two most important database sources needing charting: CSV files and MySQL tables. CSV is a delimited data format that has fields/columns separated by the comma character and records/rows terminated by newlines, and MySQL is the most popular open-source database ever. The first, CSV, can be pulled out of any competant database app, for example: spreadsheets such as Microsoft Excel or databases such as Microsoft Access database or Apache OpenOffice, which is a spreadsheet, database, word processor, plus other stuff (and it's free). MySQL is a server-side relational database, and the tables that we store in this database can be easily created, modified, or just read with server-side computer languages—such as PHP. We'll read a CSV file here: Make Pie Chart from CSV data, but on this page we'll stick to MySQL tables. Okay, let's look at some test data we typed into phpMyAdmin:

MySQL data in phpMyAdmin

Now let's check out MySQL test data stuck into an HTML table—it's handy and compact but a graph or chart lets you see the comparative size of the values more clearly:

table of MySQL data stuck into an HTML table

And you can, of course, simply import comma-separated data into MySQL if you're too lazy to type the data into phpMyAdmin, which sticks it into a MySQL table once you press Save. Plenty of apps can make lots better graphs than what we'll do here, but if you like programming as much as we do, you'd like to check out how it's done. Besides, what if you've created a PHP poll with results stored in a MySQL table, but do not feel like dealing with the script for making a chart, but you do want a pie chart?

Create the MySQL table database. And get the PHP program below called Make Pie Charts from MySQL Table Data, which you may copy from this page and name as a PHP file called Make-Pie-Charts-from-MySQL-Table-Data.php.

When creating your CSV file or Excel file that you'll export into MySQL as CSV, remember to type in a word like the name of a month and then an amount that must be a number. Don't add $ or other things—if you need it to be about money, have this in your CSV name, such as Monthly Data in Dollars Collected From Selling Pottery. If you'd like to add a $ filter so that you can deal with exported Excel data with $ in front of it, that's simple programming we'll leave up to you to add that to the PHP program below.

On to the script. First comes the include('config.php') statement. See The Configure File—it doesn't matter what you name your variables, as long as the right info is provided that lets the connection to the MySQL database succeed, and as long as the variable names after the mysql_connect() are the same as the ones before it.

After some simple CSS to style the page so it looks good, we code the grab and drop script. The Grab and Drop, Not Drag and Drop script lets you adjust the positions of your labels onscreen. It requires the script to constantly monitor cursor position.

After some simple CSS to style the divs to be grabbed, we define the variables we'll need for the grab and drop. Next we have the JavaScript function check(). It is called from inside the div we want to move. The code says: if the div we clicked is being carried, we drop it. But if the div we clicked is not being carried, we pick it up. The flag variable is 1 if the div is getting carried and 0 if it is not. It's set up as an on-off switch. The q variable is where the id of the div goes, or "iii" if we haven't grabbed one.

And next we have the function getCoords() run off the onload event in the body tag. The traditional event registration model is used to force the mouse coordinate getting function every time we move the mouse. Note the absence of () after getCoords. Calling the function needs () but registering which event fires
getCoords() requires you NOT to use ().

The getCoords() function itself is mostly the standard coordinate reader, which uses different properties depending on the browser, but instead of browser sniffing we check for object support. The clientX event attribute returns the horizontal coordinate (according to the client area) of the mouse pointer when an event was triggered. The client area is the current window. The clientY event attribute returns the vertical coordinate (according to the client area) of the mouse pointer when an event was triggered. The pageX property returns the position of the mouse pointer, relative to the left edge of the document. The pageY property sets or returns the y-coordinate of the mouse pointer relative to the top-left corner of the browser window's client area. The scrollTop property sets or retrieves the number of pixels by which the contents of an object are scrolled upward. The scrollLeft property sets or retrieves the number of pixels by which the contents of an object are scrolled to the left.

We use the getElementById() method to access the div element with the id of the div we clicked on. Then we alter the div's left and top properties to be the cursor's minus 5 pixels. This latter is because when we later drop the div, the cursor's click must be ON the div, not just off its top corner. Now the JavaScript function get_xxx_yyy() figures label positions based on many factors. It is assumed you'll still need to adjust a few labels with the grab and drop function, which is automatically available once the pie chart is done. Next the function trimit() ensures no label is over 12 characters long.

Next comes the function goodline(), which draws all the pie chart lines—720 radius lines from the circle center to the perimeter. And each radius line is about 100 pixels long, so that is a lot of line pixels. Had we made the circle 220 pixels wide instead of 200 pixels, IE8 would have stopped before it got to the end of the line drawing because only around 135,000 divs are allowed on a page and 200 wide circle filling requires nearly that many. It's not that there are that many pixels in the circle—it's just that as one draws lines from the outside perimeter to the circle's center, more and more overlapping occurs in which you're placing divs over divs.

The function goodline() first defines 25 different line colors and puts these in the colors[] array. Then all labels are run through the trimit() function. Next, after defining some variables we total all the amount data and put it into the variable called total. Then we figure out how many lines we'll draw for each amount data, and this goes into the data2[] array. But we change the data in this array to something more helpful: which of the 720 perimeter pixels of the circle will start each new line color. We add i%2 to these calculations, which is Modulus (division remainder) to add 1 to the data2[] values when i is odd and add 0 when it is even. This makes up for the fact that the arithmetic necessarily is integer (since div positions have to be on integer-based coordinates, with no decimal numbers to increase precision), so it does not have the precision of floating point. Without this incrementing on every other i value, the 720 lines (radii) will quit drawing before the circle is 100% filled—settling for 99%, which looks bad.

Then we start a for loop for drawing the 720 Bresenham's Line Algorithm lines from the perimeter to the center of the circle. In the loop, we check if we've gotten to a perimeter pixel where it is time for the next color by comparing i and data2[k]. Since k started at 0, and data2[k] is initially which of the 720 perimeter pixels will be the first color change location, as soon as we get to this point, we increment k to prepare to check the next location in the data2[] array.

Then the xxx and yyy variables, which get the results of figuring label positions, are calculated. We will look at xxx only—yyy is done similarly. So the xxx=Math.round(Math.abs(xl-xl2)/2) code gets half the difference between the current color change perimeter pixel and the last one. This is either added or substracted to xxx, depending on how far around the circle perimeter we've gotten. If the last color change perimeter pixel is left of the current one, we add this xxx midpoint to the current color change perimeter pixel location, otherwise it is added to the last one. We run the get_xxx_yyy() function now to fine-tune the label position, which sometimes needs further adjustment via our grab and drop routine. What should be obvious here is that we want labels to be centered next to pie slices, not at the start or end of the color change where one pie slice ends and the next one begins. Okay?

We now adjust for the fact that the values for xxx, the horizontal label coordinate, are fine if xxx>500 (the circle center), but if not, they'll be representing where the right edge of the label should end, since the label is to the left of the circle center. We want label starting coordinate, not ending coordinate. We get the label length and multiply by 6 (average pixels per character width) and add this to xxx, because the get_xxx_yyy() function defaulted to starting horizontal label positions at 100 pixels left of the circle edge, which means short labels would be too far left of the pie.

Now we create the label div. It has 'grab' as its class and onclick as its event and j as its id. The j variable contains the letter i and a unique number so it ends up as "i3" or something like it. The onclick event gets check() as its function which gets j as its argument. Note the \ escaping characters allowing two quotes in a row without befuddling the browser. We need the id (e.g., "i3") in quotes, but also needed to get the value of j between the quotes. And onclick='check(\""+j+"\")' does the job. Note the lines in the circle, the circle itself, and the labels all go into the variable addtopage, which gets added to the page later with innerHTML. And the actual labels in the labels[] array came from the PHP script on this page which read a MySQL table in PHP and then we converted the array into a JavaScript array with JavaScript Object Notation (JSON) for PHP to JavaScript Conversions. See below.

So far we have dealt with labels, but we must draw the actual lines now, using Bresenham's Line Algorithm, which will draw lines from the perimeter to the center of the circle, one point (in this case a 2x2 div) at a time. So xmiddle and ymiddle represent the center of the pie. The xx[] and yy[] arrays have the 720 circle perimeter coordinates figured in our circle() function. It runs before the goodline() function and saves these coordinates as it figures them out from a circle algorithm in circle(). The 2x2 divs are added to addtopage, and the routine keeps on until the end of the line is reached, which is detected with: if (x0 === xmiddle && y0 === ymiddle) break; which breaks us out of the line loop.

The circle routine is based on a common circle algorithm except most speed the draw by using 360 instead of 720 in all three of the instances you find it in the circle() function. This 720 is not really needed very badly with a smaller circle like this, but with circles twice as big (400 pixels wide rather than 200 pixels wide like in this pie chart script), you'll get gaps without it. In Good JavaScript Circle Algorithm we use a 3x3 div and it looks great, but in our pie charts we use a 5x5 div, which looks better and the inside pixels get covered by lines so it ends up as 3x3 anyway. The circle() function ends by calling the goodline() function.

Now we run into a "please wait....calculating" message script that displays a message while the pie is being built. Upon completion, the message goes away. The form discussed below has an onsubmit event that runs the setup_timer() function, which runs a routine to display the message and then a routine to stop displaying it. The 'tooltip' id is the id of the message div, and using DHTML to change the CSS display property from none to block to none is all that's required. We need the message because the pie creation takes a few seconds to draw the circle and then fill it with 720 lines.

Note that the body tag has an onload event that runs the startmouse() script. Next, there's a little form whose action is the PHP file Make-Pie-Charts-from-MySQL-Table-Data.php, the one whose source code is on this page. It is the name you need to give the PHP file on this page. In other words, once you type in the MySQL table name, such as makechart, the web page calls itself, reloading but remembering the POSTed table name. The form has two submit buttons. One says 'Get pie chart data w/o border' and the other says 'Get pie chart data'. The form runs the page we are on. The onsubmit='setup_timer()' code gets the message going.

Next comes the PHP script, although technically the include at the start was PHP also. We declare the $labels and $data arrays, then POST in the table name and check to see if there is such a table in your MySQL database on the server. And we check a flag POSTed from the form which tells the PHP script whether to set the variable $bd to 1 or 0. This is the variable to determine border or no border on the pie chart. Next comes the MySQL query "SHOW TABLES LIKE '$table'", which checks whether or not the table exists. Now we use the mysql_num_rows() Function. This function counts the number of table rows to be greater than zero if the table exists and has one or more rows, otherwise it gives the JavaScript message "No such table." and the page reloads. If the table is there and has data, the script goes on to load the table contents into a resultset $sql. We use a while() loop with the PHP function mysql_fetch_array() to get the table data into rows.

While in the loop, we use the array_push() function to get the MySQL table data into our two arrays. After closing the MySQL database, we give the JavaScript message "No such table." and the page reloads if there was no table or it was empty.

Next comes the div with the 'tooltip' id and the message. Note that its display property starts out as none—it will stay that way until form submission.

Now come JavaScript Object Notation (JSON) for PHP to JavaScript conversions. We use the json_encode() function to convert the PHP data and labels arrays and $bd flag and the MySQL table name into JavaScript arrays and variables, respectively, for convenient use. Then we run the circle() function and use the document.write() function to put the table name under the pie chart as the chart name.


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<TITLE>Make Pie Charts from MySQL Table Data</TITLE>
<meta name="description" content="Make Pie Charts from MySQL Table Data">
<meta name="keywords" content="Make Pie Charts from MySQL Table Data,View MySQL Table Data as Pie Charts,pie chart mysql data,php,javascript, dhtml, DHTML">

<STYLE TYPE="text/css">
BODY {margin-left:0; margin-right:0; margin-top:0;text-align:left;}
p, li, td {font:13px Verdana; color:black;text-align:left}
h1 {font:bold 28px Verdana; color:black;text-align:center}
h2 {font:bold 24px Verdana;text-align:center}
h3 {font:bold 15px Verdana;}
#myform {position:absolute;left:50px;top:20px}
#label {position:absolute;left:400px;top:550px;}
.grab {position:absolute;font-size:15px;font-weight:bold;color:black;}
<script type="text/javascript">

var q="iii";//which one is grabbed--if none, this is "iii"
var flag=0;//if one is grabbed--if none, this is 0
var bi,x,y,xq,yq;
var addtopage='';

function check(bi) {

function startmouse() {document.onmousemove=getCoords;}

function getCoords(e){
if (!e) var e = window.event;
if (e.pageX){xq = e.pageX;yq = e.pageY;} else if
(e.clientX){xq = e.clientX + document.body.scrollLeft;yq = e.clientY + document.body.scrollTop;}
if(flag==1&&q!="iii"){var myDiv=document.getElementById(q);myDiv.style.left=(xq-5)+'px';myDiv.style.top=(yq-5)+'px';}

var radius,xmiddle,ymiddle;
var xxx=0;var yyy=0;
var xlast = -1;
var ylast = -1;
var yy = new Array();
var xx = new Array();
var labels = new Array();
var data = new Array();
var t;
var s = null;
var timer_on = false;
var bd=0;

function get_xxx_yyy(){
return true;

function trimit(lll){lll = lll.substring(0, 12);return lll;}

function goodline(xmiddle,ymiddle,xx,yy,data,labels) {
var colors = new Array('#f00','#ff0','#f0f','#0f0','#0ff','#00f','#f88','#88f','#8f8','#888','#f80',
for(var i = 0;i < labels.length; i++){labels[i]=trimit(labels[i]);}
var data2 = new Array();
L=data.length; var ii=0; var total=0; var k=0; var xl=500;var yl=400; var xl2=500;var yl2=400;
for(var i = 0;i < L; i++){data2[i]=parseInt((data[i]/total)*720);}
for(var i = 0;i < L; i++){if(i>0){data2[i]=data2[i]+data2[i-1]+(i%2);}}
for (var i = 0; i <=720; i++) {
if(xxx<500){var llen=labels[k-1].length;xxx=xxx+6*(12-llen);}
var j="i"+(k-1).toString();
addtopage += "<div class='grab' id='"+j+"' onclick='check(\""+j+"\")' style='position:absolute;left:"+xxx+"px;top:"+yyy+"px;font-size:15px;font-weight:bold;'>"+labels[k-1]+"</div>";
var x0=xx[i];var y0=yy[i];
var dx = Math.abs(xmiddle - x0), sx = x0 < xmiddle ? 1 : -1;
var dy = Math.abs(ymiddle - y0), sy = y0 < ymiddle ? 1 : -1;
var err = (dx>dy ? dx : -dy)/2;
while (true) {
addtopage += "<div style='position:absolute;left:"+x0+"px;top:"+y0+"px;background-color:"+colors[k]+";width:2px;height:2px;font-size:1px'></div>";
if (x0 === xmiddle && y0 === ymiddle) break;
var e2 = err;
if (e2 > -dx) { err -= dy; x0 += sx; }
if (e2 < dy) { err += dx; y0 += sy; }
document.body.innerHTML += addtopage;

function circle(radius,xmiddle,ymiddle,bd){
for (var i = 0; i <= 720; i++) {
x = parseInt(xmiddle+(radius*Math.sin(i*2*(Math.PI/720)))+0.5);
y = parseInt(ymiddle+(radius*Math.cos(i*2*(Math.PI/720)))+0.5);
if (xlast != x || ylast != y){
xlast = x;ylast = y;
if(bd==1){addtopage += "<div style='position:absolute;left:"+(x-2)+"px;top:"+(y-2)+"px'><IMG SRC='anti3.gif' WIDTH=5 HEIGHT=5 BORDER=0></div>";}


function setup_timer(){t=7;stopcounter();start_timer();}

function stopcounter(){

function start_timer(){
if (t==0){stopcounter();bye();}
else {t=t-1;timer_on=true;

function bye(){document.getElementById('tooltip').style.display = 'none';}

<body onload="startmouse()">

<div id='myform'>
<center><h2>Make Pie Charts from MySQL Table Data (with Grab-and-Drop Labels)</h2>Click label to grab it. Move it with mouse. Click to drop it. Do not hold down mouse button to drag labels.</center>
<form action='Make-Pie-Charts-from-MySQL-Table-Data.php' method='post' name='sendname' onsubmit='setup_timer()'>
<center><input type='text' name='table' id='whattable' size='35' maxlength='40' value=''>
<input type='submit' value='Get pie chart data' name='flag1'> <input type='submit' value='Get pie chart data w/o border' name='flag'></center></form></div>

if (isset($_POST['flag'])){$bd=0;}else{$bd=1;}
if(strlen($table) > 0){
$exists = mysql_query("SHOW TABLES LIKE '$table'") or die(mysql_error());
$num_rows = mysql_num_rows($exists);
$sql=mysql_query("SELECT * FROM $table");

while($row = mysql_fetch_array($sql)){
$m=htmlentities(stripslashes($row['month']), ENT_QUOTES);
$a=htmlentities(stripslashes($row['amount']), ENT_QUOTES);
array_push ($data, $a);
array_push ($labels, $m);

else{echo '<script language="javascript">alert("No such table.");window.location="Make-Pie-Charts-from-MySQL-Table-Data.php";</script>;';}

<div id='tooltip' style='display:none;background-color:#ccc;border:1px solid black;z-index:99;position:absolute;left:250px;top:200px;width:350px;padding:10px'>
<p><b>please wait....calculating</b><BR></p></div>

<script type="text/javascript">
var labels = <?php echo json_encode($labels); ?>;
var data = <?php echo json_encode($data); ?>;
var t = <?php echo json_encode($t); ?>;
var bd = <?php echo json_encode($bd); ?>;
if (labels.length > 0 && data.length > 0) {
document.write("<div id='label'><h1>"+t+"</h1></div>");