PHP reminder E-mail - Cron job to run the script

[oku]

Hello everyone,

Well i've just searched the forum to see if my question has been answered prioer to posting, but seem to find the answer.

Well basicly i've created a script to help me monitor and maintain the domain names my company owns (1000's)

I've virtually finished it but i am missing one bit - the auto email's reminding me when a domain will expire - I know you get the reminders from registrars but they only send to myself and others need to know this informaiton eg: Marketing, IT dept and i dont want them to have access to the domain contral panel at the registrar.

So i've been looking up possible ways to generate an automated email - but to no avail.

I have the following fields in my database:

domain_name, company_name, exp_date (Which is the expiry date in 0000-00-00format

So i was wondering if a cron job script can be run daily to check the domains that will expire in the next 7 days. For example if a domain had a exp_date as 2008-05-16 when the script is run on the 2008-05-09 an email will be sent to telling me such a domain is expiring soon.

If any 1 can point me in the right direction, i'd be very greatful

Many thanks

Dan

[digital-ether]

Hello everyone,

Well i've just searched the forum to see if my question has been answered prioer to posting, but seem to find the answer.

Well basicly i've created a script to help me monitor and maintain the domain names my company owns (1000's)

I've virtually finished it but i am missing one bit - the auto email's reminding me when a domain will expire - I know you get the reminders from registrars but they only send to myself and others need to know this informaiton eg: Marketing, IT dept and i dont want them to have access to the domain contral panel at the registrar.

So i've been looking up possible ways to generate an automated email - but to no avail.

I have the following fields in my database:

