Help With Reminder Script

[derekshull]

I have a script that I need some help with. Its a reminder script (posted below). Basically I have a completiondate (which in the database is a varchar...don't know if that makes a difference) that is made up like day, month, year (Ex: 06102012) and I want the script (which is a cron job) to look at todays date and see if it is 2 days before todays date. If it is then I want an email sent. I'm confused on where I'm going wrong. Please help! Thanks.

 

An example would be:

Todays date is: 06102012

If completiondate = 2 days before todays date

{

send email

}

 

$todaysdate = date('d/m/Y');
$query = "SELECT completiondate FROM needs WHERE completiondate =($todaysdate, strtotime('-1 days'))";
$result = mysql_query($query);

mail('[email protected]', 'Important Stuff', 'Hey there.');
?>

[Jessica]

Did anyone else read the title as Reindeer? No? Just me? Ok.

 

 

Your code has no if statement in it, and no logic. What did you try so far?

 

strtotime is a php function, and won't work inside a string like that.

[derekshull]

I tried this but no luck

 

 

 

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

$query = "SELECT completiondate FROM needs";

$result = mysql_query($query);

 

$date = $rows['completiondate'];

if ($date = ($todaysdate - strtotime('-1 days'))) {

 

 

mail('[email protected]', 'Important Stuff', 'Hey there.');

}

?>

[derekshull]

I've tried looking everywhere online too.

 

I'm basically needs a function that will get todays date and subtract 2 days away from the current date. Then if the record entry "completiondate" = that function date then it should send an email.

[Christian F.]

I recommend that you start in the PHP manual, and read the examples.

Reason I linked you to the "MySQLi" section is because the old "mysql" library is old, outdated and missing features from the new MySQL versions. Thus going to be removed in the future, and all new code should thus use the new and actively developed libraries instead.

[derekshull]

Thanks. I'm not sure that that really helped any though.

[Jessica]

MySQL has a DATESUB function you can use.

[derekshull]

how to I go about getting the current date?

[Jessica]

NOW()

[derekshull]

Ok so I thought I had it...but apparently not.

 

$query = "SELECT completiondate FROM needs WHERE completiondate IS NOT NULL";
$result = mysql_query($query);
while ($rows = mysql_fetch_array($result)) {
$completiondate = $rows['completiondate'];
$todaysdate = date("dmY");
$reminderdate = DATE_SUB($todaysdate, INTERVAL 2 DAY);
if ($completiondate = $reminderdate) {
mail('[email protected]', 'Important Info', 'This stuff is importnat.');
}
}

 

I'm getting this error:

Parse error: syntax error, unexpected T_LNUMBER in /home/content/17/9932517/html/remindertwodaysbefore.php on line 29

 

Line 29 is where the $reminderdate is.

[Jessica]

I said MySQL has the function DATESUB. Not PHP. You clearly looked it up to get the syntax... but then just stuck it in as PHP code?

[derekshull]

Hey I'm still new to this...I've only been learning this for 2 days. What's the solution? Do you get what I'm going after?

[Jessica]

Well, if they are MySQL functions, where do you think you would use them?

[derekshull]

I'd use them here:

 

$query = "SELECT completiondate FROM needs WHERE completiondate IS NOT NULL";

 

 

But what I'm wondering is how to compare the two dates and if they are the same to send out the email. I'm lost...but I feel like i'm close.

[Jessica]

I doubt that completiondate is the only column you'll need to select, but:

SELECT completiondate FROM needs WHERE completiondate = DATESUB(NOW(), INTERVAL 2 DAY)

[derekshull]

Is there a certain format that my entries in the column completiondate should be in? Right now they are in dmY format.

[Jessica]

They need to be a DATE field. Unless you need the time too, then a DATETIME.

[derekshull]

So now I have this:

 

$query = "SELECT completiondate FROM needs WHERE completiondate = DATESUB(NOW(), INTERVAL 2 DAY)";
$result = mysql_query($query);

mail ('[email protected]', 'hey', 'hey');

 

I've put the competiondate field as DATE

 

And I've set my completiondate to 2012-10-08

 

and it sent me an email.

 

But when I set it to another date it still sends me an email. Both past and future dates...i'm confused. I thought that would work.

[derekshull]

I need a while statement i think....i'm getting there lol

[Jessica]

How many emails do you want to get? One for each row? Or one containing all the info for every row that matches?

 

And right now, you just told it to email you, with no logic.

[derekshull]

yes

[Jessica]

So then loop through the rows and send the emails.

[derekshull]

Like this?:

 

$query = 'SELECT completiondate FROM needs WHERE completiondate = DATESUB(NOW(), INTERVAL 2 DAY)';
$result = mysql_query("SELECT completiondate FROM needs WHERE completiondate = DATESUB(NOW(), INTERVAL 2 DAY)");
while ($rows = mysql_fetch_array($result)) {

mail ('[email protected]', 'hey', 'hey');
}

[Jessica]

Did you try it?

[derekshull]

yeah it throws off this error:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/content/17/9932517/html/remindertwodaysbefore.php on line 23