I am new to MySQL and trying to display items from my database using items that have a date from today plus the next days days (like a calendar) and it’s only showing items after 7 days.
An example of the output is;
2022-12-28 11:02:00 (Showing after todays date plus the next 7 days)
My code is;
$stmt = $pdo->query("SELECT * FROM reminders_sc WHERE reminder_date > CURDATE() + INTERVAL 7 DAY");
I also tried;
$now = date("Y-m-d h:i:sa");
$stmt = $pdo->prepare('SELECT * FROM care_plan_review where reminder_date > ? order by id desc');
$stmt->execute([$now]);
This just showed everything in the database 🙁
Database is colunm datetime format.
I have looked on SO and can’t figure what I am doing wrong.
>Solution :
You don’t want "greater than", you want to use "between". The > (greater than) means the date must be beyond 7 days. Using between you can check that the date is between now (today) and 7 days.
reminder_date between now() and now() + INTERVAL 7 DAY
Additional note:
Y-m-d h:i:sa
would not give you a MySQL datetime value. You’d want Y-m-d H:i:s. Also if your PHP and MySQL have different timezone settings this can result in different data returns.