domain_name, company_name, exp_date (Which is the expiry date in 0000-00-00format

So i was wondering if a cron job script can be run daily to check the domains that will expire in the next 7 days. For example if a domain had a exp_date as 2008-05-16 when the script is run on the 2008-05-09 an email will be sent to telling me such a domain is expiring soon.

If any 1 can point me in the right direction, i'd be very greatful

Many thanks

Dan

Which do you have problems with, writing the PHP script or adding the script to cron?

[oku]

Which do you have problems with, writing the PHP script or adding the script to cron?

The cron will be the easy part - its just structuring the script that i'm finding difficult

[digital-ether]

The cron will be the easy part - its just structuring the script that i'm finding difficult

Really depends on how you're updating the database. If you're updating just for the case of sending out emails when the domains expire, then you can just delete the rows in the db when you send out an email..

Your query:

SELECT domain_name from domains_table where exp_date > NOW();

Iterate through each row, send email and remove row. Otherwise, you could add another column with the email send timeboolean. So your query would be something like:

SELECT domain_name from domains_table where exp_date > NOW() AND sent_email == FALSE;

Is that what you're trying to do?

[oku]

Well - the query i would use would be very similar to what you posted -

where exp_date > NOW();

Once the domain has expired i dont intrend to delete the record from my database though.

The way i thought it would work would be:

When the domain is 1 week from expiring a query will be run checkin the dates in exp_date to see what domains are going to expire in 7 days

Then if there is a domain(s) about to expire in 7 days it will send an email reminder informing myself - and other members of staff that its goin to expire:

the email format will be something like this

Staff members name(s)

the following domain(s) will expire in 7 days

example.com
example.co.uk
example.net

Else no e-mail is sent until the cron job runs the next day
Or something similar - not to familiar with the mail function though in php

[digital-ether]

Well - the query i would use would be very similar to what you posted -

where exp_date > NOW();

Once the domain has expired i dont intrend to delete the record from my database though.

The way i thought it would work would be:

When the domain is 1 week from expiring a query will be run checkin the dates in exp_date to see what domains are going to expire in 7 days

Then if there is a domain(s) about to expire in 7 days it will send an email reminder informing myself - and other members of staff that its goin to expire:

the email format will be something like this

Staff members name(s)

the following domain(s) will expire in 7 days

example.com
example.co.uk
example.net

Else no e-mail is sent until the cron job runs the next day
Or something similar - not to familiar with the mail function though in php

that should work...

PHP mail function.

[oku]

Been lookin at that php mail function today - was able to send a simple email using the examples that are provided

still figuring out how to email results from my query to be sent via email

ive structured a script below - it doesnt work but you can see what i am after

<?php

$host = "localhost"; 
$user = "user_name"; 
$pass = "password"; 
$db = "domains";


mysql_connect($host,$user,$pass) or die("ERROR:".mysql_error());
mysql_select_db($db) or die("ERROR DB:".mysql_error()); 

$query="SELECT domain_name FROM domains WHERE exp_date > NOW()+7 ORDER BY domain_name ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();


$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$domain_name=mysql_result($result,$i,"domain_name");
$company_name=mysql_result($result,$i,"company_name");
$simply_account=mysql_result($result,$i,"simply_account");
$notes=mysql_result($result,$i,"notes");
$exp_date=mysql_result($result,$i,"exp_date");



$to      = '[e-mail]';
$subject = 'Domain renewall reminder';
$message = '


The following domains will expire in 7 days

<BR><BR>

$domain_name - $company_name<BR>

<BR><BR>

Dont forget to renew!<BR><BR>

Domain Team


';
$headers = 'From: [e-mail]' . "\r\n" .
    'Reply-To: [e-mail]' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
?>

Not sure where i am going wrong :(

[digital-ether]

Been lookin at that php mail function today - was able to send a simple email using the examples that are provided

still figuring out how to email results from my query to be sent via email

ive structured a script below - it doesnt work but you can see what i am after

<?php

$host = "localhost"; 
$user = "user_name"; 
$pass = "password"; 
$db = "domains";


mysql_connect($host,$user,$pass) or die("ERROR:".mysql_error());
mysql_select_db($db) or die("ERROR DB:".mysql_error()); 

$query="SELECT domain_name FROM domains WHERE exp_date > NOW()+7 ORDER BY domain_name ASC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();


$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$domain_name=mysql_result($result,$i,"domain_name");
$company_name=mysql_result($result,$i,"company_name");
$simply_account=mysql_result($result,$i,"simply_account");
$notes=mysql_result($result,$i,"notes");
$exp_date=mysql_result($result,$i,"exp_date");



$to      = '[e-mail]';
$subject = 'Domain renewall reminder';
$message = '


The following domains will expire in 7 days

<BR><BR>

$domain_name - $company_name<BR>

<BR><BR>

Dont forget to renew!<BR><BR>

Domain Team


';
$headers = 'From: [e-mail]' . "\r\n" .
    'Reply-To: [e-mail]' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
?>

Not sure where i am going wrong :(

try doing some dumps (var_dump or print_r) of your $message variable in the while loop to see if you're getting what you want.

[oku]

Hey digital-ether,

thanks for responding to my thread again.

I've been doing alot of reading and i've virtually clocked it

Now when the email sends - instead of just sending one email with the results of the query, it will send alot more

for example if the query returned 30 results it will send me 30 emails with each result of the query being in each email and i just want all results to appear one!

Here is the code im using - 90% there!

<?
mysql_connect("localhost","username","password"); 
mysql_select_db("database") or die( "Unable to select database"); 
 
 
$query="SELECT * FROM domains WHERE company_name = 'IWOOT' ORDER BY domain_name ASC";
$result=mysql_query($query);
 

$num=mysql_numrows($result);

mysql_close();


$i=0;
while ($i < $num) 

{
 
  
$id=mysql_result($result,$i,"id");
$domain_name=mysql_result($result,$i,"domain_name");
$company_name=mysql_result($result,$i,"company_name");
$simply_account=mysql_result($result,$i,"simply_account");
$notes=mysql_result($result,$i,"notes");


$exp_date = mysql_result($result,$i,"exp_date");
$exp_date = date("d-m-Y", strtotime($exp_date) );


if($query == TRUE)
    {
           
      //send email
   
      $to = "[e-mail]";
  
      $subject = "Expriy Notice";
   
      $from = "Admin";
   
  
      $msg .= "Hello Daniel,<BR><BR>";
  
      $msg .= "Domains for Iwoot...<BR><BR>";

      $msg .= "$domain_name - $exp_date <BR><BR>";  	

      $msg .= "Admin";
 
       
  
      $headers = "MIME-Version: 1.0" . "\r\n";
  
      $headers .= "Content-type: text/html; charset=iso-8859-1" . "\r\n";
  
      $headers .= "From: Daniel Whiteside\r\nReply-To:[e-mail]" . "\r\n";
 
       
 
      $mailsend = mail("$to","$subject","$msg","$headers");
  
      echo $mailsend ? "Email was sent :-)." : " Email sending failed.";


$i++;
}

} 

?>

Where am i going wrong?

Many thanks

[CademiaX]

It looks like you have the mail function inside the loop :)