If 32 days over date do this - HELP!!

[ipwnzphp]

Here is my due date: 2009-08-19

Here is my next due date: 2010-08-18

 

My dates are stored in same format i posted the dates as.

 

What it should do, If time between the last invoice and the next due date is greater than or equal to. It should email me!

 

$datedff = ""; what goes here?

if($datedff >= "32") {

$to  = '';

$subject = 'Invoice Aduit Report';

$message = "There is no invoice for ". $username;

 @mail($to, $subject, $message, $headers);
 }

[akitchin]

if you're storing the dates as a DATE in MySQL, you can use their date functions. in particular, look at TO_DAYS():

 

SELECT (TO_DAYS(NOW()) - TO_DAYS(date_column)) AS age_in_days FROM table

[waynewex]

if you're storing the dates as a DATE in MySQL, you can use their date functions. in particular, look at TO_DAYS():

 

SELECT (TO_DAYS(NOW()) - TO_DAYS(date_column)) AS age_in_days FROM table

 

This means that you should have your last invoice "send date" stored in your db.

[ipwnzphp]

Trying it now

[ipwnzphp]

Here is my full code, it is still not working.

 

require("dbconnect.php");

$hosting_get = mysql_query("SELECT (TO_DAYS(NOW()) - TO_DAYS(nextduedate)) AS age_in_days_due FROM `tblhosting` WHERE domainstatus = 'Active'");
while ($hosting_pull = mysql_fetch_array($hosting_get)) {

$userid = $hosting_pull['userid'];

$orderid = $hosting_pull['id'];

$billingcycle = $hosting_pull['billingcycle'];

$regdate =$hosting_pull['age_in_days_due'];

$username = $hosting_pull['username'];

$items_get = mysql_query("SELECT (TO_DAYS(NOW()) - TO_DAYS(duedate)) AS age_in_days  FROM `tblinvoiceitems` WHERE relid = '$orderid'");
$items_pull = mysql_fetch_array($items_get);

$duedate = $items_pull['age_in_days'];


if($billingcycle == "Monthly"){	

if($duedate >= $regdate) {

$to  = '';

$subject = 'Invoice Aduit Report';

$message = "There is no invoice for ". $username;

 @mail($to, $subject, $message);
 }


    }

}

[akitchin]

there has obviously been some miscommunication here. are you looking for the difference between `nextduedate` and `duedate` in number of days?

[ipwnzphp]

there has obviously been some miscommunication here. are you looking for the difference between `nextduedate` and `duedate` in number of days?

 

yes! exactly!

[akitchin]

TO_DAYS() converts the given DATE into the number of days since year 0. in order to get the difference in days between `nextduedate` and `duedate`, SELECT the TO_DAYS() of both of them and take the difference. therefore, change your SELECT expressions from:

 

TO_DAYS(NOW()) - TO_DAYS(datefield)

 

to:

 

TO_DAYS(datefield)

 

you can use the same alias if you want.

[ipwnzphp]

testing now.

[ipwnzphp]

Still not working, here is my updated code.

 

require("dbconnect.php");

