picture of Allan Kent
As more and more web sites start incorporating a database in some part of their design, we can start using the data that those databases collect to display statistics. A lot of web sites today have opinion polls or voting applications somewhere. It's one thing to display the data in a table, but quite another to create a graph from the data. Sure, we could slap together a workable bar graph with some tables, but what about line graphs or pie graphs ? PHP has a set of Image functions that will allow us to create images on the fly - let's see how these functions can be put to good use and create some graphs.
Keep in mind though that the Image functions in PHP require that your system has the GD libraries installed. You can find the libraries at www.boutell.com/gd/ as well as some pointers on how to get them installed on your system.
Note: I started this writing this article on a Red Hat Linux 6.2 machine, and finished it off on a Windows 2000 machine. On Red Hat I was running the lastest version of PHP and MySQL, but on Windows I had to go back to MySQL 3.21.29 and PHP 3.0.11 as it was all I had access to - the SQL and PHP ran on both without modification. Let's not get into a whole thing on cross-platform development here, shall we ?
To keep the data simple and allow us to concentrate on creating the graphs, I used a small hypothetical dataset - the sales figures for the first 6 months of the year for 2 branches of an international company. I based the one office in London and the other in Atlanta.
 Month 1Month 2Month 3Month 4Month 5Month 6
London325345400390370320
Atlanta300280270300350410
Sales Figures for first 6 months
I then entered the data into a mySQL database - I've included a dump of the data below:
# MySQL dump 7.1
#
# Host: localhost    Database: graphing
#--------------------------------------------------------
# Server version	3.22.32

#
# Table structure for table 'sales'
#
CREATE TABLE sales (
  g_id int(11) DEFAULT '0' NOT NULL auto_increment,
  g_month tinyint(4) DEFAULT '0' NOT NULL,
  g_team tinytext NOT NULL,
  g_num int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (g_id)
);

#
# Dumping data for table 'sales'
#

INSERT INTO sales VALUES (1,1,'London',325);
INSERT INTO sales VALUES (2,1,'Atlanta',300);
INSERT INTO sales VALUES (3,2,'London',345);
INSERT INTO sales VALUES (4,2,'Atlanta',280);
INSERT INTO sales VALUES (5,3,'London',400);
INSERT INTO sales VALUES (6,3,'Atlanta',270);
INSERT INTO sales VALUES (7,4,'London',390);
INSERT INTO sales VALUES (8,4,'Atlanta',300);
INSERT INTO sales VALUES (9,5,'London',370);
INSERT INTO sales VALUES (10,5,'Atlanta',350);
INSERT INTO sales VALUES (11,6,'London',320);
INSERT INTO sales VALUES (12,6,'Atlanta',410);
My database is called graphing and the table with the data in is called sales. I have stored the month number as an integer in the field g_month.
Before we can start drawing the graph, let's start with the basics and look at how PHP creates an image. The first thing that we need to do is tell the browser that it's getting an image, and what kind of image it's getting:

<?php

Header
"Content-type: image/gif");

?>
Now that the browser knows it's getting a GIF image we can start creating the image. First up we have to create a blank canvas to start drawing on. The ImageCreate function does this for us. ImageCreate will return an identifier to the image and it we need to tell the function is how large to make the canvas in pixels, x (width) by y (height).

<?php

$image 
imagecreate(200,200);

?>
Now we've got a 200 pixels by 200 pixels blank canvas to start working on. The next step is to create some colors that are going to be used in the image. For this we have to use the ImageColorAllocate function, and it needs to know the identifier of the image that the color is for, as well as the RGB value of the color. ImageColorAllocate will return an identifier to the color that we just created. We will use the color identifier later when we start drawing on the canvas. The way that ImageColorAllocate works is that we have to allocate a color for each image that we are working on - so if we were creating 3 GIF's and wanted red in each of them, we would have to allocate the color red 3 times - once for each GIF. I'll allocate a color identifier called $maroon, and give the red value 100, green 0 and blue 0. While I'm at it I'll create white as well.

<?php

