douzi115522
douzi115522
2018-04-28 06:14
浏览 187
已采纳

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

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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongsuiwo0279
    dongsuiwo0279 2018-04-28 06:18
    已采纳

    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.

    点赞 评论
  • dqjl0906
    dqjl0906 2018-04-28 06:30

    I hope it will work for you.

    DATEDIFF(d,GETDATE(),expiration_date) >= 30
    
    点赞 评论

相关推荐