CODEWITHSUNDEEP
phpmysqlvalidationdate-range
Guilhermy
Guilhermy

Reputation: 55

How to verify if day and month of date are in a range using SQL/PHP

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";
}

Upvotes: 1

Views: 177

Answers (3)

Guilhermy
Guilhermy

Reputation: 55

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.

Upvotes: 0

srp
srp

Reputation: 585

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

Upvotes: 1

Tsuyoshi Ishikawa
Tsuyoshi Ishikawa

Reputation: 1

Just try

WHERE date_format(bornDate, '%m-%d') between '07-01' and '10-01'

Upvotes: 0

Related Questions