Dynamic Graphs with PHP(part 2)-..:: Programmer Kingdom ::..

Dynamic Graphs with PHP is a tutorial that will show you how to use the data recorded on a site, and transform some of it into some eye pleasing graphs. Great if you need to show them off to more then yourself.

Following the method used to get the number of options, we can similarly get the number of votes. Observe:

$pollquery = mysql_query("SELECT * FROM poll");
$numvotes = mysql_num_rows($pollquery);
?>

Both queries select all entries to the databases without discriminating. Using mysql_num_rows will give us the number of entries in the database, which is the number of vote options in the voteoptions database, or number of votes in the poll database. Now we have all the raw data that has to do with the actual votes, lets get back to the actual graph. There are two more variables that need to be set before the drawing begins:

$xval = 30;
$barwidth = floor(300/$numoptions);
?>

Lets analyze. $xval is the value for the x position of the left-most side of the graph. This value will be manipulated later, but for now, it will be set to 30 (the y-axis's distance from the left hand side of the image, plus 5 pixels for some breathing space). $barwidth determines how wide each bar should be. The 300 used is simply 360 (the length of the x-axis) minus 60 for some more breathing space. This is then divided by the number of vote options. As you may have noticed, a lot of the original values and measurements from the image have been changed to account for "breathing space." This is my term for the space between the bars so that the graph doesn't seem to crowded. Technically, the script would work with out this extra space. But try it, and I assure you will develop a case of claustrophobia.

The actual drawing of the graph, what you started reading this article for. It would be obvious to any programmer that to execute any number of indefinite commands, a loop is required. So if you guessed that we need a loop to draw the bars, pat your self on the back. If not, its okay. My personal favorite loop is the for loop. This script can be rewritten using a while loop, or any other loop that you please, however. Well take this a line or two at a time. The first line is as such:

for ($i=0;$i<=($numoptions-1);$i++)
{
?>

The setting of $i to 0 and subtracting one from $numoptions may seem pointless, but its necessary. The mysql_result function, that will be used later, reads rows from MySQL tables starting at row 0, not row 1. Subtracting one from $numoptions is done so that the loop doesn't overshoot its target number, and try to draw an extra bar that doesn't exist. This might be a bit confusing, but the next two line will make things a bit clearer:

$voteoption = mysql_result($optionsquery,$i,'name');
$votevalue = mysql_result($optionsquery,$i,'value');
?>

Since each loop deals with and draws the bar for a different voting option, we need to know which option were dealing with. $voteoption takes on the value of the actual name (the 'name' column of the vote options table), and $votevalue stores the HTML value (the 'value' column of the same table), of the current vote option. The $optionsquery query was the query used earlier to get the total number of vote options. Keep in mind that this loop deals with one vote option at a time, the option that is on row $i. Since $i is incremented after each loop, all vote options are dealt with. We already know how many people have voted overall ($numvotes), we need to know how many people have voted for the current option specifically.

ake a look at these two lines:

$currentnumquery = mysql_query("SELECT * FROM poll WHERE vote='$votevalue'");
$currentnum = mysql_num_rows($currentnumquery);
?>

The query in the first line is almost identical to the one used to find how many total votes there were ("SELECT * FROM poll"). The only exception is that there was a bit of discrimination here: only the votes with their 'HTML value' column matching the HTML value of the current vote option will be selected. This means that only the people who voted for the current vote option will be counted. This way, we get the number of votes for the current vote option, and we store it in the variable $currentnum using mysql_num_rows. Now that we have the total number of votes and the number of votes for our current vote option, we no longer need any outside data. The rest of the calculations will take place within the script. The graph in this example uses percents, not actual values. Were going to need to calculate these percents before the drawing begins. As usual, I put up two lines for analysis:

$per = floor(($currentnum/$numvotes)*184);
$rper = floor(($currentnum/$numvotes)*100);
?>

The two calculations are identical, but with one difference: one is in relation to 184, and the other to 100. $per is used to determine how high the current graph should be. It is multiplied by 184, the height of the y-axis. $rper (real percent) is simply the actual percentage that will be used only for display later on. We have determined, called, calculated and processed all the necessary data. Now its time to actually get results (the graph is still quite empty if you haven't checked).

imagefilledrectangle ($im, $xval, (200-$per), ($xval+$barwidth), 200, $red);
imagerectangle ($im, $xval, (200-$per), ($xval+$barwidth), 200, $black);
?>

It may look like a lot, but the coordinates in both functions are the same. Each coordinate is an expression based off of all the calculations we just went through (except the last one). $xval is the current value for the left most side of the bar (essentially, the x coordinate for the upper-left hand corner) that will be incremented by $barwidth+10 later on. 200-$per uses $per, the height of the bar. Since we have the actual whole height of the bar, we are forced to work from down up, i.e. from 200 (the bottom-most line in the actual graph, but necessarily in the actual image) to the top, 0. Hence, we minus $per. The second x value is the same as the first, but with $barwidth added to it. The last value is the easiest: its 200 no mater what happens. 200 is the bottom-most point on the graph, and the last y value (the lowest y value) has to be on it. Mind you, if your lowest point is not 200, don't put and expect it to work :).

Let's take a look at our code so far, as its enough to draw a full graph:

header ("Content-type: image/png");
$im = imagecreatefrompng ("graphtemp.png");
$red = imagecolorallocate ($im, 255, 0, 0);
$black = imagecolorallocate ($im, 0, 0, 0);
mysql_connect("localhost", "user", "password");
mysql_query("USE database");
$optionsquery = mysql_query("SELECT * FROM voteoptions");
$numoptions = mysql_num_rows($optionsquery);
$pollquery = mysql_query("SELECT * FROM poll");
$numvotes = mysql_num_rows($pollquery);
$xval = 30;
$barwidth = floor(300/$numoptions);
for ($i=0;$i<=($numoptions-1);$i++)
{
    $voteoption = mysql_result($optionsquery,$i,'name');
    $votevalue = mysql_result($optionsquery,$i,'value');
    $currentnumquery = mysql_query("SELECT * FROM poll WHERE vote='$votevalue'");
    $currentnum = mysql_num_rows($currentnumquery);
    $per = floor(($currentnum/$numvotes)*184);
    $rper = floor(($currentnum/$numvotes)*100);
    imagefilledrectangle ($im, $xval, (200-$per), ($xval+$barwidth), 200, $red);
    imagerectangle ($im, $xval, (200-$per), ($xval+$barwidth), 200, $black);
    $xval+=($barwidth+10)
}
imagepng($im);
?>

Note the addition of the closing brackets and the incrementing of $xval (as I promised). If all went well, you should get a nice red graph. Well, actually, a nice series of red bars. There is no indication as to which bar means what, and how much each bar is. Although nice, this graph is essentially useless. Not to worry, this can be fixed. A few imagestrings will solve all your problems. The most important thing is to label the bars. Take a look at this bit of code:

imagestring ($im, 1, ($xval+($barwidth/2)), 205, $voteoption, $black);
?>

I used font one, its small and practical, but it really doesn't matter. The x coordinate is the current $xval plus half the $barwidth. This is an attempt to center the text, and it works pretty well (it's not that good actually, there are many better ways to center text, but for simplicity, I'm not going to include it). The y coordinate, 205, puts it a little lower than the bottom line. The variable $voteoption is used as the text to be written, and the color $black is used as the, well, color. This line goes right after the imagerectangle, and before the $xval increment. To finish it all off, we'll add text on to of the bar saying what percent of the voters chose this option. The code is as follows:

imagestring ($im, 2, ($xval+($barwidth/2)), ((200-$per)-15), "$rper%", $black);
?>

The last analysis, I promise! The font used doesn't matter, but using a big font (4, 5) might hurt the look and even the readability of your graph. The x coordinate here is the same as the x coordinate in the previous imagestring. The y coordinate uses the same calculation that was used to determine the height of the bar (200-$per) with 15 subtracted from it so the text isn't drawn inside the bar. "$rper%" is the variable $rper with the percent symbol added to it (otherwise, its just a number). Well, that's all the code. Here's the final script:

Conclusion

Dynamic graphs are a great way to combine the power of PHP with MySQL and GDLib. It offers data in an easily readable format to your viewers. This article barley scratches the surface of what is possible with dynamic graphing, let alone GDLib. One could make bars of alternating colors, or even color-code them from highest to lowest. Different types of graphs can be made just as easily: line graphs, pie graphs etc. With a little creativity, you can even pull off a pseudo-3d effect using imageline. The possibilities are endless, and the only way to ensure that you are getting the most out of GDLib is to read through all the functions in the PHP manual. I hope you enjoyed and benefited from this article as much as I would have had some one wrote it before me :). Good luck, and happy graphing.