使用日期比较从mysql DB检索数据

Question

I want to send a reminder to my subscribers a month before their subscription ends, i have columns validity and reminder (type VARCHAR) in my MYSQL DB

date stored in those columns are saved using php date function

$validity = date('d/m/Y',strtotime("6 months"));
$reminder = date('d/m/Y',strtotime("5 months"));

now i want to send a mail when the current date is equals reminder date I have a test entry with reminder value 22/06/2017 and $date variable echo the same value.

$date = date('d/m/Y');

$q = 'SELECT * FROM subscriptions WHERE reminder = "$date"';
$r = mysql_query($q);

if(!$r){
echo 'query err';
}

$a = mysql_num_rows($r);
echo 'No of rows returned '.$a;

*mailing script after this line*

this script outputs No of rows returned 0

Can someone give me some idea how i should approach this

Answer 1

First i suggest you your date format is change in database and type change datetime.

This format follow for you insert reminder date

$reminderdate = date('Y-m-d');

Then compare with currentdate when fetch data from database:

$date=date('Y-m-d');
if($r['reminder']==$date)
{
    echo 'Mail sent here';
}
else
{
   echo 'date not match';
}