Email reminder system

[geeklike]

Hi,

I'm trying to make a reminder system where I have saved dates in a database along with events that will happen on that day, and am now trying to make a reminder system that sends out an email one day before the event happens.

However, the code I've found doesn't seem to be working as it should, and that's where you guys come in.

This is the code:

<?php

include('inc/db_connect.inc');

$q ="SELECT * FROM webcom_users";
$q_res = mysql_query($q, $dbc);
while ($q_data=mysql_fetch_assoc($q_res)){

$appy = $q_data['appy'];
$appm = $q_data['appm'];
$appd = $q_data['appd'];
$appdate = $q_data['appy'] . $q_data['appm'] . $q_data['appd'];

$number_of_days_before = 1;
$email = "email";
$reminder_details = "";
$todays_date = date( "Ymd" );
$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 4, 2);
$date = substr($todays_date, 6, 2);
$trigger_date = date("Ymd", mktime (0,0,0,$month,$date-$number_of_days_before,$year));
$result = mysql_query("SELECT * FROM webcom_users WHERE $year = $appy AND $month = $appm AND $date = $appd+1 ORDER BY appy AND appm AND appd ASC" );
$nr = mysql_num_rows( $result );
while( $row = mysql_fetch_array( $result ) )
{
$year = substr($row["appy"], 0, 4);
$month = substr($row["appm"], 4, 2);
$date = substr($row["appd"], 6, 2);
$reminder_date = date("M j, Y", mktime (0,0,0,$month,$date,$year));
$reminder_details .= "Dude\n\n";
}
mysql_free_result( $result );
if( !empty( $nr ) )
{
// Send out Reminder mail
$mailheader = "From: Reminder System <$email>\nX-Mailer: Reminder\nContent-Type: text/plain";
mail("$email","Reminder","$reminder_details","$mailheader");
}
}
?>

Can anybody help?

-Pix

[edited by: geeklike at 2:31 pm (utc) on May 12, 2010]

[Matthew1980]

Hi there Geeklike,

Try this:-

$result = mysql_query("SELECT * FROM `webcom_users` WHERE `".$year."` = '".$appy."' AND `".$month."` = '".$appm."' AND `".$date."` = '".$appd+1."' ORDER BY `appy` AND `appm` AND `appd` ASC" ) or die(mysql_error());

I have popped the error reporting on the end of the query just incase there is anything wrong there..

I have only skimmed through the code, but the query just seems as though there maybe an error there in the formatting, as for the syntax being correct I'm not sure.

Try that first though.

Also:-

$mailheader = "From: Reminder System <".$email.">\n\r";
$mailheader .= "X-Mailer: Reminder\n\r";
$mailheader .= "Content-Type: text/plain\n\r";

mail($email,"Reminder",$reminder_details,$mailheader);

you can build the var using the .= operator.

Oh, and for future ref: Exemplify your details, keeps your email detail to yourself, the mods will probably remove that ;-p

Cheers,
MRb

[geeklike]

Thanks for replying (and thanks for the tip :P), Matthew1980.

I tried what you said (that line now says:

$result = mysql_query("SELECT * FROM `webcom_users` WHERE `" . $year . "` = '" . $appy . "' AND `" . $month . "` = '" . $appm . "' AND `" . $date . "+1` = '" . $appd . "' ORDER BY `appy` AND `appm` AND `appd` ASC" ) or die(mysql_error());

in stead), but now it says "Unknown column '2010' in 'where clause'".

I've tried changing the dates in the database, but that didn't help.

- Pix

[Matthew1980]

Hi there geeklike,

Well, you are specifying a field called $year which I would guess is '2010' :-

SELECT * FROM `webcom_users` WHERE `" . $year . "`

So that tells you as you are looking for a field that doesn't exist!

Try echoing the sql query to see how its populated too, this can reveal anomolies.

Cheers,
MRb

[rocknbil]

Yes, it's in $month and $date too, look.

$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 4, 2);
$date = substr($todays_date, 6, 2);

then

SELECT * FROM `webcom_users` WHERE `" . $year . "` = '" . $appy . "' AND `" . $month . "` = '" . $appm . "' AND `" . $date . "+1` = '" . $appd . "' ORDER BY `appy` AND `appm` AND `appd` ASC

Unless you have, say, fields named "2010," "13", etc. (which I think is illegal anyway) it won't work. Note sure what you're trying to do with $year, $month, $date, but they're not field names.

[geeklike]

Thank you so much for the replies, it now send out e-mails (YAY!).

However, in stead of just sending out the details that was entered for the mail, it now send out as many mails as there are rows in the db-table, one for each row but to the same email. So, in short, it takes values from other rows in stead of just from its own row.

