automatic php email

[ianhaney50]

Is working perfect, is there a way of including just the date that is due to expire and not all 3 dates within the email

 

I got it like the following now

$sqlCommand = "SELECT visitor_id, visitor_name, visitor_email, visitor_model, visitor_plate, visitor_tax, visitor_mot, visitor_insurance
, DATE_FORMAT(visitor_tax, '%e %M %Y') as taxdate
, DATE_FORMAT(visitor_mot, '%e %M %Y') as motdate
, DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate
FROM visitors 
WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY";

at the mo I got all 3 dates showing in the email, see below

 

Name: Ian Haney

 Car Tax Expiry Date: 20 June 2015

 Car MOT Expiry Date: 21 June 2015

 Insurance Expiry Date: 28 June 2015

 

below is the email coding I have

$from = "noreply@irhwebsites.co.uk";
    $to = "{$row['visitor_email']}";
    $subject = "{$row['visitor_name']} Expiry Date(s)";
	$message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['taxdate']} \n Car MOT Expiry Date: {$row['motdate']} \n Insurance Expiry Date: {$row['vidate']}";
	$headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Email Successfully Sent";

I really do appreciate all the help on this

[Barand]

You can test the dates are in range when you output with PHP, eg

if (date('Y-m-d',strtotime($row['vidate'])) >= date('Y-m-d') && strtotime($row['vidate'])<= date('Y-m-d',strtotime("+7 days"))) {
	echo "Insurance Expiry Date: {$row['vidate']}<br/>";
}

or you can adjust the SQL

SELECT visitor_id
  , visitor_name 
  , visitor_email 
  , IF(visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY,
  		DATE_FORMAT(visitor_tax, '%e %M %Y') , 'n/a') as taxdate
  , IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY,
  		DATE_FORMAT(visitor_mot, '%e %M %Y' , 'n/a') as motdate
  , IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY,
  		DATE_FORMAT(visitor_insurance, '%e %M %Y' , 'n/a') as vidate 
FROM visitors 
WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
	OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
	OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY

Edited June 20, 2015 by Barand

[ianhaney50]

Ok cool

 

I done it the adjusting sql way

$sqlCommand = "SELECT visitor_id, visitor_name, visitor_email, visitor_model, visitor_plate, visitor_tax, visitor_mot, visitor_insurance
, IF(visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_tax, '%e %M %Y') , 'n/a') as taxdate
, IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_mot, '%e %M %Y' , 'n/a') as motdate
, IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_insurance, '%e %M %Y' , 'n/a') as vidate 
FROM visitors 
WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY";

So that will generate the email if either one is near the expiry date and in the email body will it just include the one that is near the expiry date cause am trying to work it out as currently it includes all 3 dates

$from = "noreply@irhwebsites.co.uk";
    $to = "{$row['visitor_email']}";
    $subject = "{$row['visitor_name']} Expiry Date(s)";
	$message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['taxdate']} \n Car MOT Expiry Date: {$row['motdate']} \n Insurance Expiry Date: {$row['vidate']}";
	$headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Email Successfully Sent";

[ianhaney50]

Sorry quick one, I uploaded the php file and is displaying the error

 

Incorrect parameter count in the call to native function 'DATE_FORMAT'

[Barand]

Sorry! Missed a couple of ")"s when I was pasting.

 

SELECT visitor_id
  , visitor_name
  , visitor_email
  , IF(visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY,
          DATE_FORMAT(visitor_tax, '%e %M %Y') , 'n/a') as taxdate
  , IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY,
          DATE_FORMAT(visitor_mot, '%e %M %Y') , 'n/a') as motdate
  , IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY,
          DATE_FORMAT(visitor_insurance, '%e %M %Y') , 'n/a') as vidate
FROM visitors
WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
    OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
    OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY

[ianhaney50]

Sorry think I sussed the issue, on the following lines, it did not have the ) after the date format coding

, IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_mot, '%e %M %Y') , 'n/a') as motdate
, IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_insurance, '%e %M %Y') , 'n/a') as vidate

it was like the following

, IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_mot, '%e %M %Y' , 'n/a') as motdate
, IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_insurance, '%e %M %Y' , 'n/a') as vidate

It is working perfect and love how the email only includes the date or dates that are near the expiry date and love how it says n/a if the expiry date is not near

 

One thing I have noticed though is on the php page, it's visitor_insurance date has came up as 01 January 1970 but in the database it is 2015-06-28?

[ianhaney50]

lol beat me to the little error

 

any idea with the date being changed to 01 January 1970 lol

Edited June 20, 2015 by ianhaney50

[Barand]

01 January 1970 is day 0 in the Unix world and usually signifies trying to format a zero or invalid date with php. Odd that SQL should do for 2015-06-28 eg

mysql> SELECT DATE_FORMAT('2015-06-28', '%e %M %Y') as date;
+--------------+
| date         |
+--------------+
| 28 June 2015 |
+--------------+

mysql> SELECT DATE_FORMAT('', '%e %M %Y') as date;
+------+
| date |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT DATE_FORMAT('0', '%e %M %Y') as date;
+------+
| date |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT DATE_FORMAT('2015-06-31', '%e %M %Y') as date;
+------+
| date |
+------+
| NULL |
+------+

[ianhaney50]

