CODEWITHSUNDEEP
phpmysql
Drenyl
Drenyl

Reputation: 934

MySQL - Fetch data 30 days before its expiration date

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

Upvotes: 2

Views: 3220

Answers (3)

Benard Kim
Benard Kim

Reputation: 1

For sql server,to check items which will expire within a period of 1-7days

select sdate, edate, DATEDIFF(MONTH,GETDATE(),edate) as MONTHS from Stockin 
where DATEDIFF(MONTH,GETDATE(),edate) between 1 and 7
order by edate desc

enter image description here

Upvotes: 0

Barmar
Barmar

Reputation: 782785

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.

Upvotes: 4

Azeem Hafeez
Azeem Hafeez

Reputation: 250

I hope it will work for you.

DATEDIFF(d,GETDATE(),expiration_date) >= 30

Upvotes: -1

Related Questions