The code now looks like this:

<?php

include('inc/db_connect.inc');

$todays_date = date( "Ymd" );
$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 4, 2);
$date = substr($todays_date, 6, 2);

$q ="SELECT * FROM webcom_users";
$q_res = mysql_query($q, $dbc);
while ($q_data=mysql_fetch_assoc($q_res)){

$email = "MY EMAIL";
$reminder_details = "";
$result = mysql_query("SELECT * FROM `webcom_users` WHERE `appy` = '" . $year . "' AND `appm` = '" . $month . "' AND `appd` = '" . $date . "' ORDER BY `appy` AND `appm` AND `appd` ASC" ) or die(mysql_error()); 
$nr = mysql_num_rows( $result );
while( $row = mysql_fetch_array( $result ) )
{
$year = substr($row["appy"], 0, 4);
$month = substr($row["appm"], 4, 2);
$date = substr($row["appd"], 6, 2);
$reminder_details .= "Kære ".$q_data['navn'].",\n\n";
$reminder_details .= "Husk at du har en tid til at få klippet dit hår idag klokken ".$q_data['appt']."\n\n";
$reminder_details .= "Vi glæder os til at se dig, med venlig hilsen,";
$reminder_details .= "Stender ".$q_data['saloon'];
}
mysql_free_result( $result );
if( !empty( $nr ) )
{
// Send out Reminder mail
$mailheader = "From: Stender ".$q_data['saloon']." <".$email.">\n\r"; 
mail("$email","Din tid hos Stender ".$q_data['saloon'],"$reminder_details","$mailheader");
}
}
?>

[Matthew1980]

Hi there geeklike,

Are you just saying that you want 1 email to be sent out from the $email address provided from the user? Or is the email a constant? Because at the moment the mail() function sits in the while loop, so it will send out as many emails as there are rows in the DB, irrespective of there only being 1 address specified.

Cheers,
MRb

[geeklike]

Hi Matthew1980,

I actually made that part work by playing around with it, but now it only sends out one email even though there are two in the database on the same day, and I once again have no idea why :/

This is the code:

<?php

include('inc/db_connect.inc');

$todays_date = date( "Ymd" );
$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 4, 2);
$date = substr($todays_date, 6, 2);


$q ="SELECT * FROM webcom_users WHERE appy = " . $year . " AND appm = " . $month . " AND appd = " . $date . "";
$q_res = mysql_query($q, $dbc);

$num_rows = mysql_num_rows($q_res);

while ($q_data=mysql_fetch_assoc($q_res))
{

$email = "#*$!@#*$!.org";
$reminder_details = "";
$result = mysql_query("SELECT * FROM `webcom_users` WHERE `appy` = '" . $year . "' AND `appm` = '" . $month . "' AND `appd` = '" . $date . "'" ) or die(mysql_error()); 
$nr = mysql_num_rows( $result );
while( $row = mysql_fetch_array( $result ) )
{
$year = substr($row["appy"], 0, 4);
$month = substr($row["appm"], 4, 2);
$date = substr($row["appd"], 6, 2);
$reminder_details .= "Kære ".$q_data['name'].",\n\n";
$reminder_details .= "Husk at du har en tid til at få klippet dit hår idag klokken ".$q_data['appt']."\n\n";
$reminder_details .= "Vi glæder os til at se dig, med venlig hilsen,";
$reminder_details .= "Stender ".$q_data['saloon'];
}
mysql_free_result( $result );
if( !empty( $nr ) )
{
// Send out Reminder mail
$mailheader = "From: Stender ".$q_data['saloon']." <".$email.">\n\r"; 
mail("$email","Din tid hos Stender ".$q_data['saloon'],"$reminder_details","$mailheader");
}
}

?><

[Matthew1980]

Hi there geeklike,

You are only specifying one email address unless I have read the code wrong, the mail recipient in the mail() is called from the defined var $email, there is no reference to the returned DB array from the while loop, so only 1 email will be sent, if there is an email address in the DB you will have to put the mail function in the loop and put the array key into the recipient part of the mail function - only if the address are stored in the DB though.

$mailheader = "From: Stender ".$q_data['saloon']." <".$email.">\n\r";

Shouldn't the $q_data['saloon'] (if it is an email address?) key be in the recipient field and the $mail var where q_data is? Looks like they are the wrong way around to me?

Hope this makes sense, hopefully I have understood you correctly too!

Cheers,
MRb

[geeklike]

Holy crap, thank you, it's WORKING now! :D

[Matthew1980]

Hi there geeklike,

Cool! Glad as you are all sorted now, have fun with the rest of the project.

Cheers,
MRb