MySQL - 在到期日期前30天获取数据

Question

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

Answer 1

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.