Get from the database, but only the last 30 days

Question

I use this php to retrieve data from my mysql database:

$wpdb->get_var("select count(*) from " . $wpdb->prefix . "newsletter where status='C'"); ?> 

You may notice that I'm in Wordpress, so I use the $ wpdb variable.

This returns all the data from the table, where status='C' . This all works fine, but I only need to receive data for the last 30 days. I'm not sure mysql stores data about when this record was saved, but I have another column called created on the same line as status . This saves the date in the following format:

2011-10-14 15:33:58

Is there a way to use this to update my code to only recover data from the last month?

All the tips here are greatly appreciated.

Thanks C

Answer 1

MySQL has an adddate () function that you can use to do this :)

 $wpdb->get_var("select count(*) from " . $wpdb->prefix . "newsletter where status='C' and date_format(created,'%Y%m%d')>date_format(adddate(now(),interval -30 day),'%Y%m%d')"); 

This question refers to 30 days ago, but you can replace the “-30 days interval” with “the -1 month interval” or visit dev.mysql where you explained everything.

Hope this helps.

Answer 2

Try in the query:

 select count(*) from " . $wpdb->prefix . "newsletter where status='C' AND DATE(created)>=DATE_SUB(CUR_DATE, INTERVAL 30 DAY) 

Answer 3

  $wpdb->get_var("select count(*) from " . $wpdb->prefix . "newsletter where status='C' AND DATEDIFF(CURDATE(), created) <= 30"); ?> 

B:

DATEDIFF () - returns expr1 - expr2, expressed as a value in days from one date to another. expr1 and expr2 are the date or date and time of the expression. Only parts of the date values ​​are used in the calculation.

AND:

CURDATE () - Current date