Graphing with PHP and GD

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 1	Month 2	Month 3	Month 4	Month 5	Month 6
London	325	345	400	390	370	320
Atlanta	300	280	270	300	350	410
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.