CODEWITHSUNDEEP
phpmysql
Saifullah Alam
Saifullah Alam

Reputation: 301

Can't figure out how to query only for last 30 days result in mysql

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

Upvotes: 0

Views: 79

Answers (3)

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

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.

Upvotes: 0

Jay Blanchard
Jay Blanchard

Reputation: 34406

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

Upvotes: 3

bob_1982
bob_1982

Reputation: 733

   $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';

Upvotes: 0

Related Questions