2

I want to collect data from SQL from the last 30 days. This code shows data of all time

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able GROUP BY country');

this shows nothing, instead of showing last 30 days data.

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able GROUP BY country WHERE dtime > DATE_SUB(CURDATE(), INTERVAL 30 DAY)');

All SQL entries were made within the last 30 days. also tried

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able WHERE dtime > DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY country');

What am I doing wrong here ? database dtime section

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Adhithyan VN
  • 21
  • 4
  • https://stackoverflow.com/questions/2041575/mysql-query-records-between-today-and-last-30-days for working techniques – ADyson Apr 28 '22 at 11:43
  • Your second try looks ok. (The WHERE clause in your first try is on the wrong place.) Did you try it with phpmyadmin or some other MySQL client? Also, generally it's smart to use `>=` rather than `>` for the beginning of a date range. – O. Jones Apr 28 '22 at 11:44
  • I rolled back some edits that removed the php code surrounding the Sql code in the question. Removing the php code obscures the OQ's intent. – O. Jones Apr 28 '22 at 11:46
  • @O.Jones but the PHP code is not relevant to the actual problem at hand, which is entirely self-contained within the SQL. Would you have them show how they output the data into a HTML view, as well? It's supposed to be a [mre] of the issue. The PHP in this case is not affecting the issue. :-) – ADyson Apr 28 '22 at 11:57

1 Answers1

1

Try this:

SELECT country, COUNT(*) 
FROM data_able 
WHERE dtime > DATE_ADD(CURDATE(), INTERVAL -30 DAY)
GROUP BY country

The second doesn't work because you put GROUP BY before WHERE statement, which is not correct SQL order.

As for why the third code doesn't work, I'm not sure, but if I had to guess, it has something to do with that DATE_SUB statement.

Dev Minty
  • 64
  • 6