CODEWITHSUNDEEP
phpmysqlarrays
andreaem
andreaem

Reputation: 1655

PHP fetch data from mySQL only last 7 days

I got a chart that display last 7 days site visitors.

This chart will be populated using these last days data, but at the time I get only the first 7 days stored in database, I must retrieve the current week data getting the last 6 days and the current.

The table structure is like:

|| ID ||     dt     ||   ip    ||
|| 1  || 2016-06-15 || 0.0.0.0 ||
|| 2  || 2016-06-15 || 1.0.1.0 ||
|| 3  || 2016-06-15 || 2.1.0.1 ||
|| 4  || 2016-06-16 || 0.1.0.1 ||
|| 5  || 2016-06-16 || 2.1.2.0 ||

I'm using this line of code to get the data but seems not working

$siteViewsDaily = $DB_CON->query("SELECT count(*), date(dt) FROM statistics GROUP BY date(dt) - INTERVAL 7 DAY")->fetchAll();

How can I get data in array like:

[dt] => [ip] => "n"

Thanks a lot to all who can help.

Upvotes: 0

Views: 2489

Answers (2)

user4962466
user4962466

Reputation:

just do it in sql side with ADDDATE() function

$sql = 'SELECT count(*), date(dt) as d FROM statistics WHERE d BETWEEN ADDDATE(NOW(),-7) AND NOW() GROUP BY d';

Upvotes: 1

Viktor Koncsek
Viktor Koncsek

Reputation: 574

$fromDate = date("Y-m-d",strtotime("-7 days"));

And in the SQL: WHERE dt > '$fromDate'

Upvotes: 0

Related Questions