thelee Posted December 2, 2012 Share Posted December 2, 2012 im using this code to show total sales based on each month <?php include("bookingconnect.php"); $sql="SELECT * FROM `boooking`"; $result=mysql_query($sql) or die("Cannot execute sql."); $query = "SELECT month, SUM(admission_price) FROM boooking GROUP BY month"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo "Total ". $row['month']. " = RM ". $row['SUM(admission_price)']; echo "<br />"; } ?> and i have found a coding to build a graph. here is it <?php # ------- The graph values in the form of associative array $values=array( "Jan" => 120, "Feb" => 130, "Mar" => 215, "Apr" => 81, "May" => 310, "Jun" => 110, "Jul" => 190, "Aug" => 175, "Sep" => 390, "Oct" => 286, "Nov" => 150, "Dec" => 196 ); $img_width=450; $img_height=300; $margins=20; # ---- Find the size of graph by substracting the size of borders $graph_width=$img_width - $margins * 2; $graph_height=$img_height - $margins * 2; $img=imagecreate($img_width,$img_height); $bar_width=20; $total_bars=count($values); $gap= ($graph_width- $total_bars * $bar_width ) / ($total_bars +1); # ------- Define Colors ---------------- $bar_color=imagecolorallocate($img,0,64,128); $background_color=imagecolorallocate($img,240,240,255); $border_color=imagecolorallocate($img,200,200,200); $line_color=imagecolorallocate($img,220,220,220); # ------ Create the border around the graph ------ 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 is required to adjust the scale ------- $max_value=max($values); $ratio= $graph_height/$max_value; # -------- Create scale and draw horizontal lines -------- $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); imagestring($img,0,5,$y-5,$v,$bar_color); } # ----------- Draw the bars here ------ for($i=0;$i< $total_bars; $i++){ # ------ Extract key and value pair from the current pointer position 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; imagestring($img,0,$x1+3,$y1-10,$value,$bar_color); imagestring($img,0,$x1+3,$img_height-15,$key,$bar_color); imagefilledrectangle($img,$x1,$y1,$x2,$y2,$bar_color); } header("Content-type:image/png"); imagepng($img); ?> can anyone help me, how to make the graph value based on the total sales for each month ? Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/ Share on other sites More sharing options...
Barand Posted December 3, 2012 Share Posted December 3, 2012 You have the query, you have the graph code. What is the problem? Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397143 Share on other sites More sharing options...
thelee Posted December 3, 2012 Author Share Posted December 3, 2012 i dont know how to make it,can u teach me ? Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397215 Share on other sites More sharing options...
Barand Posted December 3, 2012 Share Posted December 3, 2012 (edited) What does your data look like? What is the table structure? Edited December 3, 2012 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397225 Share on other sites More sharing options...
Barand Posted December 3, 2012 Share Posted December 3, 2012 I've prepared an example using my table of daily rainfall amounts as I had no idea of how you are storing your dates. In the HTML I created an IMG tag where the SRC is the php file creating the dynamic graph image and pass the year in the querystring. HTML <html> <head> <title>Sample Graph</title> </head> <body> <h3>Rainfall 2011</h3> <img src='graph1.php?year=2011' alt="Rainfall chart" /> </body> </html> In graph1.php I get the year and query the rainfall table to get monthly totals for that year and store the values in the $values array with the month names as the keys and the totals as the values. GRAPH1.PHP <?php include("testDBconnect.php"); $year = intval($_GET['year']); $sql = "SELECT DATE_FORMAT(raindate, '%b') as mname, MONTH(raindate) as mth, SUM(rain) as totrain FROM rainfall WHERE YEAR(raindate) = $year GROUP BY mth"; $values = array(); /************************ * create $values array * from the query *************************/ $res = $mysqli->query($sql) or die(mysqli_error ($mysqli)); while ($row = $res->fetch_assoc()) { $values[$row['mname']] = $row['totrain']; } $img_width=450; $img_height=300; $margins=20; # ---- Find the size of graph by substracting the size of borders $graph_width=$img_width - $margins * 2; $graph_height=$img_height - $margins * 2; $img=imagecreate($img_width,$img_height); $bar_width=20; $total_bars=count($values); $gap= ($graph_width- $total_bars * $bar_width ) / ($total_bars +1); # ------- Define Colors ---------------- $bar_color=imagecolorallocate($img,0,64,128); $background_color=imagecolorallocate($img,240,240,255); $border_color=imagecolorallocate($img,200,200,200); $line_color=imagecolorallocate($img,220,220,220); # ------ Create the border around the graph ------ 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 is required to adjust the scale ------- $max_value=max($values); $ratio= $graph_height/$max_value; # -------- Create scale and draw horizontal lines -------- $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); imagestring($img,0,5,$y-5,$v,$bar_color); } # ----------- Draw the bars here ------ for($i=0;$i< $total_bars; $i++){ # ------ Extract key and value pair from the current pointer position 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; imagestring($img,0,$x1+3,$y1-10,$value,$bar_color); imagestring($img,0,$x1+3,$img_height-15,$key,$bar_color); imagefilledrectangle($img,$x1,$y1,$x2,$y2,$bar_color); } header("Content-type:image/png"); imagepng($img); imagedestroy($img); ?> Do not forget to destroy the image (imagedestroy()) after outputting. Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397242 Share on other sites More sharing options...
thelee Posted December 3, 2012 Author Share Posted December 3, 2012 here is my table structure. CREATE TABLE `boooking` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `address` varchar(50) NOT NULL, `phone` int(20) NOT NULL, `ic` varchar(20) NOT NULL, `email` varchar(20) NOT NULL, `plate` varchar(20) NOT NULL, `day` varchar(10) NOT NULL, `month` varchar(10) NOT NULL, `years` varchar(10) NOT NULL, `washtime` varchar(20) NOT NULL, `wash` int(20) NOT NULL, `normalpolish` varchar(20) NOT NULL, `specialpolish` varchar(20) NOT NULL, `wax` varchar(20) NOT NULL, `vacuum` varchar(20) NOT NULL, `admission_price` varchar(20) NOT NULL, `referenceno` varchar(20) NOT NULL, `time` varchar(20) NOT NULL, `date` varchar(20) NOT NULL, Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397293 Share on other sites More sharing options...
Barand Posted December 3, 2012 Share Posted December 3, 2012 That's a mess!. You seem over-fond of varchar, yet "phone" which should be a varchar, you have as integer! price should be DECIMAL. Dates and times should be DATE/TIME/DATETIME. Not sure about your month field - is it a month number or month name? If it's name, how do you sort them. Are the day, month and years fields the same as in your date field?. If so,why hold both? Can you list a couple of sample records? Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397296 Share on other sites More sharing options...
thelee Posted December 3, 2012 Author Share Posted December 3, 2012 sorry.im still noob. the day month year table is a date for appointment date because i'm using list view form for it. the month value in list form is january, february and so on. year is 2000,2001,2003 and so on and day is 1,2,3,4 and so on. admission_price is the total price of the services. i want to to build the graph based on month and admission_price Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397299 Share on other sites More sharing options...
Barand Posted December 3, 2012 Share Posted December 3, 2012 (edited) It doesn't get any better. The date column has no relation to the day/month/years columns. If you group by your month column then you will get the months in this order +-------+ | mname | +-------+ | Apr | | Aug | | Dec | | Feb | | Jan | | Jul | | Jun | | Mar | | May | | Nov | | Oct | | Sep | +-------+ I think you have a bit of table redesign to do before you can do much useful with that data. Edited December 3, 2012 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397301 Share on other sites More sharing options...
thelee Posted December 3, 2012 Author Share Posted December 3, 2012 oh thanks a lot guru Quote Link to comment https://forums.phpfreaks.com/topic/271505-how-to-make-the-graph-value-based-on-total-sales-for-each-month/#findComment-1397302 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.