ahh wonder if is cause I added a new column at the end of my database table, I called it email_sent as thought would need the php to update the database so we know when a email has been sent so yeah called it email_sent and its type is TINYINT(4) with a default value of 0 cause was going to use 1 for sent and 0 for not sent

 

sorry didn't think that would affect it as was not calling it or anything yet in the PHP code

Edited June 20, 2015 by ianhaney50

[Barand]

I would be surprised if that were the cause. IMHO it might be more useful to make that extra field "date_last_notified" instead of just "status"

[Barand]

I need to go foraging for food so I'll be AFK for an hour or two

[ianhaney50]

yeah good call I'll make it date_last_modified rather than email_sent

 

I'll do that now

 

Yeah no worries I'll be on here for ages yet lol, thank you so much for all your help so far

[ianhaney50]

Sorry Barand

 

I have changed the column name in mysql database table to date_last_modified and made it TIMESTAMP as the type

[ianhaney50]

Been having a little play with the update of the mysql records after the email has been sent so we know when emails have been sent, I created the date_last_modified column and its type is TIMESTAMP

 

not sure if this will work but came up with the following coding after the email script

if($sendmail){
            $sqlCommand2 = "UPDATE visitors set date_last_modified='".time()."' WHERE visitor_id = " . $visitor_id['visitor_id'];
            
			$query2 = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db));
        } else {
		echo "do nothing";
		}

so the code with the email script looks like the following

$from = "noreply@irhwebsites.co.uk";
    $to = "{$row['visitor_email']}";
    $subject = "{$row['visitor_name']} Expiry Date(s)";
	$message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['taxdate']} \n Car MOT Expiry Date: {$row['motdate']} \n Insurance Expiry Date: {$row['vidate']}";
	$headers = "From:" . $from;
    $sendmail = mail($to,$subject,$message, $headers);
    echo "Email Successfully Sent";
	
	if($sendmail){
            $sqlCommand2 = "UPDATE visitors set date_last_modified='".time()."' WHERE visitor_id = " . $visitor_id['visitor_id'];
            
			$query2 = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db));
        } else {
		echo "do nothing";
		}
	
}

// Free the results  
mysqli_free_result($query);

mysqli_free_result($query2);

[fastsol]

time() won't work in this instance. time() returns a numeric number value, which is not the same as TIMESTAMP in mysql.   This should be more correct:

$sqlCommand2 = "UPDATE visitors set date_last_modified=NOW() WHERE visitor_id = " . $visitor_id['visitor_id'];

[Barand]

You should not be using time() to update the TIMESTAMP type column.

 

• An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

 

Set it to CURRENT_TIMESTAMP or NULL (if defined as NOT NULL)

[ianhaney50]

Hi fastsol

 

I have updated the coding to the line you provided

 

Hi Barand

 

I have checked and the type was already set to TIMESTAMP and set to CURRENT_TIMESTAMP under default, is that right?

 

i am getting this error though

 

Notice: Undefined variable: visitor_id in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 58 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Edited June 20, 2015 by ianhaney50

[fastsol]

Should probably be $row['visitor_id'] not $visitor_id['visitor_id']

[ianhaney50]

perfect, should of known that really as $row is above and makes sense

 

thank you so much, only thing I am confused on the php page, the date is 01 Jan 1970 instead of the date that is stored in the database?

 

The page says the following

 

success

3

Ian Haney

01 Jan 1970 <--- should be 28-06-2015
Email Successfully Sent

 

The line that produces that is below

echo date("d M Y",strtotime($row['vidate']));

Is it to do with that or the following coding

$sqlCommand = "SELECT visitor_id, visitor_name, visitor_email, visitor_model, visitor_plate, visitor_tax, visitor_mot, visitor_insurance
, IF(visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_tax, '%e %M %Y') , 'n/a') as taxdate
, IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_mot, '%e %M %Y') , 'n/a') as motdate
, IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_insurance, '%e %M %Y') , 'n/a') as vidate 
FROM visitors 
WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY";

[Barand]

If you are formatting the date in the query why are you trying to reformat it with PHP? Just output $row['vidate']

 

Define the timestamp field as

`date_last_modified`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Then in the update query you can use

SET date_last_modified = NULL

[Barand]

That also explains the 01 January 1970

echo date('d F Y', strtotime('n/a'));             //---> 01 January 1970

[ianhaney50]

Hi

 

Sorry for my late reply, I have done all that so now the line looks like the following

echo $row['vidate'];

Then the UPDATE line looks like the following

$sqlCommand2 = "UPDATE visitors SET date_last_modified = NULL WHERE visitor_id = " . $row['visitor_id'];

and the database looks like the following

 

16 date_last_modified timestamp   on update CURRENT_TIMESTAMP No CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

 

Is that all right?

 

as the page is now displaying n/a

 

http://www.broadwaymediadesigns.co.uk/sites/the-tax-elephants/auto-email.php

[Barand]

Looks OK. Did you you check that date_last_modified had been updated with the correct date and time?

[ianhaney50]

Ahh ok so sorry yeah think I got it now cause it's not within 7 days, 28th is 8 days away

 

the date last modified date and time in that column is 2015-06-20 21:59:13

 

just refreshed the page and has now updated in the column to 2015-06-20 22:08:19

Edited June 20, 2015 by ianhaney50

[ianhaney50]

Thank you so much Barand and fastsol for all your replies and help, really appreciate it so so much