I’m trying to get users tracked birthdays to show up one week before and one week after the tracked person’s birthday. But it doesn’t seem to work. The MySQL table for user_birthdays consists of these 4 rows: bid, bname, bdate, buser_id. The bdate is saved with the format YYYY-MM-DD in the database.
$sql = "SELECT * FROM user_birthdays WHERE bdate BETWEEN DATE_FORMAT(CURDATE(), '%m-%d') - INTERVAL 7 DAY AND DATE_FORMAT(CURDATE(), '%m-%d') + INTERVAL 7 DAY AND buser_id='{$_SESSION['id']}'";
$result = mysqli_query($connection, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
Everything works when I’m not using the date conditions.
EDIT: Solved with changing it to:
$sql = "SELECT * FROM user_birthdays WHERE date(concat_ws('-', year(now()), month(bdate), day(bdate))) BETWEEN CURDATE() - INTERVAL 1 WEEK AND CURDATE() + INTERVAL 1 WEEK AND buser_id='{$_SESSION['id']}'";
>Solution :
Assuming that bdate is correctly formatted, try this one:
WHERE bdate BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), '%m-%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m-%d')
otherwise you could calculate the min and max dates from php and insert them in between $min_date and $max_date