Page 1 of 1

Modify reminder send class

Posted: Wed Feb 11, 2009 4:30 pm
by Sonny
Hi

The problem is any reminders which do not send within their
hour and date, server down etc, will never get sent or deleted
from the database.

I have been trying to modify the code below, to get all unsent past
reminders to go out the next time the reminder script runs. but am
having no luck.

Any help on this is appreciated
Thanks

------------------------------------

function send ($stamp)
{
global $db, $config;

list ($day, $month, $year, $hour) = array(date('j', $stamp), date('n', $stamp), date('Y', $stamp), date('G', $stamp));
$query = "SELECT * FROM reminders WHERE date_day=$day AND date_month=$month AND date_year=$year AND date_hour=$hour";
$result = $db->query($query);
if(DB::iserror($result))
{
die("Error in send.<br>SQL: $query<br>" . $result->getMessage());
}

$num_rows = $result->numRows();

for ($i = 0; $i < $num_rows; $i++)
{
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);

$newdate = array();

$to = $row['email_to'];
$body = wordwrap($row['email_body']);
$subj = $config['mail_subj_prepend'] . ' ' . $row['email_subject'];

if ($to != '')
{
if (mail($to, $subj, $body, 'From: ' . $config['mail_from']))
{
echo "\n(ID:{$row['id']}) E-mail Reminder sent to: <$to>";
}

Re: Modify reminder send class

Posted: Wed Feb 11, 2009 11:08 pm
by susrisha
I think i understood your problem but let me explain so that it can be clear..
You are trying to send remainders through mail. you stored all the remainders data in database using date time variable to store the time to be sent and also the data to be sent.

When the server is down, you and could not send the data, you are trying to send them in the next run.
Well here is what i suggest:
Put a status field Sent/Scheduled in database. Once the script runs, it will have to check for all the rows Whose Time value is less than the Time value you provided AND whose status is Scheduled. Once the information is sent, set the status to Sent. Allow it to run.

I had a similiar problem with a scheduler and i solved it this way. :)

Re: Modify reminder send class

Posted: Wed Feb 11, 2009 11:22 pm
by Sonny
[quote="susrisha"]I think i understood your problem but let me explain so that it can be clear..
You are trying to send remainders through mail. you stored all the remainders data in database using date time variable to store the time to be sent and also the data to be sent.

When the server is down, you and could not send the data, you are trying to send them in the next run.
Well here is what i suggest:
Put a status field Sent/Scheduled in database. Once the script runs, it will have to check for all the rows Whose Time value is less than the Time value you provided AND whose status is Scheduled. Once the information is sent, set the status to Sent. Allow it to run.

I had a similar problem with a scheduler and i solved it this way. :)[/quote]


Is their anything in that function above I can modify to send everything thats in the database
that's a past reminder during the latest run?

I am very new at this, and cannot get that to work

Re: Modify reminder send class

Posted: Thu Feb 12, 2009 3:07 am
by susrisha
okay here is a solution
1. Add a field in the table reminders fieldname: status VARCHAR(10)
2. While inserting the reminder schedule, also insert the status field as 'SCHEDULED'
3. In the mysql query given in the function, change the query to

Code: Select all

 
$query = "SELECT * FROM reminders WHERE date_day<=$day AND date_month<=$month AND date_year<=$year AND date_hour<=$hour and status='SCHEDULED'";
 
4. at the end of your function add an update statement

Code: Select all

 
if (mail($to, $subj, $body, 'From: ' . $config['mail_from'])) 
{
echo "\n(ID:{$row['id']}) E-mail Reminder sent to: <$to>";
 
$query2= "UPDATE reminders SET status='SENT' WHERE id= $row['id']";
//I havent written the query execution part. best that its left to you to execute
}
 

Re: Modify reminder send class

Posted: Thu Feb 12, 2009 9:20 am
by Sonny
Hi I have been at this for about a month now
If possible, "please" show exactly where in the code above I need to add this

if (mail($to, $subj, $body, 'From: ' . $config['mail_from']))
{
echo "\n(ID:{$row['id']}) E-mail Reminder sent to: <$to>";

$query2= "UPDATE reminders SET status='SENT' WHERE id= $row['id']";
//I haven't written the query execution part. best that its left to you to execute
}

Also can you please include the execution part?
I have a database full of old reminders mixed in with new ones.
I'm over my head with this, as it is.

###########################
# This is what my current SQL file looks like
#
# Create database 'reminders'
#
DROP DATABASE IF EXISTS remind;
CREATE DATABASE remind;
USE remind;
#
# Table structure for table 'reminders'
#
CREATE TABLE reminders (
id int(11) DEFAULT NULL auto_increment,
email_to varchar(255) not null,
email_subject varchar(255) not null,
email_body text not null,
date_day int(3) not null,
date_month int(3) not null,
date_year int(6) not null,
date_hour int(3) not null,
recur_every int(6) not null,
recur_interval varchar(32) not null,
PRIMARY KEY (id)
);

Thanks

Re: Modify reminder send class

Posted: Thu Feb 12, 2009 2:54 pm
by Sonny
Problem Fixed, under the send function query, changed "=" to "<="

Thanks for trying to help