$maroon 
ImageColorAllocate($image,100,0,0);
$white ImageColorAllocate($image,255,255,255);

?>
Now that we've got our color, we can draw something with it. The first thing to do is paint our canvas white. The function ImageFilledRectangle will draw a rectangle on our canvas and fill it with the color we specify.

<?php

    ImageFilledRectangle
($image,0,0,200,200,$white);

?>
The first thing to tell ImageFilledRectangle, as with all Image functions, is which image we are working with, so we pass it the $image identifier. It then needs to know the x and y co-ordinates to start the rectangle at (0,0 - the upper left hand corner) and the co-ordinates to end the rectangle at (200,200 - the bottom right hand corner of the canvas). The last thing to tell it is the identifier of the color to draw the rectangle in, in this case $white. Now we can start drawing on our nice white background.

<?php

ImageRectangle
($image,10,10,190,190,$maroon);
ImageFilledRectangle($image,50,50,150,150,$maroon);

?>
ImageRectangle works in exactly the same way as ImageFilledRectangle, except that it doesn't fill the rectangle with color. Once we are done drawing, we can output the image -

<?php

ImageGIF
($image);

?>
And then destroy the image that we are storing in memory:

<?php

ImageDestroy
($image);

?>
What this gives us is:
Maroon Square
which is not quite a graph, yet.

Line Graphs

To start off we can create a blank 200x200 pixel canvas and fill it with a gray. Onto this we are going to plot our lines. We then allocate a color for each office, red for London, green for Atlanta.
Now we can start getting our data and drawing it. The important thing to keep in mind when drawing lines, is that the ImageLine requires us to tell it the x and y co-ordinates of the start and of the end of the line. Now if we are grabbing our data out of a database we will only have one set of co-ordinates at a time. So we could either store our values into an array, or have a temporary variable that we store the previous set of co-ordinates in. I've opted to use an array.
Besides the actual co-ordinates that we will be plotting, we will need to know the maximum value that we can attain (the ceiling of our graph) as well as the number of columns of information (the number of plots along our x axis). So after connecting to the database, we select the MAX(g_num) from our database and store that away for later use. We then select all of the records where the g_team is 'London' and grab the number of rows that were returned, so that we will know how many columns of data we are working with. From our table above, you can see that we will be working with 6 months or columns of data, and that the maximum value we ever have is 410

<?php

    $columns 
6;
    
$max 410;

?>
The x value is always going to be the easiest - that will range from 0 to the width of our image, in this case 200. We'll start our line at 0 and want to end our line at 200, so after starting with $x=0 we'll need to increment $x by an amount ($columns-1) times (in this case 5) to get to 200 - which will give us 6 plots, with the last being on 200. With this in mind we can create a variable called $xincrement and give that a value of 200 divided by ($columns-1).
We'll then give $x a starting value of 0 and at the bottom of each loop while we're grabbing the rows of our result set we can increment $x by $xincrement.

<?php

      $xincrement 
bcdiv(200,$columns-1,0);

?>
bcdiv divides the first operand by the second and returns a value with third operand decimal spaces. To use the BCMath functions you will need to compile it into PHP under Unix, but the Windows version has it already compiled in. README.BCMATH in the root directory of the PHP source will explain the where and how.
The y value is going to be a proportionate amount of $max, so what we can do is divide the g_num amount by $max to get the proportional fraction, and then multiply that by 200 - the height of our image.

<?php

    $y 
bcmul(bcdiv($salesRow[0],$max,2),200,2);

?>
$salesRow[0] is g_num for the current row, dividing that by $max and getting a result with 2 decimal places. We then use bcmul to multiply the result by 200.
Then what we can do is loop around through the array, using the current element as the start of the line, and current element +1 as the end co-ordinates of the line. Because the last element will never be the start of a line, we must cut the loop short by one, so instead of looping around while less than or equal to $columns-1, we loop around while less than $columns-1.
Let's have a look at the code so far:

<?php

  
// Send out the header info and create the initial blank canvas
  
Header'Content-type: image/gif');
  
