retrieving data from mysql DB using date comparison

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

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 22 '17 at 12:50
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 22 '17 at 12:50
Your date isn't passing through. Single quotes on the outside passes the string as it is. You need to switch your quotes around so that the double quotes are on the outside, and the single quotes are around the date, so that the date is actually parsed. But you can also avoid all of these sticky quoting issues by switching to [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) or [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and using prepared statements. — aynber, Jun 22 '17 at 12:54
Also, as a suggestion, MySQL date comparison works better if you actually use the date datatype. You can convert the date to the format you want to display either when you pull it out or with PHP. — aynber, Jun 22 '17 at 12:56
You should define the query like `$q = 'SELECT * FROM subscriptions WHERE reminder = '.$date;` and yes as @jay said your script is at risk of SQL injections. — Sachchit Bansal, Jun 22 '17 at 12:58
@SachchitBansal the query as you have shown it is still at risk. — Jay Blanchard, Jun 22 '17 at 13:10

score 1 · Accepted Answer · answered Jun 22 '17 at 12:57

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';
}

score 0 · Answer 2 · answered Jun 22 '17 at 12:54

Your script is at risk for SQL Injection Attacks, see this

The issue is here:

'SELECT * FROM subscriptions WHERE reminder = "$date"';

change it to:

"SELECT * FROM subscriptions WHERE reminder = '".$date."'";

to compare date, you have to wrap it into single quotes '

score 0 · Answer 3 · answered Jun 22 '17 at 12:58

A few suggestions:

the reminder column may be redundant depending on your logic
in the future please use different approach to connect to the database, for example: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
the validity column should rather be DATETIME format
Now to your main question - it can be because of datetype mismatch so switch in database to DATETIME and in PHP use date('Y-m-d').

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

0

First Of all You should not store date in varachar.

Now to compare dates in varchar first you have to convert the string(varchar) into date.

for that you can use mysql's function: str_to_date

You can get the example how to convert it and then compare it to get the result.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 22 '17 at 13:01

Ketan Solanki

697
5
13

score 0 · Answer 5 · answered Jun 22 '17 at 13:01

0

$q = "SELECT * FROM subscriptions WHERE reminder = '{$date}'";

answered Jun 22 '17 at 13:01

Zeljka

376
1
10

score -1 · Answer 6 · answered Jun 22 '17 at 13:23

I Know You have selected the answer already and ignored the comments, because you just want to get this done with and move on no matter how you did it. I will just make this answer a community WIKI.

Below are the things you need to note :

The API that you are using have depreciated for a long time ago rather use mysqli or even better PDO with prepared statements.
Also What I'm sure when the use subscrips on your website, you should have a column that stores the duration of the subscription and the actual expire date and a flag to identify expired subscriptions.
Mysql does provide its own date time functions, which you can use and use the date type on these columns.

Use API that is still active and maintained that is mysqli_* or pdo they both support prepared statements.

$host    = '127.0.0.1';
$db      = 'DBNAME';
$user    = 'root';
$pass    = '';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false
);

$dbh = new PDO($dsn, $user, $pass, $opt);

$stmt = $dbh->query("SELECT * FROM subscriptions WHERE subscriptionsExpireColumn  = DATE_ADD(CURDATE(), INTERVAL 1 MONTH)");
if (!$stmt) {

    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
} else {

    $results = $stmt->fetchall();

    if (count($results) > 0) {

        foreach ($results as $row) {

            //DO WHAT EVER YOU WANT TO DO WITH THE RESULTS
        }
    } else {

        echo "no records found";
    }

}
?>

actually i know mysql commands are no longer supported but since i started the project with these, i want to finish it first than will change the codes to mysqli or PDO. anyways thanks for your response — Arun, Jun 22 '17 at 13:50

retrieving data from mysql DB using date comparison

6 Answers6