Well we will just get right to it, and set up the MySQL database to hold the statistics. So save this as create_table.php.
 |
|
 |
Code Breakdown:
mysql_connect('localhost', 'USERNAME', 'PASSWORD'); - To most of you who have used MySQL before, I am sure you are quite familiar with this function (it is required to use a MySQL database in PHP). So change "USERNAME" and "PASSWORD" to a MySQL log in that has full privileges to the MySQL database you made.
mysql_select_db('DATABASE'); - Another function that is commonly used, just change "DATABASE" to the name of the database you made to hold the table.
mysql_query("CREATE TABLE `traffic` ( - This starts the query using the PHP/MySQL function and then starts the syntax for creating the table, which will be called "traffic".
`day` text NOT NULL, - This will create the field "day" it will hold a fairly long encoded string of an array of the all of the selected month's days and hits.
`month` char(9) NOT NULL, - This will hold the selected month for the traffic days and statistics that go with it.
`year` int(4) NOT NULL - The year that goes with the month.
Now we have can index our statistics, the next step is to actually gather them now. So put this next chunk of code some where in an already existing PHP page on your site that gets visited (if this is that case then, remove the 2 MySQL functions at the beginning if there is already an active MySQL connection), or since it is just a tutorial save it as it's own page and just refresh it to count each hit.
|
|
|
Code Breakdown:
$result = mysql_query("SELECT * FROM `traffic` WHERE `month`='".date("F")."' AND `year`='".date("Y")."'"); - This will the entry from the database for the traffic that has been tracked for the current month. (if there is a record)
$number_days_month = array("January" => 31, - This array will contain the amount of days in each month, as you can see for January it will use the full name of the month as the index and the amount of days as the value. (I won't explain the rest of the values because it follows the same pattern just for the different months)
if(mysql_num_rows($result) == 0) { - This will check if we already have a record for the traffic this month in the database, based on the query we did at the begining. The function 'mysql_num_rows()' returns the number of rows return from the query (hence the name), in this case it will be either 0 or 1.
for($i=1;$i!=($number_days_month[date("F")]+1);$i++) { - This for loop will help create the array that will hold all of the current month's days and hits. It will loop for each day in the month.
if(date("j") == $i) { - This checks if the day we are at in our loop is equal to the current day it is on script execution, since 'date("j")' will return the current day of the month in a number format, and if we are at the current day of the month then we might as well set it to 1 hit while we are making the array.
$ser_hits = serialize($hits); - Since we can't just put an array in the database as it is, we have to serialize it. The function serialize will convert the array into a storable value.
mysql_query("INSERT INTO `traffic`(`day`, `month`, `year`) VALUES('".$ser_hits."', '".date("F")."', '".date("Y")."')"); - Now we have created the record of that traffic for this month, so we insert the serialized array of the traffic.
$data = mysql_fetch_array(mysql_query("SELECT * FROM `traffic` WHERE `month`='".date("F")."' AND `year`='".date("Y")."'")); - The traffic record for this month does exist, so we select the serialized array of the days and fetch the array and assign it to a variable.
$hits = unserialize($ser_hits); - Since we put the array in the database in a serialized format, we have to unserialize it so we can use it again, so we use the 'unserialize()' function.
$hits[date("j")]++; - Increment the amount of hits for the current day by 1.
$ser_hits = serialize($hits); - We don't need to do anything else, so let's serialize it so we can put it back in the database.
mysql_query("UPDATE `traffic` SET `day`='".$ser_hits."' WHERE `month`='".date("F")."' AND `year`='".date("Y")."'"); - Update the the current month's traffic record with the new one.
Now we can handle and get our statistics, so now on to the actual reason we made the tutorial, the graph. The following chunk of code will simply be that page that generates the graph, it will recieve the $_GET variables from a page where you can select the month and year you want to view.
Save this as graph.php
|
|
|
Code Breakdown:
header("Content-type: image/png"); - Since we will be showing the content in the form of an image (rather than the usual text/html) we must set the content-type using the header function.
mysql_connect('localhost', 'USERNAME', 'PASSWORD'); - We need to use the database so remember to change this just as you did for the other scripts, also change the database on the line below it.
$selected_year = addslashes($_GET['year']); - When we show the image we will pass on $_GET variables so we assign it to a local variable and use "addslashes()" just to be careful. You can also see that I did the same thing on the line below for the $selected_month variable.
$im = @imagecreatetruecolor(500, 315) or die("Cannot Initialize new GD image stream"); - This function will create a black image (or the script will stop and say that GD is not enabled or there is a problem) and we assign an identifier to the variable "$im", in this case it will be a 500 x 315 image.
$actual_graph_height = 260; - You may be thinking that we just created an image that has it's height set at 315px but this is only 260px. Well this is the actual height of the area that our data will be displayed in, since the information won't take up the entire image because within the image we have to have labels, titles etc.
$actual_graph_width = 460; - This is the same case as the variable above it, just this is for the width.
$current_month_days = $number_days_month[$selected_month]; - This will get us the number of days in the selected month. (using the array which was also used in traffic.php)
$space_between_x_label = $actual_graph_width/$current_month_days; - This will be the width between each label and marking that goes horizonatally, meaning that it is the number of pixels that will be between each day. (it will be some long decimal number but there is no need to round it, since it would be unnecessary and this way we can be more precise)
foreach($hits as $value) { - Since we unserialized our record of hits for the selected month, we use the "foreach" loop, it will go through each day and assign the number of hits for that day to the variable "$value".
if($value > $highest) { - As you can see from everything that is going on, we are just figuring out what the greatest number of hits we have had in the selected months.
$space_between_hit = $actual_graph_height/$highest; - We figured out the spacing for the labels along the labels on the x axis, so this figures out the spacing for the labels along the y axis.
$line_color = imagecolorallocate($im, 255, 0, 0); - Using the "imagecolorallocate()" function we make an identifier to use a color in our image, the function uses the RGB format (red, green, blue) in this case we are making the color entirely red.
$prev_y_plot = 270; - Throughout the loop we will track the previous point (in pixels) to where we put it in x and y coordinates. By default the Y coordinate is 270 because that is that is where the line for the X axis will be. (we have to subtract from the coordinate to go up on the graph since the top left corner of the image is 0, 0)
$prev_x_plot = 30; - Even though we add on to the X coordinate to go over, we start at 30 because the line for Y axis is 30 over.
while($x < ($current_month_days+1)) { - This will loop for each day in the month so we can add the lines for the specific day.
$y_plot = (270 - 1); - The amount of hits for this day is 0, so we are setting the point in which we plot the line vertically, just 1 pixel above the Y axis. (so we can still see the line)
$x_plot = (10 + ($y * $space_between_x_label)); - Since where it is horizontally, it doesn't matter how many hits we still would put in the in the same spot. (along the X axis)
$y_plot = (270 - ($hits[$x] * $space_between_hit)); - The day has atleast 1 hit or more, so it multiplies the number of hits that day times the space (in pixels) between each hit then subtract it from from the position of the Y axis. (since it will go up when we subtract pixels)
imageline($im, $prev_x_plot, $prev_y_plot, $x_plot, $y_plot, $line_color); - Now we actually plot the line, we make it start at the previous coordinates and we finish it at the new coordinates.
imagestring($im, 1, (20 + ($i * $space_between_x_label)), 280, $i, $x_label_color); - As you can see this is in the same loop we used before (that loops through each day of the month), so each time it loops this function puts the number of the day at it's appropriate spot along the X axis.
if($highest > 10) { - We check if the highest amount of hits for the current month is greater then 10, because on this graph we will only show 10 numbers along the Y axis. (10%, 20%, 30%... right up to 100% of the highest amount of hits). So if it is less then 10 then we wouldn't get full numbers and you can't have half a hit.
while($i < 11) { - This will loop 10 times, it says 11 because $i is starting at 1 rather than 0.
imagestring($im, 1, 13, (270 - ($actual_graph_height*($i/10))), round(($highest*($i/10))), $y_label_color); - This will write an amount of hits along the Y axis, as you can see it divides the current loop number by 10 (so the first loop would be 0.10) and then multiply that by the graph height which gives us 10% of the graph height. We do the same thing for the amount of hits we show. (except multiply the percentage with the height amount of hits)
imagestring($im, 1, 13, (270 - ($actual_graph_height*($i/($highest)))), round(($highest*($i/($highest)))), $y_label_color); - This basically does the same thing except since the highest point is 10 or less, it won't be like 10%, 20%, 30%... right to 100% like I said before (unless it is 10). Instead it will find the percent itself, image if the highest number of hits was 5 then it would be like 1/5 (20%), 2/5 (40%). So it will show 1 through to 5 evenly spaces out along the Y axis.
imagestring($im, 3, 220, 295, "Day", $text_color); - This just labels the X axis as the current day.
imagestringup($im, 3, 0, 150, "Hits", $text_color); - This makes a vertical string, that we are using to label the Y axis with.
imageline($im, 30, 280, 30, 20, $line); - The line for our Y axis.
imageline($im, 20, 270, 490, 270, $line); - The line for our X axis.
imagepng($im); - This outputs the PNG image (in other words, your graph)
imagedestroy($im); - This destroys our work of art, until next time we run the script.
This description may have been hard to understand or confusing but that won't really matter since it is mostly just figuring our sizes and what not, so I suggest you don't try to change the size of the graph unless you completely understand everything in this script.
Of course just running this script will not work, we need to make a script that will allow you to pick the month and year and show the statistics.
This is a very simple script, save it as stats.php
|
|
|
Code Breakdown:
if(isset($_POST['submit'])) { - This checks if the form has been submitted. (since it submits and processes all in one script)
$split = explode('-', $_POST['month_year']); - The form will submit something with the year and month in it just seperated with a "-", so we explode() it to get those values.
echo '<img src="graph.php?year='.$split[0].'&month='.$split[1].'" /><br /><br />'; - This will show the graph with the values for the year and the month passed on to it via the URL.
$result = mysql_query("SELECT * FROM `traffic`"); - This selects all of statistics in our table.
while($row = mysql_fetch_array($result)) { - This loops through all of the statistics.
if(isset($_POST['month_year'])) { - This will check if the script is being executed after someone has already submitted the year and month they want to view.
if($_POST['month_year'] == $row['year'].'-'.$row['month']) { - This will check if the year and month we are about to add to the drop down has already been selected, if it has then we make it the default in the drop down. So they can tell which month they are viewing. (as you can see in the echo's below, we just add "selected" to make it the default.
Now if you followed it correctly you should be able to select a month and year with the script stats.php.
Here is a static example of the graph (I put in random stats):
discuss this topic to forum
