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 Line Chart from MySQL Table Data

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

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

Line Chart made from MySQL Table data

There are endless reasons to draw lines on browser screens. Our reason was to create line charts from MySQL table data although elsewhere we made a line chart with CSV data. We used a Pythagorean theorem-based algorithm below. However, as mentioned, we could have used the Bresenham's Line Algorithm instead in our line charts.

When considering line drawing algorithms, 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 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 charts and graphs 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 Line 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 Line 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 bar chart or line chart?

Create the MySQL table database. And get the PHP program below called Make Line Charts from MySQL table Data, which you may copy from this page and name as a PHP file called make-line-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 bDiv DIV which we use for both the month labels and the amount labels. We'll be adding these labels to the document with DOM methods like createElement() and appendChild(), using innerHTML for inserting the actual text into the DIV. We'll use calculated top and marginLeft properties on this DIV later. Note that the overflow property is hidden and the background-color is transparent so in case you decide to have longer labels, the text won't slobber all over nearby text.

Next, there's a little form whose action is the PHP file make-line-charts-from-mysql-table-data.php, which 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.

Next comes the PHP script, although technically the include at the start was PHP also. We declare the $month and $amount arrays, then POST in the table name and check to see if there is such a table in your MySQL database on the server. 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. Then, in order to know how to arrange the web page, we need to know the maximum value found in the $amount array. We use the max() function for this purpose. 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 line drawing script, which is JavaScript, because even though the PHP GD library can draw lines, we prefer the JavaScript way—why tax the server when the browser can do the job client-side just fine?

