PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: DATE_FORMAT
DATE_FORMAT - phponwebsites.com
Showing posts with label DATE_FORMAT. Show all posts

29 Jul 2014

Generate Yearly Statistics dynamically using PHP and MySQL

                       All of you know to create daily statistics and also monthly statistics using php and mysql. You can also create yearly statistics using php and mysql. The php and mysql allows user to create charts on daily, monthly and yearly charts between two years. Due to this yearly stats, you can find how much your website got viewed and also find comparison of number of visits.

Yearly stats using php and mysql:


                      You need to follow the below steps to create yearly stats using php.

1.Create table in mysql database:


             First you have to create table for update values.

                  Create table stats(view int(10),date date)

          Now the table is created with fields view and date.

2.Counter code in php:

   
                  Then you add the number of views to mysql table using php. Add the following code to header of each page in your website. Because header is in all pages. So you can easily calculate the number of views of your website. Due to this code, you can find total number views of your website per day.


<?php
    mysql_connect('localhost','root','')  or die(mysql_error());
    mysql_select_db('new')  or die(mysql_error());
    $q=mysql_query('select * from stats where date IN (CURDATE())')  or die(mysql_error());
    $n=mysql_num_rows($q);
    if($n==0)
    {
     mysql_query("insert into stats values(1,CURDATE())");
    }
    else
    {
     mysql_query("update stats set view=view+1 where date IN (CURDATE())");
    }
?>

where,
      - select * from stats where date IN (CURDATE()) is select the views if present in current date.
      - mysql_num_rows() return the number of rows selected. If it is 0, then value inserted into mysql table. Otherwise values are updated.

3. Stats(chart) using php and mysql:


                    Then you need to create statistics using php and mysql through google charts. There are many types of charts in google. You can choose anything as you want. The php code for create yearly stats:

yearly_stats.php



<html>
<body>
<?php
    mysql_connect('localhost','root','');
    mysql_select_db('new');
