PHP generating PNG bar chart

Question

I have a database with orders in and need to generate a PNG graph to show the total of orders placed every month. I can get this to work from a normal array, however I can't seem to get it to work from my database query.

I think the code which needs changing is the 3rd and fourth for loops, as this is the code which I think generates the month names and bars.

Here is the code which works via an array, with the SQL query I need to use declared underneath the $values array:

<?php

require_once(DATABASE CONNECTOR REMOVED);

//Set the values
$values=array(
    "Jan" => 1100,
    "Feb" => 1300,
    "Mar" => 2150,
    "Apr" => 810,
    "May" => 3100,
    "Jun" => 1100,
    "Jul" => 1900,
    "Aug" => 1750,
    "Sep" => 3900,
    "Oct" => 2860,
    "Nov" => 1500,
    "Dec" => 1960
); 

$query = mysql_query("SELECT SUM(order_total) AS total, MONTHNAME(FROM_UNIXTIME(order_date)) AS month FROM orders GROUP BY YEAR (order_date), MONTH(order_date)") or die ('Error: '.mysql_error());

$img_width=600;
$img_height=400;
$margins=35;
$graph_width=$img_width - $margins * 2;
$graph_height=$img_height - $margins * 2; 

$img=imagecreatetruecolor($img_width,$img_height);

$bar_width=20;
$total_bars=count($values);
$gap= ($graph_width- $total_bars * $bar_width ) / ($total_bars +1); 

$bar_color=imagecolorallocate($img,70,85,96);
$background_color=imagecolorallocate($img,255,255,255);
$border_color=imagecolorallocate($img,200,200,200);
$line_color=imagecolorallocate($img,70,85,96);

imagefilledrectangle($img,1,1,$img_width-2,$img_height-2,$border_color);
imagefilledrectangle($img,$margins,$margins,$img_width-1-$margins,$img_height-1-$margins,$background_color);

$max_value=max($values);
$ratio= $graph_height/$max_value;

$horizontal_lines=20;
$horizontal_gap=$graph_height/$horizontal_lines;
for($i=1;$i<=$horizontal_lines;$i++){
    $y=$img_height - $margins - $horizontal_gap * $i ;
    imageline($img,$margins,$y,$img_width-$margins,$y,$line_color);
    $v='£'.intval($horizontal_gap * $i /$ratio);
    imagettftext($img, 8, 0, 2 ,$y+5, $bar_color, 'includes/trebuc.ttf', $v);
}

for($i=1;$i<=$horizontal_lines;$i++){
    $y=$img_height - $margins - $horizontal_gap * $i ;
    imageline($img,$margins,$y,$img_width-$margins,$y,$line_color);
    $v='£'.intval($horizontal_gap * $i /$ratio);
    imagettftext($img, 8, 0, 2 ,$y+5, $bar_color, 'includes/trebuc.ttf', $v);
}


    for($i=0;$i< $total_bars; $i++){
        list($key,$value)=each($values);
        $x1= $margins + $gap + $i * ($gap+$bar_width) ;
        $x2= $x1 + $bar_width;
        $y1=$margins +$graph_height- intval($value * $ratio) ;
        $y2=$img_height-$margins;
        imagefilledrectangle($img,$x1,$y1,$x2,$y2,$bar_color);
        imagettftext($img, 10, 0, $x1,$img_height - 14, $bar_color, 'includes/trebuc.ttf', $key);
}



for($i=0;$i< $total_bars; $i++){
    list($key,$value)=each($values);
    $x1= $margins + $gap + $i * ($gap+$bar_width) ;
    $x2= $x1 + $bar_width;
    $y1=$margins +$graph_height- intval($value * $ratio) ;
    $y2=$img_height-$margins;
    imagefilledrectangle($img,$x1,$y1,$x2,$y2,$bar_color);
    imagettftext($img, 10, 0, $x1,$img_height - 14, $bar_color, 'includes/trebuc.ttf', $key);
}

header("Content-type:image/png");
imagepng($img); 

?>

EDIT

I am aware that this will not just limit itself to the last 12 months, I aim to fix this once I have the graph being built by values from the the database

don't you need to look at what you are getting from database instead of posting that code? — dmitry, Jan 09 '12 at 16:28
I used while($fetch = mysql_fetch_array($query)){ echo 'Month vs total: '.$fetch["month"].' - '.$fetch["total"].'
'; } to check the output and it outputs "Month vs total: November - 1155.50
" etc as expected. — Phil Young, Jan 09 '12 at 16:34
At first, your code is messy with gd calls, which seem unrelated to your problem: the code is working with given inline array but not with database data. Right? So, check the database data. Fetch it into array like in the code and try again. You can debug your code, the rest of us here - not. (+ some advice: take a look at graph library: [jpgraph](http://jpgraph.net/)) — dmitry, Jan 09 '12 at 16:39

score 0 · Answer 1 · answered Jul 31 '12 at 09:39

0

You must fetch the results and fill the $values array:

after the mysql_query():

$values = array();
while ($row = mysql_fetch($query) {
  $values[substr($row['month'], 0, 3)] = $row['total'];
}

answered Jul 31 '12 at 09:39

Pedro L.

7,376
3
25
27

PHP generating PNG bar chart

1 Answers1