In the line drawing script, the function goodline() first gets its variables declared. Next the hypotenuse length is put into the lengthofline variable, using good old Pythagorus. (The formula gets the square root of the sum of the squares of two adjacent sides.) Next, we define a for loop where we go from one end (of the line we're drawing) to the other, a pixel at a time, plotting divs on each line pixel. So, technically, given two points we find the length of the line between two given points and then a variation on slope-intercept form and point-slope form equations is used to plot each point of the line. The line is not quite a line with anti-aliased smoothness, but this technique using a 4x4 grey-background-colored div is the next best thing. The addtopage variable has all those divs added to it and now we add one more thing: a vertical line that will be right above one of the Month labels and going to the top of the chart, intersecting a point where two lines meet and the Amount label is later inserted. Finally, we use the innerHTML() function to put the two lines on the page. Where does the xx[q] number for the left style position of the vertical line come from? You'll see below.

Now come JavaScript Object Notation (JSON) for PHP to JavaScript conversions. We use the json_encode() function to convert the month names array and the amount array and the biggest amount number and the CSV file name into JavaScript array and variables, respectively, for convenient use. Then we declare xx and yy as arrays. Then we get a factor (r) to divide each amount by, after which we subtract all this from 497 (bottom y edge of graph) to get a y value to end a line at. Next we get ll, the actual number of characters in the highest amount. E.g., the amount 555 would make ll=3. Next we get 0.01 as the value of factor (still using 555 as an example), using the Math.pow() method. Next we get 500 (still using 555 as an example) as the value of num which is the highest value where there will be a horizontal line. There will be 5 horizontal lines added. They will be at y values corresponding to the amount values 100, 200, 300, 400, and 500.

Next we get fac=100 by use of Math.pow(10,ll-1), so we have the lowest value where there will be a horizontal line. Now we use the variable firstint to hold num/fac which is 5—the number of horizontal graph grid lines. And next we use variable dd to hold num/b which is 0.9009. Then we get 63 when we use the variable o to hold Math.round((350*dd)/firstint). So this 63 is the distance apart of each horizontal line. The 350 is the height of the graph minus 30—which we did because we don't want an amount label in the graph or a line end to hit the top of the graph. Only the vertical grid lines are allowed to touch the top.

Next we use the Document Object Model (DOM) in a for loop to add lines to the page. We use the createElement() Method to bring a div into existence and the appendChild() Method to get it integrated into the document. The innerHTML property is used to put the line divs into the div elements.

Next we test the a and the m arrays—if both have lengths greater than 0, we go on. Otherwise we jump to the end of the script and do nothing. This ensures the MySQL table had some data to graph. Now comes a for loop in which we put values in the xx and yy arrays. But since i starts at 0, the first amount line start (also the vertical line x coordinate) is at xx[i]=59. The next xx[i] value is at 137, which is 78+59, and so on, with each new line starting 78 horizontal pixels right of the last. The factor r is used to divide each amount (a[i]) by, after which we subtract all this from 497 (bottom y edge of graph) to get a y value to end a line at. We use if(i>0) before running the line routine because we are drawing lines from A to B, and when i is 0, we know only the A but not the B so where would we draw such a line TO? And so the "else" is that we do nothing but draw the vertical line at x=59 right now. But if i > 0 we run the line routine, packing it with 4 parameter values: goodline(xx[i-1], xx[i], yy[i-1], yy[i]);. This is all the data needed for A and B, above, which we're just using here to say one line's start point to the next line's start point.

We've already talked about the line routine, goodline(), which draws a 4-pixel-thick line representing amount fluctuations and then draws a vertical line as a graph grid line. The for loop that runs this function several times continues until the last line is drawn. However, there are 3 more things to do: the month labels, the amount labels, and the box around the entire line chart.

Starting with the month labels, we use the Document Object Model (DOM) in a for loop to add month labels to the page. We use createElement() and appendChild() DOM functions. Note the calculated marginLeft property and the innerHTML() method to get the label into the new div.

We do the same thing with the amount labels: we use the Document Object Model (DOM) in a for loop to add amount labels to the page. We use createElement() and appendChild() DOM functions. Note the calculated marginLeft property and top property and the innerHTML() method to get the label into the new div.

Finally, we include the MySQL table name as a big title under the graph and we draw a box around the whole graph using a width forced by the width and min-width properties.


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<TITLE>Make Line Charts from MySQL Table Data</TITLE>
<meta name="description" content="Make Line Charts from MySQL Table Data">
<meta name="keywords" content="Make Line Charts from MySQL Table Data,View MySQL Table Data as Line Charts,chart MySQL Table 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;}
#myid {position:absolute;left:10px;top:117px;height:380px;border: solid 1px #000;}
#myform {position:absolute;left:50px;top:20px}
#label {position:absolute;left:400px;top:550px;}
.bDiv {
width: 76px;
border: none;
background-color: transparent;
font-size: 11px;
font-weight: bold;
font-family: verdana;
color: #000;
padding: 5px;


<div id='myform'>
<center><h1>Make Line Charts from MySQL Table Data</h1></center>
<form action='make-line-charts-from-mysql-table-data.php' method='post' name='sendname'>
<center><input type='text' name='table' id='whattable' size='35' maxlength='40' value=''>
<input type='submit' value='Get chart data' name='flag'></center></form></div>

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 ($amount, $a);
array_push ($month, $m);

else{echo '<script language="javascript">alert("No such table.");window.location="make-line-charts-from-mysql-table-data.php";</script>;';}

<script language="javascript">
var xa, xb, ya, yb, x, y; var addtopage = "";

function goodline(xa, xb, ya, yb) {
var lengthofline = Math.sqrt((xa-xb)*(xa-xb)+(ya-yb)*(ya-yb));
for(var i=0; i<lengthofline; i++){
addtopage += "<div style='position:absolute;left:"+x+"px;top:"+y+"px;background-color:#a4a4a4;width:4px;height:4px;font-size:1px'></div>";}
addtopage += "<div style='position:absolute;left:"+xx[q]+"px;top:117px;'><IMG SRC='vert.gif' WIDTH=2 HEIGHT=380 BORDER=0></div>";
document.body.innerHTML += addtopage;}

var m = <?php echo json_encode($month); ?>;
var a = <?php echo json_encode($amount); ?>;
var b = <?php echo json_encode($biggest); ?>;
var t = <?php echo json_encode($t); ?>;
var r=b/350;var xx = new Array();var yy = new Array();
var ll=(b.toString()).length;
var factor=Math.pow(10,-1*(ll-1));
var num=(Math.floor(b*factor)/factor);
var fac=Math.pow(10,ll-1);
var firstint=num/fac;
var dd=num/b;
var o=Math.round((350*dd)/firstint); //497y start then go up (y less) for each tic
for (var i=firstint;i>0;i--){
var divTag = document.createElement("div");
divTag.id="a" + i;
divTag.style.marginLeft = 10+"px";
divTag.style.position = "absolute";
divTag.style.top = (497-o*(firstint-(firstint-i)))+"px";
divTag.style.height = 2+"px";
divTag.innerHTML = "<IMG SRC='hor.gif' WIDTH='"+(a.length*80)+"' HEIGHT='2' BORDER='0'>";

if (a.length > 0 && m.length > 0) {

for (var i=0;i<a.length;i++){q=i;
if(i>0){goodline(xx[i-1], xx[i], yy[i-1], yy[i]);}else{addtopage += "<div style='position:absolute;left:"+xx[q]+"px;top:117px;'><IMG SRC='vert.gif' WIDTH=2 HEIGHT=380 BORDER=0></div>";}

for (var i=0;i<a.length;i++){
var divTag = document.createElement("div");
divTag.id="b" + i;
divTag.setAttribute("align", "center");
divTag.style.marginLeft = (i*78+20)+"px";
divTag.style.position = "absolute";
divTag.style.top = 500+"px";
divTag.style.height = 22+"px";
divTag.className = "bDiv";
divTag.innerHTML = m[i];

for (var i=0;i<a.length;i++){
var divTag = document.createElement("div");
divTag.id="c" + i;
divTag.setAttribute("align", "center");
divTag.style.marginLeft = (i*78+20)+"px";
divTag.style.position = "absolute";
divTag.style.top = (470-(a[i]/r))+"px";
divTag.style.height = 22+"px";
divTag.className = "bDiv";
divTag.innerHTML = a[i];
document.write("<div id='label'><h1>"+t+"</h1></div><div id='myid' style='min-width:"+(a.length*80)+"px; width:"+(a.length*80)+"px'> </div>");