?>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {var data = google.visualization.arrayToDataTable([
  <?php
$str=" ['Year', 'Year'] ";
$query="select SUM(view) as vi, DATE_FORMAT( date, '%Y' ) as dat from stats group by DATE_FORMAT(date, '%Y') order by DATE_FORMAT(date, '%Y') "; 
$result=mysql_query($query);
while($rows=mysql_fetch_array($result,MYSQL_BOTH)){
$str =$str . ",['". $rows['dat'] ."'," .$rows['vi'] ."]" ;
}
echo $str;
?>
        ]);

      var options = {
          //title: 'Company Performance',
          hAxis: {title: 'Year', titleTextStyle: {color: 'red'}},
          vAxis: {title: 'Total views', titleTextStyle: {color: '#FF0000'}, maxValue:'5', minValue:'1'},
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>    
   <p style="font-size:20px;">Monthly Stats</p>
   <div id="chart_div" style="width: 400px; height: 200px;"></div>
</body>
</html>

where,
          The following MySQL query is used to find total number of views of your websites per yearly.
        - select SUM(view) as vi, DATE_FORMAT( date, '%Y' ) as dat from stats group by DATE_FORMAT(date, '%Y') order by DATE_FORMAT(date, '%Y') ASC
is select the number of views monthly.

Consider the following example:
    Suppose your table look like this:

Create yearly stats dynamically using PHP and MySQL


Then you'll get output like below:

Create yearly stats dynamically using PHP and MySQL


        Now you can calculate the number of views of your website yearlly.

Related Post:
Create daily statistics using PHP and MySQL
Calculate monthly statistics dynamically using PHP and MySQL

28 Jul 2014

Monthly statistics using php and mysql

                       All of you know to create daily stats using php and mysql. Otherwise visit How to calculate daily statistics using PHP and MySQL. You can also create monthly statistics using php and mysql. The php and mysql allows user to create charts on daily, monthly and charts between two dates.

Monthly stats using php and mysql:


                      You need to follow the below steps to create monthly stats using php.

1.Create table in mysql database:


             First you have to create table for update values.

                  Create table stats(view int(10),date date)

          Now the table is created with fields view and date.

2.Counter code in php:

     
                  Then you add the number of views to mysql table using php. Add the following code to header of each page in your website. Because header is in all pages. So you can easily calculate the number of views of your website.


<?php
    mysql_connect('localhost','root','')  or die(mysql_error());
    mysql_select_db('new')  or die(mysql_error());
    $q=mysql_query('select * from stats where date IN (CURDATE())')  or die(mysql_error());
    $n=mysql_num_rows($q);
    if($n==0)
    {
     mysql_query("insert into stats values(1,CURDATE())");
    }
    else
    {
     mysql_query("update stats set view=view+1 where date IN (CURDATE())");
    }
?>

where,
           
      - select * from stats where date IN (CURDATE()) is select the views if present in current date.
      - mysql_num_rows() return the number of rows selected. If it is 0, then value inserted into mysql table. Otherwise values are updated.

3. Stats(chart) using php and mysql:


                    Then you need to create statistics using php and mysql through google charts. There are many types of charts in google. You can choose anything as you want. The php code for create monthly stats:

monthly_stats.php



<html>
<body>
<?php
    mysql_connect('localhost','root','');
    mysql_select_db('new');
?>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {var data = google.visualization.arrayToDataTable([
  <?php
$str=" ['Month', 'Month'] ";
$query="select SUM(view) as vi, DATE_FORMAT( date, '%M' ) as dat from stats group by DATE_FORMAT(date, '%Y-%M') order by DATE_FORMAT(date, '%Y-%M') ASC"; 
$result=mysql_query($query);
while($rows=mysql_fetch_array($result,MYSQL_BOTH)){
$str =$str . ",['". $rows['dat'] ."'," .$rows['vi'] ."]" ;
}
echo $str;
?>
        ]);

      var options = {
          //title: 'Company Performance',
          hAxis: {title: 'Month', titleTextStyle: {color: 'red'}},
          vAxis: {title: 'Total views', titleTextStyle: {color: '#FF0000'}, maxValue:'5', minValue:'1'},
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>      
   <p style="font-size:20px;">Monthly Stats</p>
   <div id="chart_div" style="width: 400px; height: 200px;"></div>
</body>
</html>

where,  
             The following MySQL query is used to find total number of views of your websites per monthly
        - select SUM(view) as vi, DATE_FORMAT( date, '%M' ) as dat from stats group by DATE_FORMAT(date, '%Y-%M') order by DATE_FORMAT(date, '%Y-%M') ASC
is select the number of views monthly.

Consider the following example:
    Suppose your table look like this:

Monthly statistics using php and mysql


Then you'll get output like below:

Monthly stats using php and mysql

        Now you can calculate the number of views of your website monthly.

Related Post:
Create daily statistics using PHP and MySQL
Create yearly statistics dynamically using PHP and MySQL

27 Jul 2014

Daily statistics using php and mysql

                       All website owners wants to know how many times the site is viewed ?. So they create this details in admin panel of their website. You can create daily statistics using php and mysql. The php and mysql allows user to create charts on daily, monthly and charts between two dates.

Daily stats using php and mysql:


                      You need to follow the below steps to create daily stats using php.

1.Create table in mysql database:


             First you have to create table for update values.

                  Create table stats(view int(10),date date)

          Now the table is created with fields view and date.

2.Counter code in php:


            Then you add the number of views to mysql table using php. Add the following code to header of each page in your website. Because header is in all pages. So you can easily calculate the number of views of your website.


<?php
    mysql_connect('localhost','root','') or die(mysql_error());
    mysql_select_db('new')  or die(mysql_error()); 
    $q=mysql_query('select * from stats where date IN (CURDATE())')  or die(mysql_error());
    $n=mysql_num_rows($q);
    if($n==0)
    {
     mysql_query("insert into stats values(1,CURDATE())");
    }
    else
    {
     mysql_query("update stats set view=view+1 where date IN (CURDATE())");
    }
?>


where,
      - select * from stats where date IN (CURDATE()) is select the views if present in current date.
      - mysql_num_rows() return the number of rows selected. If it is 0, then value inserted into mysql table. Otherwise values are updated.

3. Stats(chart) using php and mysql:


                    Then you need to create statistics using php and mysql through google charts. There are many types of charts in google. You can choose anything as you want. The php code for create stats:

daily_stats.php


<html>
<body>
<?php
    mysql_connect('localhost','root','')  or die(mysql_error());
    mysql_select_db('new') or die(mysql_error());
?>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {var data = google.visualization.arrayToDataTable([
 <?php
$str=" ['Day', 'Day'] ";
$query="select view as vi, DATE_FORMAT( date, '%b-%d' ) as dat from stats order by date ASC";
$result=mysql_query($query)  or die(mysql_error());
while($rows=mysql_fetch_array($result,MYSQL_BOTH)){
$str =$str . ",['". $rows['dat'] ."'," .$rows['vi'] ."]" ;
}
echo $str;
?>
        ]);

      var options = {
          //title: 'Company Performance',
          hAxis: {title: 'Day', titleTextStyle: {color: 'red'}},
          vAxis: {title: 'Total views', titleTextStyle: {color: '#FF0000'}, maxValue:'5', minValue:'1'},
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>        
   <p style="font-size:20px;">Dialy Stats</p>
   <div id="chart_div" style="width: 400px; height: 200px;"></div>
</body>
</html>

where,    
               The following MySQL query is used to find total number of views of your websites per daily
        - select view as vi, DATE_FORMAT( date, '%b-%d' ) as dat from stats order by date ASC
is select the number of views daily.

Consider the following example:
    Suppose your table look like this:

Daily stats using php and mysql


Then you'll get output like below:

Dialy statistics using php and mysql

        Now you can calculate the number of views of your website.

Related Post:
Create monthly statistics using PHP and MySQL
Create yearly statistics dynamically using PHP and MySQL