$hosting_get = mysql_query("SELECT TO_DAYS(nextduedate) AS age_in_days_due FROM `tblhosting` WHERE domainstatus = 'Active'");
while ($hosting_pull = mysql_fetch_array($hosting_get)) {

$userid = $hosting_pull['userid'];

$orderid = $hosting_pull['id'];

$billingcycle = $hosting_pull['billingcycle'];

$regdate =$hosting_pull['age_in_days_due'];

$username = $hosting_pull['username'];

$items_get = mysql_query("SELECT TO_DAYS(duedate) AS age_in_days FROM `tblinvoiceitems` WHERE relid = '$orderid'");
$items_pull = mysql_fetch_array($items_get);

$duedate = $items_pull['age_in_days'];

if($billingcycle == "Monthly"){	

if($duedate >= $regdate) {

$to  = '';

$subject = 'Invoice Aduit Report';

$message = "There is no invoice for ". $username;

 @mail($to, $subject, $message;
 }


    }

}

[akitchin]

this section:

 

$hosting_get = mysql_query("SELECT TO_DAYS(nextduedate) AS age_in_days_due FROM `tblhosting` WHERE domainstatus = 'Active'");
while ($hosting_pull = mysql_fetch_array($hosting_get)) {

$userid = $hosting_pull['userid'];

$orderid = $hosting_pull['id'];

$billingcycle = $hosting_pull['billingcycle'];

$regdate =$hosting_pull['age_in_days_due'];

$username = $hosting_pull['username'];

 

is flawed. you're not SELECTing anything other than the `age_in_days_due` column, so `userid`, `id`, `billingcycle` and `username` won't be defined. that causes the second query to grab nothing, since $orderid is not defined. add those other columns to the SELECT query.

 

second, this condition:

 

   if($duedate >= $regdate) {

 

just checks if the $duedate is greater than or equal than $regdate, with no mention of your 32-day delay.

[ipwnzphp]

this section:

 

$hosting_get = mysql_query("SELECT TO_DAYS(nextduedate) AS age_in_days_due FROM `tblhosting` WHERE domainstatus = 'Active'");
while ($hosting_pull = mysql_fetch_array($hosting_get)) {

$userid = $hosting_pull['userid'];

$orderid = $hosting_pull['id'];

$billingcycle = $hosting_pull['billingcycle'];

$regdate =$hosting_pull['age_in_days_due'];

$username = $hosting_pull['username'];

 

is flawed. you're not SELECTing anything other than the `age_in_days_due` column, so `userid`, `id`, `billingcycle` and `username` won't be defined. that causes the second query to grab nothing, since $orderid is not defined. add those other columns to the SELECT query.

 

second, this condition:

 

   if($duedate >= $regdate) {

 

just checks if the $duedate is greater than or equal than $regdate, with no mention of your 32-day delay.

 

How would i do the 32 day delay?

[akitchin]

let's see if i can guide you through this in simple steps:

 

1. $regdate holds a value, in days, of `nextduedate` from the table `tblhosting`,

2. once you fix your queries, $duedate will hold a value, in days, of `duedate` from the table `tblinvoiceitems`, and

3. subtracting one of those variables from the other will give the difference, in days, between the two dates.

 

based on those facts, how do you think you could check whether the difference between those two dates is 32 days or more?

[ipwnzphp]

let's see if i can guide you through this in simple steps:

 

1. $regdate holds a value, in days, of `nextduedate` from the table `tblhosting`,

2. once you fix your queries, $duedate will hold a value, in days, of `duedate` from the table `tblinvoiceitems`, and

3. subtracting one of those variables from the other will give the difference, in days, between the two dates.

 

based on those facts, how do you think you could check whether the difference between those two dates is 32 days or more?

 

$dodatemath = $regdate - $duedate;
if($dodatemath >= 32) {
// mail functions here
}

 

That correct?

[akitchin]

indeed it should be, provided you have fixed your queries.

[ipwnzphp]

indeed it should be, provided you have fixed your queries.

 

Testing once again.. now!

[ipwnzphp]

It sent an email for every service

 

Here is my code

 

require("dbconnect.php");

$hosting_get = mysql_query("SELECT TO_DAYS(nextduedate) AS age_in_days_due, userid, id, billingcycle, username FROM `tblhosting` WHERE domainstatus = 'Active'");
while ($hosting_pull = mysql_fetch_array($hosting_get)) {

$userid = $hosting_pull['userid'];

$orderid = $hosting_pull['id'];

$billingcycle = $hosting_pull['billingcycle'];

$regdate =$hosting_pull['age_in_days_due'];

$username = $hosting_pull['username'];

$items_get = mysql_query("SELECT TO_DAYS(duedate) AS age_in_days FROM `tblinvoiceitems` WHERE relid = '$orderid'");
$items_pull = mysql_fetch_array($items_get);

$duedate = $items_pull['age_in_days'];

$dodatemath = $regdate - $duedate;

if($dodatemath >= 32) {

$to  = '';

$subject = 'Invoice Aduit Report';

$message = "There is no invoice for ". $username;

 @mail($to, $subject, $message);
 }


}

[akitchin]

i'm guessing it's because your assignment of $regdate is failing. have you checked to see what's in $items_pull?

[ipwnzphp]

i'm guessing it's because your assignment of $regdate is failing. have you checked to see what's in $items_pull?

 

$duedate = $items_pull['age_in_days'];

echo $duedate."<br><br>";

 

Shows things like

 

733997

 

733997

 

733997

 

733997

 

733999

 

733999

 

734002

733997

 

733997

 

733997

 

and $regdate shows the same thing.

 

733997

 

733999

 

733999

 

734002

 

[akitchin]

and what is $dodatemath displaying if you echo it?