I'm trying to fetch data whose expiration date is within 30 days of today. I've tried using BETWEEN clause but still not working.
table name registration:
id exp_date
12 05-20-2018
19 05-19-2018
34 05-22-2018
let say the date today is 04-28-2018. Which I stored in a variable $date_today
$date_today = '04-28-2018';
$query = "SELECT * FROM registration WHERE expiration_date BETWEEN('$date_today', DATE_SUB(expiration_date, INTERVAL 30 DAY)";
$test = mysqli_query($con, $query);
$row = mysqli_fetch_assoc1( $test);
and this the error that I'm getting:
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given
BETWEEN is not a function, it's an operator. The syntax is:
BETWEEN low_value AND high_value
You should also stop using variable substitution and use prepared statements with mysqli_stmt_bind_param(). See How can I prevent SQL injection in PHP?
To get everything that's expiring within the next 30 days, you want:
WHERE exp_date BETWEEN CURDATE() and DATE_ADD(CURDATE(), INTERVAL 30 DAY)
Notice that this uses DATE_ADD(), not DATE_SUB(), since you want expiration dates in the future, not the past.
I hope it will work for you.
DATEDIFF(d,GETDATE(),expiration_date) >= 30