Jump to content

How To Make The Graph Value Based On Total Sales For Each Month

thelee

By thelee
in PHP Coding Help

 Share

Recommended Posts

thelee Member

thelee

Posted
Posted

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 ?

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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.

Link to comment
Share on other sites
thelee Member

thelee

Posted
  • Author
Posted

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,

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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?

Link to comment
Share on other sites
thelee Member

thelee

Posted
  • Author
Posted

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

 

65106_518115534874532_928370013_n.jpg?oh=8a53944c15963843ef01bbb5b921ae3e&oe=50BF1310&__gda__=1354731237_3930c19d4f8cf7ebfcc689c67de2326f

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (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 by Barand
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.