Plotting line graph by using data from MySQL table using PHP GD SupportWe will try to learn how to draw ( or plot ) line graph using gd library and taking data from mysql table. Before starting this please read how to draw graphics lines tutorial.
Reading data from the mysql tableTo use line graph we are using data from mysql table. We will not discuss much on how to get data and there are many tutorials available here on how to connect and how to display data from the table. For our example we have taken table where monthly sales figure is stored. The table has two columns, one stores the month name and other stores sales figure of that month. We will plot a line graph taking the sales figure from the table. We have 12 rows for twelve months and data for sales figure for that month. You can download the database dump files at the end of this tutorial.
Adjusting the Coordinates for the line graphTo plot the line graph we have to adjust the coordinates according to our data. In our graphics line tutorial we have learned that top left coordinate is 0,0 that mean horizontal axis known as X coordinate states from left edge of the screen and vertical axis known as Y coordinate starts from top line of the screen. To move to words right we have to increase the X coordinate value. As we have twelve months so to plot each month value we have to move fix distance horizontally so we will add some fixed width to move to next month. Let us call this horizontal gap as $x_gap. We have twelve months so the graph width has to twelve times of this gap and will keep some left and right side gap. So we will take the total width of the graph as 13 times of the variable $x_gap. Here is the code
Here are some more points to be considered while drawing the graph.
One important point here is our zero on vertical axis starts from top. So if we want to plot the March month sale of 140 in the graph, then we have to subtract 140 from total height of the graph. So we will get the Y coordinate on the graph by subtracting the sales value for the month from the maximum height of the graph.
As we have to draw lines between two coordinates, we always have to store the previous point in memory and draw the line from previous point (memory) and the current value. So when we are reading data for the month of Sep we have to draw a line from Aug point to Sep point. After drawing the line from Aug to Sep we have to keep Sep sales figure in memory to draw line from Sep to Oct. This is achieved by shifting $x2, $y2 data to $x1, $y1 after drawing the line.
The first data should not start from the left most part of the image, or we can't start the image from $x1=0 to $x2=0 so we will set a flag at the beginning of the while loop and on the first time looping we will change again. This if condition will stop drawing the line for the first time inside the while loop.
Here is the output of this graph
Here is the code of the graph
Download this script with sql dump to create your table.
This much is not enough for drawing line graphs. We have to take care of so many points. We can draw grid in the graph, write text on X and Y axis to mark different values. We can draw two line graphs with different coordinates in the same image, this is used for comparison. You can correlate sales to inventory level. We will try to learn all these in our next tutorial.
Line Chart Different types of graphs by using Google Charts