$image imagecreate(200,200);
  
// Allocate some colors
  
$red ImageColorAllocate($image,255,0,0);
  
$blue ImageColorAllocate($image,0,0,255);
  
$white ImageColorAllocate($image,255,255,255);
  
$grey ImageColorAllocate($image,200,200,200);
  
// create an initial grey rectangle for us to draw on
  
ImageFilledRectangle($image,0,0,200,200,$grey);
  
// Connect to the mysql server and select the database
  
$connect mysql_connect('','root','');
  
mysql_select_db('graphing',$connect);
  
// find out the maximum number in our recordset
  
$sql 'SELECT MAX(g_num) FROM sales';
  
$maxResult mysql_query($sql,$connect);
  
$max mysql_result($maxResult,0,0);
  
// get the recordset for London
  
$sql "SELECT g_num FROM sales WHERE g_team='London' ORDER BY g_month";
  
$salesResult mysql_query($sql,$connect);
  
// find out how many rows were returned, that is the number of 'columns'
  
$columns mysql_num_rows($salesResult);
  
// how much to increment $x by ? 
  
$xincrement bcdiv(200,$columns-1,0);
  
$x=0;
  
// $i will keep track of the row number
  
$i=0;
  
// lop around while we have rows of data
  
while($salesRow=mysql_fetch_array($salesResult)) {
      
// work out the y co-ordinate as discussed above
      
$y bcmul(bcdiv($salesRow[0],$max,2),200,2);
     
// add the values into the $points array
    
$points[$i][0] = $x;
    
$points[$i][1] = $y;
    
// increment $x by $xincrement
    
$x+=$xincrement;
    
// increment $i
    
$i++;
  }
  
// now we loop around through our $points array, while $i is
  // less that $columns-1
  
for($i=0;$i<$columns-1;$i++) {
    
// We pass $points[$I][0] as the first x co-ord, 
// and $points[$I][1] as the first y co-ord
// $points[$I+1][0], $points[$I+1][1] will be the next
// x,y co-ord set.
ImageLine($image,$points[$i][0],$points[$i][1],$points[$i+1][0],$points[$i+1][1],$red);
  }
  
// output the GIF to the browser and free up memory
  
ImageGIF($image);
  
ImageDestroy($image);

?>
This gives us the following result:
Wrong Way
which is both wrong and irritating. The problem that we have is that we have been working on the traditional method of x and y co-ordinates that radiate outwards from the bottom left hand corner. The co-ordinate system in the Image functions radiate out from the top left hand corner, so while our x position has been correct, our y position has been mirrored.
What we need to do is alter our code that determines the y position so that it works in the opposite way - change the line.

<?php

    ImageLine
($image,$points[$i][0],$points[$i][1],$points[$i+1][0],$points[$i+1][1],$red);

?>
To read:

<?php

ImageLine
($image,$points[$i][0],200-$points[$i][1],$points[$i+1][0],200-$points[$i+1][1],$red);

?>
And we are presented with the graph:
Getting Better
All we need to do now is add a line that graphs the data from Atlanta and we'll be done.

<?php

  $sql 
"SELECT g_num FROM sales WHERE g_team='Atlanta' ORDER BY g_month";
  
$salesResult mysql_query($sql,$connect);
  
$columns mysql_num_rows($salesResult);
  
$xincrement bcdiv(200,$columns-1,0);
  
$x=0;
  
$i=0;
  while(
$salesRow=mysql_fetch_array($salesResult)) {
      
$y bcmul(bcdiv($salesRow[0],$max,2),200,2);
    
$points[$i][0] = $x;
    
$points[$i][1] = $y;
    
$x+=$xincrement;
    
$i++;
  }
  for(
$i=0;$i<$columns-1;$i++) {
    
ImageLine($image,$points[$i][0],200-$points[$i][1],$points[$i+1][0],200-$points[$i+1][1],$blue);
  }

?>
Right Graph
The data we've used in this example has been static - in the next section we'll take a look at how we can handle dynamic data and have a look at adding some axes and labels to the graph.
--Allan