Send automatic email by date HELP!

[chris331]

HI everyone,

This is my first post and I will try to be clear as possible.

My client would like to store a code for each customer that they have, the code expiry’s after x amount of months, and my client wants me to build a system that stores the “customers name, the code, expiry date and a reminder date”.
the system has to flag up visually on screen for the client to see when a customers reminder date is current date or passed, plus this needs to send a e-mail reminder to the customer.

So far I have developed a MySql database to store the information, I have developed a website that displays a table of information and allows the user to edit or delete, and the user can add a new customer.

Could someone please help me write a script that will check the current date every day and then email the customer a reminder? and how I can I get it to run on the server side?

I my mind this is what I want to do,

Check today’s date
Store date in a variable ($var)
then run Sql query to find customers where reminder date is = to $var
then store results in a array
then for each customer in the array send a reminder e-mail.

I would really appreciate your help.

[Dormilich]

@chris331

how I can I get it to run on the server side?

With a cronjob (Linux) or Scheduled Task (Windows).

[chris331]

Thank you for the reply,

Igot cron job set up with my webhost just need to figure out how to write the php script to send the e-mail, for each customer.

[Dormilich]

best use an existing mailing library, e.g. http://swiftmailer.org

[SamA74]

If the expiry date is in the table you could have a query to select all customers X days before that date to send the reminder.

"SELECT id, name, email, code, whateverElse FROM customers WHERE expiryDate = DATE_ADD(NOW(), INTERVAL 10 DAY)"

So run daily on a cron job, this would select all customers who’s expiry date is in 10 days time.
You then just need to send the emails to the results.

[chris331]

Hi guys

Just and update, I have managed to send an email with a cronjob, but now I am trying to connect to the database and run a query to select all That match current date (todays date) then to send a email to each user from the results of the query.

I doesn’t seem to be working here is my code:`

<?php

$currentDate =  date('Y-m-d'); //corrected


$host_name = "";
$database = "";
$user_name = "";
$password = "";

$dbcon = mysqli_connect($host_name, $user_name, $password, $database);
if($dbcon->connect_error) die($dbcon->connect_error);

$remind_query = "SELECT forename, email From Bridges30 WHERE expiry = '$currentDate'">
$run = mysqli_query($dbcon, $remind_query);



while($row=mysqli_fetch_array($run))
{
	            $parID=$row[0];
				$parForename=$row[1];
				$parSurname=$row[2];
				$parChild=$row[3];
				$parEmail=$row[4];
				$parCode=$row[5];
				$parExpiry=$row[6];
				$parReminder=$row[7];
				$parGrace=$row[8];
				
	$to = $parEmail;
    $subject = "Code 30 Reminder";
    $txt = "Hi ".$parForename."\nJust a reminder that your code 30 will expiry on the".$parExpiry.",\n"
	            ."Please renew your code\n"."\nThank You"."\nBridges Nursery";
	
    $headers = "From: Bridges@email.com";
    

mail($to,$subject,$txt,$headers);
}`

Any Ideas

[SamA74]

@chris331

$currentDate = (‘Y/m/d’);

How does this work?
Maybe what yo meant to have was more like:-

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

or rather:-

$currentDate = date('Y-m-d');

…which is the correct format for an sql date field.

Or you could simply use NOW() in the query to match today’s date.

[chris331]

AH

Thanks totally missed That!

I have change that and I am still not receiving emails when I test the cron job?

Thank you.

[droopsnoot]

Here, you run a query to return two columns from the database table:

$remind_query = "SELECT forename, email From Bridges30 WHERE expiry = '$currentDate'">

and here, you try to use the fifth column as the to-address:

$parEmail=$row[4];
...
$to = $parEmail;

[chris331]

Hi Guys,

Just an update I manage to get it working and I thought I would share my the code.

<?php


$currentDate = date('Y-m-d'); //this will get the current date ie when this was posted 2107-07-06


$host_name = "  ";
$database = "  ";
$user_name = "  ";
$password = "  ";

    $dbcon = mysqli_connect($host_name, $user_name, $password, $database); //connect to database
    if($dbcon->connect_error) die($dbcon->connect_error);

	  $remind_query1 = "SELECT * FROM Bridges30 WHERE reminder= '$currentDate' "; //Sql query to find users that reminders dates match current date.
    
	if($run1 = $dbcon->query($remind_query1))
    {
	 $rows = $run1->num_rows;
      
      for ($j = 0; $j < $rows; ++$j)//loop through each user in results and send a reminder email.
      {
      	$run1->data_seek($j);
      	$row = $run1->fetch_array(MYSQLI_NUM);
      	
      	$to = $row[4]; //gets the user email address 
          $subject = "Code 30 Reminder";
          $message = "Hi ".$row[1]."\nJust a reminder that your code 30 will expiry on the ".$row[6].",\n"
      	            ."Please renew your code\n"."\nThank You"."\nBridges Nursery";
        $headers = "From: Bridges Nursery";
      	
		  mail($to,$subject,$message,$headers);
      }
		
	}
?>

hope this helps?