homepage Welcome to WebmasterWorld Guest from 54.167.185.110
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Email reminder system
geeklike




msg:4131375
 2:09 pm on May 12, 2010 (gmt 0)

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




msg:4131381
 2:21 pm on May 12, 2010 (gmt 0)

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




msg:4131396
 2:44 pm on May 12, 2010 (gmt 0)

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




msg:4131407
 3:04 pm on May 12, 2010 (gmt 0)

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




msg:4131560
 6:37 pm on May 12, 2010 (gmt 0)

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




msg:4131968
 12:49 pm on May 13, 2010 (gmt 0)

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




msg:4131985
 1:20 pm on May 13, 2010 (gmt 0)

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




msg:4131991
 1:49 pm on May 13, 2010 (gmt 0)

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




msg:4132038
 3:17 pm on May 13, 2010 (gmt 0)

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




msg:4132046
 3:36 pm on May 13, 2010 (gmt 0)

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

Matthew1980




msg:4132519
 7:24 am on May 14, 2010 (gmt 0)

Hi there geeklike,

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

Cheers,
MRb

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved