0

Hello i have this code to count visitors number from distinct ip adrress for all days stored in the table. 

$con = mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("visitorsCount");
$page = $_GET['page']; //Page users visited with their ip
$queryTest = 'SELECT date, count(*),count(distinct ip) FROM `visitors`  where section=\''.$page.'\' group by date order by date';

$result= mysql_query($queryTest);

    $data = array();
    while($ris=mysql_fetch_row($result))
        {           
           $data[$ris[0]]=$ris[1];      // Render Visitor Count
        } 

    print_r($data);

So, Print_r provides me this: Array ( [2015-02-03] => 1 [2015-05-03] => 14 ).

But i want to restrict the query only for last 30 days in $queryTest variable. How i can restrict the query only to show last 30 days records? Please have a look at the sql fiddle for the table structure. table struture

Saifullah Alam
  • 301
  • 2
  • 11
  • 2
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using [PDO](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 06 '15 at 18:43
  • I know. But just for demonstration purpose i mentioned this here. Thanks – Saifullah Alam May 06 '15 at 18:45
  • I was away a bit. I am reading the replies and surely accept the most appropriate answer. Thanks for reminding me. – Saifullah Alam May 06 '15 at 19:44

3 Answers3

3

Do a DATE_SUB() with BETWEEN in the filter statements - 

SELECT date, count(*),count(distinct ip) FROM `visitors`  where section=\''.$page.'\' AND date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() group by date order by date
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • Hi, Thanks. But when i run this query and its prints `Array ( [2015-02-03] => 1 )`. So its pulling Month February record. I meant to pull only **last** 30 days record. For example 6 May to 6 April(as today is 6 May here and it should pull last 30 days result to 6 April. I appreciate your help – Saifullah Alam May 06 '15 at 19:52
  • Okay i am running your updated query. Will come back shortly. – Saifullah Alam May 06 '15 at 19:57
  • Hi, I get an empty array now when i print_r to see the records in the loop. I do have records for last 30 days. Why its returning empty array? – Saifullah Alam May 06 '15 at 20:03
  • Swtich the dates around, I may have gone the wrong direction. See update. – Jay Blanchard May 06 '15 at 21:22
0

You can try this way to calculate ‛30 DAY‛ interval from ‛NOW‛

SELECT date, count(*),count(distinct ip) 
FROM `visitors` where date >= DATE(NOW() - 
INTERVAL 30 DAY)  AND date <= NOW() group by date order by 
date;

You can also use BETWEEN clause on you WHERE condition.

A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • Hi, thanks for the reply. The query should return only last 30 days records. I see its getting February records. I want to restrict the query for only last 30 days. – Saifullah Alam May 06 '15 at 20:07
0
   $dt=strtotime("-30 days")
   $dt=date("Y-m-d H:i:s",$dt);
   $queryTest = 'SELECT date, count(*),count(distinct ip) FROM `visitors`  where section=\''.$page.'\' AND date>=\'' . $dt . '\' group by date order by date';
bob_1982
  • 715
  • 6
  • 16