John Davidson

Message:

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.

---

My Answer:
The issue with your query is that you are trying to compare the bdate field in the database with a date range that is based on the current date (CURDATE()). However, the format of the bdate field in the database is YYYY-MM-DD, which includes the year as well.

To compare the date range correctly, you need to extract only the month and day part of the bdate field in the database and compare it with the month and day part of the current date.

Here is the updated query:

php
$sql = "SELECT * FROM user_birthdays
WHERE DATE_FORMAT(bdate, '%m-%d')
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)) {
// Your code here
}
}


This query will extract only the month and day part of the bdate field in the database and compare it with the month and day part of the current date within a date range of one week before and after. This should give you the desired result.