The "Noob Link" Technical Reference
Pantz.org Index About Homepage

 ____  _   _ ____
|  _ \| | | |  _ \
| |_) | |_| | |_) |
|  __/|  _  |  __/ 
|_|   |_| |_|_|

Make a barchart using PHP and MYSQL.

This code uses data taken from a MYSQL database and makes a barchart from it. The PHP install on the webserver has to be compiled with the GD, Freetype2, and libjpeg (libpng looks better for graphs IMHO). To make the graph appear just access it like its a picture file.

Example: <img src="picture.php" height="200" width="200">
<?

//db connection info.
$dbuser = 'db';
$dbhost = 'database.here.com';
$dbpass = 'poe23451!@#';
$dbname = 'test';
$dbtble = 'info';
$mysql_link = mysql_connect($dbhost,$dbuser,$dbpass) or die ("Could not connect"); //connect to db
mysql_select_db ($dbname) or die ("DB select failed"); //select db
$query1 = "select productname, count(*) as groupcount from product group by productname"; //get product count by groups of products
$query2 = "select count(*) as countammount from product"; //get the ammount of products in the db
$result1 = mysql_db_query($dbname,$query1); //queryresult1
$result2 = mysql_db_query($dbname,$query2); //queryresult2
$num_products = mysql_num_rows($result1); //count number of products
while ($row = mysql_fetch_assoc ($result2)) //put ammount of products from query in a var
{
    $countammount = $row[countammount];
}


/*******************************************
  Initial calculations for graph 
*******************************************/
// set up constants
$width=700;        // width of image in pixels - this will fit in 640x480
$left_margin = 50; // space to leave on left of image
$right_margin= 50; // ditto right   
$bar_height = 40;  
$bar_spacing = $bar_height/2; 
$title_size= 16; // point
$main_size= 12; // point
$small_size= 12; // point
$text_indent = 10; // position for text labels on left

// set up initial point to draw from
$x = $left_margin + 150;  // place to draw baseline of the graph.bigger number moves graph to the left. 
$y = 50;		  // ditto 
$bar_unit = ($width-($x+$right_margin)) / 100;   // one "point" on the graph

// calculate height of graph - bars plus gaps plus some margin
$height = $num_products * ($bar_height + $bar_spacing) + 50;   

/*******************************************
  Set up base image 
*******************************************/
// create a blank canvas
$im = imagecreate($width,$height);   
     
// Allocate colors   
$white=ImageColorAllocate($im,255,255,255);   
$blue=ImageColorAllocate($im,0,64,128);   
$black=ImageColorAllocate($im,0,0,0);   
$pink = ImageColorAllocate($im,255,78,243); 

$text_color = $black;
$percent_color = $black;
$bg_color = $white;
$line_color = $black;
$bar_color = $blue;
$number_color = $pink;

// Create "canvas" to draw on 
ImageFilledRectangle($im,0,0,$width,$height,$bg_color);   

// Draw outline around canvas 
ImageRectangle($im,0,0,$width-1,$height-1,$line_color);   

// Add title

$title = "Percentage of Products in Database";
$titlefont = 3;
$txtsz = imagefontwidth($titlefont) * strlen($title); //size of the text
$xpos = (int)(($width - $txtsz)/2); // center the title 
$xpos = max(1, $xpos); // force positive coordinates 
$ypos = 3; // distance from top  
imagestring($im, $titlefont, $xpos, $ypos, $title , $black); //print title

// Draw a base line from a little above first bar location to a little below last  
ImageLine($im, $x, $y-5, $x, $height-15, $line_color);   

/*******************************************
  Draw data into graph 
*******************************************/
// Get each line of db data and draw corresponding bars   
while ($row = mysql_fetch_assoc ($result1)) 
{
    $percent = intval(round(($row[groupcount]/$countammount)*100));
    
    // display percent for this value  
    imagestring($im, $titlefont, $width-30, $y+($bar_height/2),$percent."%",$black);  
    
    $right_value = intval(round(($row[groupcount]/$countammount)*100));
    
    // length of bar for this value   
    $bar_length = $x + ($right_value * $bar_unit);   
    
    // draw bar for this value   
    ImageFilledRectangle($im, $x, $y-2, $bar_length, $y+$bar_height, $bar_color);  
    
    // draw title for this value   
    imagestring($im, 1, $text_indent, $y+($bar_height/2), "$row[productname]", $black);  
    
    // draw outline showing 100% 
    ImageRectangle($im, $bar_length+1, $y-2, ($x+(100*$bar_unit)), $y+$bar_height, $line_color);  
    
    // display numbers   
    imagestring($im, $titlefont, $x+(100*$bar_unit)-50, $y+($bar_height/2), $row[groupcount]."/".$countammount,$number_color);
    
    // move down to next bar
    $y=$y+($bar_height+$bar_spacing);   
} //end main draw of bars,titles,numbers 

/*******************************************
  Display image 
*******************************************/
Header("Content-type:  image/jpeg");
Imagejpeg($im);   

/*******************************************
  Clean up 
*******************************************/
ImageDestroy($im);
?>