I have searched this site for what i need but i coulldnt find anything to my specific. I have a slight difficulty about pulling data between two dates from mysql. I know the system how to write the code to filter data between two dates but the issue i am having is these two dates are in different months. Let me explain further;
I have working code to pull the date between the two dates – currently i have to set them manually.
what i need is to calculate the two dates ($from
& $till
) according to the current date. $from
should always be the 25th of one month (00:00) and $till
should be the 24th of the next month (23:59), while the current date should always be in between.
Examples:
on january 7th 2020, $from
should be december 25th 2019, 00:00 and $till
should be january 24th 2020 (23:59)
on january 25th 2020, $from
should be january 25th 2020, 00:00 and $till
should be february 24th 2020 (23:59)
Currently i have the following code and i set the dates manually:
<?php $from = strtotime('25/12/2019 00:00'); $till = strtotime('24/01/2020 23:59'); $stmt = $con -> prepare("SELECT SUM(`amount`) as `total` FROM `income` WHERE user_id = ? && time >= ? && time <= ?"); $stmt -> bind_param('iii', $user_id, $from, $till); $stmt -> execute(); $stmt -> store_result(); $stmt -> bind_result($total_income); $stmt -> fetch(); ?>
So is there any way to set these dates automatically according to the current time?
Advertisement
Answer
To set the date automatically (according to the current date), you could do something like this:
//default: from 25th of last month to 24th of current month $from = strtotime('-1 month',strtotime(date('m/25/Y 00:00:00'))); $till = strtotime(date('m/24/Y 23:59:59')); if (intval(date('d')) > 24) { //current day is bigger than 25 - shift it one month $from = strtotime('+1 month',$from); $till = strtotime('+1 month',$till); }
this would use the 25th of last month as default start date and the 24th of the current month as default end date.
if the current day is already the 25th of the month (or later), it will shift both dates 1 month.
Also: If you use slashes in the date, keep in mind that the format ist month / day / year, not day / month / year