Here is a simple script to create a histogram using mysql, php and gd. The key point to remember here is to create a “SELECT” only account for extracting the records. Do not provide any more privileges than you have to provide. Please comment if you have a different way to perform the same task.
Here is the final image followed by the code:
Here are the contents of the pbimages_histogram.php file
#Let us get our database from a mysql database #First we need to connect to the database or die trying # I would create an account with read-only or select only access $lottery_con = mysql_connect('localhost','select_lottery','1234try'); if (!$lottery_con){ die ('Could not connect to lottery'); } mysql_select_db("lottery") or die (mysql_error()); #Let us create our first query $wb1_match_pb = mysql_query("SELECT count(wb1) FROM lottery where wb1 = pb"); $wb2_match_pb = mysql_query("SELECT count(wb2) FROM lottery where wb2 = pb"); $wb3_match_pb = mysql_query("SELECT count(wb3) FROM lottery where wb3 = pb"); $wb4_match_pb = mysql_query("SELECT count(wb4) FROM lottery where wb4 = pb"); $wb5_match_pb = mysql_query("SELECT count(wb5) FROM lottery where wb5 = pb"); $tot_record = mysql_query("SELECT count(pb) FROM lottery"); #As soon as you connect make sure you create the close connection $cntwb1 = mysql_fetch_array($wb1_match_pb); $cntwb2 = mysql_fetch_array($wb2_match_pb); $cntwb3 = mysql_fetch_array($wb3_match_pb); $cntwb4 = mysql_fetch_array($wb4_match_pb); $cntwb5 = mysql_fetch_array($wb5_match_pb); $cntwbT = $cntwb1[0] + $cntwb2[0] + $cntwb3[0] + $cntwb4[0] + $cntwb5[0]; $cnttr = mysql_fetch_array($tot_record); mysql_close($lottery_con); #Let us create the histogram $pb_histogram_image = imagecreate(250,215) or die('Cannot Initialize new GD image stream'); $pb_text = imagecolorallocate($pb_histogram_image,0,0,0); $blue = imagecolorallocate($pb_histogram_image, 0, 0, 255); $black = imagecolorallocate($pb_histogram_image,0,0,0); $red = imagecolorallocate($pb_histogram_image, 255, 0, 0); $green = imagecolorallocate($pb_histogram_image,0,255,0); $gray = imagecolorallocate($pb_histogram_image,150,150,150); $orange = imagecolorallocate($pb_histogram_image,255,127,0); $coral = imagecolorallocate($pb_histogram_image,240,128,128); $white = imagecolorallocate($pb_histogram_image, 255, 255, 255); imagefilltoborder($pb_histogram_image,0,0,$black,$white); imageline($pb_histogram_image,0,0,0,250,$black); imageline($pb_histogram_image,0,0,250,0,$black); imageline($pb_histogram_image,249,0,249,213,$black); imageline($pb_histogram_image,0,214,250,214,$black); #Let us add the counts imagestring($pb_histogram_image,2,15,200,"wb1",$black); imagestring($pb_histogram_image,2,60,200,"wb2",$black); imagestring($pb_histogram_image,2,95,200 ,"wb3",$black); imagestring($pb_histogram_image,2,140,200,"wb4",$black); imagestring($pb_histogram_image,2,180,200 ,"wb5",$black); imagestring($pb_histogram_image,2,213,200,"wb Tot",$black); imagestring($pb_histogram_image,3,10,20," Histogram of white balls",$black); imagestring($pb_histogram_image,3,10,35," matching powerball",$black); imagestring($pb_histogram_image,3,10,50," Records: " . $cnttr[0],$black); imagestring($pb_histogram_image,2,15,200 - $cntwb1[0] - 20, $cntwb1[0],$black); imagestring($pb_histogram_image,2,60,200 - $cntwb2[0] - 20, $cntwb2[0],$black); imagestring($pb_histogram_image,2,95,200 - $cntwb3[0] - 20, $cntwb3[0],$black); imagestring($pb_histogram_image,2,140,200 - $cntwb4[0] - 20, $cntwb4[0],$black); imagestring($pb_histogram_image,2,180,200 - $cntwb5[0] - 20, $cntwb5[0],$black); imagestring($pb_histogram_image,2,220,200 - $cntwbT - 20, $cntwbT,$black); #Let us make the bars imagefilledrectangle($pb_histogram_image,5,200,40,200 - $cntwb1[0],$blue); imagefilledrectangle($pb_histogram_image,45,200,80,200 - $cntwb2[0],$red); imagefilledrectangle($pb_histogram_image,85,200,120,200 - $cntwb3[0],$green); imagefilledrectangle($pb_histogram_image,125,200,155,200 - $cntwb4[0],$gray); imagefilledrectangle($pb_histogram_image,165,200,205,200 - $cntwb5[0],$orange); imagefilledrectangle($pb_histogram_image,210,200,245,200 - $cntwbT,$coral); header ('Content-type: image/png'); imagepng($pb_histogram_image); imagedestroy($pb_histogram_image);
Let me know what you think and as always please check out the offerings of my sponsors.