1

This is an algorithm to check if within the previous or next 2 weeks was/gonna be the birthday of some user and if yes, it shows the user and the date of his birthday.
The fetch arrays do work, returning day and month, but the SQL does not return a thing.

BornDate is a date field, so there is no need to use str_to_date (I tried that). I tried to remove the function str_to_date applied on next two variables: got no result either. What am I doing wrong? There isn't any easier way to achieve what I need? My code:

$sub = mysqli_fetch_array(mysqli_query($conn,"select date_sub(curdate(), interval 14 day);"));
$before = mysqli_fetch_array(mysqli_query($conn,"select date_format('$sub[0]', '%d-%m');"));

$add = mysqli_fetch_array(mysqli_query($conn,"select date_add(curdate(), interval 14 day);"));
$after = mysqli_fetch_array(mysqli_query($conn,"select date_format('$add[0]', '%d-%m');"));

$sql = "select * from user where date_format(bornDate, '%d-%m') between str_to_date('$before[0]', '%d-%m') and str_to_date('$after[0]', '%d-%m');";
$request = mysqli_query($conn, $sql);

if (mysqli_affected_rows($link) != 0) {
  while ($user = mysqli_fetch_array($request, MYSQLI_ASSOC)) {
    echo $user['name'];
    echo $user['bornDate'] . "<br>";
  }
} else {
  echo "No user does/did birthday on previous/next 2 weeks";
}
Cœur
  • 37,241
  • 25
  • 195
  • 267
Guilhermy
  • 55
  • 6
  • 1
    Possible duplicate of [How to check if a date is in a given range?](https://stackoverflow.com/questions/976669/how-to-check-if-a-date-is-in-a-given-range) – Joseph Sible-Reinstate Monica Mar 30 '18 at 04:56
  • Or maybe of https://stackoverflow.com/questions/12910691/selecting-date-range-mysql-with-date-format – Joseph Sible-Reinstate Monica Mar 30 '18 at 04:57
  • I think he asked how to reduce query code? Instead of getting the start and end date through SQL query get it through PHP date function and then directly go for a single query. – Alive to die - Anant Mar 30 '18 at 04:58
  • No, friend. These questions are about a full date range. I need to check if a day and a month of a SQL field are within a range. I cant do that using the year. – Guilhermy Mar 30 '18 at 05:00
  • @GuilhermyCamargo Also if you are trying to compare today's' date then previous 2 weeks is un-understandable to me – Alive to die - Anant Mar 30 '18 at 05:03
  • @AlivetoDie This algorithm must verify if the users' birthday was in previous 2 weeks, or if it will be in next two weeks, by taking the current date. Got it? – Guilhermy Mar 30 '18 at 05:09
  • 1
    @Cœur You can see in the comments that wasn't me whom found the solution. And I marked the comment as correct. – Guilhermy Apr 27 '18 at 09:27

3 Answers3

1

Please use this query to get the result (user born within previous or next 2 weeks)

select * from user where DAYOFYEAR(bornDate) between 
DAYOFYEAR(CURDATE())-14 and DAYOFYEAR(CURDATE())+14
srp
  • 560
  • 1
  • 5
  • 14
  • This does not work. BornDate contains dates like 1998-05-09. CURDATE uses the current year too. Only if the user has born within 14 days he will be listed. – Guilhermy Mar 30 '18 at 05:05
  • I have modified my answer. Please try and use this – srp Mar 30 '18 at 05:24
  • Actually, I have tried some dates. If the birthday is March 4 (or less), it is not listed. If the birthday is March 5 (or more), it is listed. Now trying future dates: If the birthday is May 8 (or less), it is listed. If the birthday is May 9 (or more) it is not listed. – Guilhermy Mar 30 '18 at 05:38
  • @Guilhermy try this query – srp Mar 30 '18 at 05:41
  • Alright. I just tried your new query. Seems to work, but it is listing March 15 birthdays (2 weeks and 1 day previous), and it is not listing May 13 birthdays (exactly 2 weeks next). – Guilhermy Mar 30 '18 at 05:48
  • This probably is the nearest i can get you with this query. You may need to tinker with it for more accuracy – srp Mar 30 '18 at 05:53
  • some tinkering like this DAYOFYEAR(CURDATE())-13 and DAYOFYEAR(CURDATE())+15 etc – srp Mar 30 '18 at 05:54
  • Yeah! This actually works just fine! You have solved my problem. Thanks a lot. (I am talking about the query -13/+15) – Guilhermy Mar 30 '18 at 05:58
0

Just try

WHERE date_format(bornDate, '%m-%d') between '07-01' and '10-01'
Mohammad Usman
  • 37,952
  • 20
  • 92
  • 95
Tsuyoshi Ishikawa
  • 1
  • I just tried using this range, and I got no result. But I changed '07-01' to '01-01' and '10-01' to '31-12'. All users were listed. See that my code uses the mask day-month. – Guilhermy Mar 30 '18 at 05:19
  • WHERE (date_format(bornDate, '%m-%d') between '12-20' and '12-31') OR (date_format(bornDate, '%m-%d') between '01-01' and '01-05') – Tsuyoshi Ishikawa Mar 30 '18 at 05:23
  • This works on predefined dates. This code must check and update dates every day. – Guilhermy Mar 30 '18 at 06:03
0

The solution was to update the SQL code to:

$sql = "select * from user where DAYOFYEAR(bornDate) between DAYOFYEAR(CURDATE())-13 and DAYOFYEAR(CURDATE())+15;";

All fetch arrays above the $sql are no longer needed.

Guilhermy
  • 55
  • 6