| Graphing in PHP - Make Your Own Line Graph Using PHP Functions |
 |
In this tutorial I will show you how to make your own line graph using GD functions in PHP, based on traffic statistics for a desired page. So this tutorial will require a MySQL database to hold the information from daily traffic statistics.
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.
 |
<?php
mysql_connect('localhost', 'USERNAME', 'PASSWORD');
mysql_select_db('DATABASE');
mysql_query("CREATE TABLE `traffic` (
`day` text NOT NULL,
`month` char(9) NOT NULL,
`year` int(4) NOT NULL
)");
?>
|
 |
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.
|
<?php
mysql_connect('localhost', 'USERNAME', 'PASSWORD');
mysql_select_db('DATABASE');
$result = mysql_query("SELECT * FROM `traffic` WHERE `month`='".date("F")."' AND `year`='".date("Y")."'");
$number_days_month = array("January" => 31,
"February" => 28,
"March" => 31,
"April" => 30,
"May" => 31,
"June" => 30,
"July" => 31,
"August" => 31,
"September" => 30,
"October" => 31,
"November" => 30,
"December" => 31);
if(mysql_num_rows($result) == 0) {
$hits = array();
for($i=1;$i!=($number_days_month[date("F")]+1);$i++) {
if(date("j") == $i) {
$hits[$i] = 1;
}else{
$hits[$i] = 0;
}
}
$ser_hits = serialize($hits);
mysql_query("INSERT INTO `traffic`(`day`, `month`, `year`) VALUES('".$ser_hits."', '".date("F")."', '".date("Y")."')");
}else{
$data = mysql_fetch_array(mysql_query("SELECT * FROM `traffic` WHERE `month`='".date("F")."' AND `year`='".date("Y")."'"));
$ser_hits = $data['day'];
$hits = unserialize($ser_hits);
$hits[date("j")]++;
$ser_hits = serialize($hits);
mysql_query("UPDATE `traffic` SET `day`='".$ser_hits."' WHERE `month`='".date("F")."' AND `year`='".date("Y")."'");
}
?>
|
|
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.
|
 |
|
Date Added: 23-02-2008
Votes: 2
Rating: 10
Views: 8713
Author: admin
Main Category: PHP
Sub-Category: Images
|
 |
|
|
|
