Friday, March 12, 2010

11:54 PM

In the example below, using graphic functions we will build a bar chart based on the values stored in MySQL database. In our case, values represent poll results.

In this example we will use poll database. To create it run the following SQL script in your MySQL server:
USE poll;

CREATE TABLE `results` ( 
   book_type VARCHAR(50), 
   num_votes INT

INSERT INTO `results` values  
    ('Classic', 15), 
    ('Fantasy', 7), 
    ('Humor', 32), 
    ('Mystery', 12), 
    ('Poetry', 25);
Script building a bar chart is quite long, so let's divide it into two parts and discuss them separately. The first part of the script connects to MySQL database and requests data that will be represented in the bar chart.
//Querying the database to get poll results

//connection to the database 
$dbhandle = mysql_connect("localhost", "root", "") 
  or die("Unable to connect to MySQL"); 

//select a database to work with 
$selected = mysql_select_db("poll",$dbhandle) 
  or die("Could not select examples"); 

//execute the SQL query and return records 
$result = mysql_query("SELECT * FROM results"); 

$num_poller = mysql_num_rows($result); 
$total_votes = 0; 

//fetch the data from the database 
while ($row = mysql_fetch_array($result)) { 
  $total_votes += $row{'num_votes'};  //calculating total number of votes

//nulling the pointer $result

//close the connection 
In the second part of the script first of all we will calculate variables needed to build the chart. Then, we prepare primary image and actually output data. Results for each genre are picked from the database one by one, percentage of votes is calculated, and then bars and explanatory notes are outputted.
In this script we are using TrueType fonts to get anti-aliased text. Functions that work with the TrueType fonts have its own peculiarities. First, you need to point where the fonts are located. For this, system variable GDFONTPATH is set. Then, we need to set the font name. TrueType fonts are rendered using  ImageTTFText().
We have already discussed above all functions used for drawing, the code has been commented and is quite clear, so you shouldn’t have any trouble.
//Tell gd2, where your fonts reside 
$font = 'arial'; 

//Set starting point for drawing 
$y = 50; 

//Specify constant values 
$width = 700; //Image width in pixels
$bar_height = 20; //Bars height
$height = $num_poller * $bar_height * 1.5 + 70; //Calculating image height 
$bar_unit = ($width - 400) / 100; //Distance on the bar chart standing for 1 unit

//Create the image resource 
$image = ImageCreate($width, $height); 

//We are making four colors, white, black, blue and red 
$white = ImageColorAllocate($image, 255, 255, 255); 
$black = ImageColorAllocate($image, 0, 0, 0); 
$red   = ImageColorAllocate($image, 255, 0, 0); 
$blue  = imagecolorallocate($image,0,0,255); 

//Create image background 
//Draw background shape 
ImageRectangle($image, 0, 0, $width-1, $height-1, $black); 
//Output header 
ImageTTFText($image, 16, 0, $width/3 + 50, $y - 20, $black, $font, 'Poll Results'); 

while ($row = mysql_fetch_object($result)) { 
  if ($total_votes > 0) 
    $percent = intval(round(($row->num_votes/$total_votes)*100)); 
    $percent = 0; 

//Output header for a particular value 
ImageTTFText($image,12,0,10, $y+($bar_height/2), $black, $font, $row->book_type); 
//Output percentage for a particular value 
ImageTTFText($image, 12, 0, 170, $y + ($bar_height/2),$red,$font,$percent.'%'); 

$bar_length = $percent * $bar_unit; 

//Draw a shape that corresponds to 100% 
ImageRectangle($image, $bar_length+221, $y-2, (220+(100*$bar_unit)), $y+$bar_height, $black); 
//Output a bar for a particular value 
ImageFilledRectangle($image,220,$y-2,220+$bar_length, $y+$bar_height, $blue); 
//Output the number of votes 
ImageTTFText($image, 12, 0, 250+100*$bar_unit, $y+($bar_height/2), $black, $font, $row->num_votes.' votes cast.'); 

//Going down to the next bar 
$y = $y + ($bar_height * 1.5); 

//Tell the browser what kind of file is come in 
header("Content-Type: image/jpeg"); 

//Output the newly created image in jpeg format 

//Free up resources