Can't get date conditions to work in MYSQLI query-CodePudding

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.

CodePudding user response：

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