Jump to content

automatic php email


ianhaney50
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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 by Barand
  • Like 1
Link to comment
Share on other sites

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";
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 |
+------+

Link to comment
Share on other sites

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 by ianhaney50
Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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'];
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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 by ianhaney50
Link to comment
Share on other sites

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";
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by ianhaney50
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.