I have the following script which successfully finds the values for $payer_email, reminder_date and sub_expire_date, however where I am having trouble is when I apply a WHERE condition to the SELECT. The condition I am trying to apply is the commented out line at the end of the SELECT. The intention of the WHERE is to filter the values from the SELECT to only provide those values which point to the subscription expiry (sub_expire_date) thirty days ahead of time, but no values are found when they really should be.
Can anyone tell me what is wrong?

<?php
error_reporting(E_ALL ^ E_NOTICE);

include_once ("../real_conn/real_i_conn.php");
$reminder_date = date("Y-m-d", strtotime("+30 days"));
echo $reminder_date ."<br><br>";

$sql = "SELECT sec_tblpurchased_secureareas.users_id, sec_tblpurchased_secureareas.sub_expire_date, DATE_ADD(sec_tblpurchased_secureareas.sub_expire_date, INTERVAL - 30 day) AS reminder_date, sec_tblusers.payer_email
FROM sec_tblpurchased_secureareas
INNER JOIN sec_tblusers ON sec_tblusers.recid = sec_tblpurchased_secureareas.users_id";
//WHERE DATE_ADD(sec_tblpurchased_secureareas.sub_expire_date, INTERVAL - 30 day) = '". $reminder_date ."' ";

$result = mysqli_query($conni, $sql);

if ($result)
{
    // Return the number of rows in result set
    $rowcount=mysqli_num_rows($result);
    printf("Result set has %d rows.\n",$rowcount);
    echo "<br><br>";
}

while ($num = mysqli_fetch_array($result)) {
    //echo $num;

    $payer_email        = $num['payer_email'];
    echo $num['payer_email'] . "  <br>";

    $reminder_date      = $num['reminder_date'];
    $reminder_date      = date_create("$reminder_date");
    echo date_format($reminder_date,"Y/m/d") ." remind<br>";

    $sub_expire_date    = $num['sub_expire_date'];
    $sub_expire_date    = date_create("$sub_expire_date");
    echo date_format($sub_expire_date,"Y/m/d") . " expire<br><br>";

}
?>

This problem is solved. I should have used DATE_SUB instead of DATE_ADD along with a positive term of days instead of a negative one.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.