ianhaney50

Hi I have been looking into the timestamp to prevent the same emails being sent when I refresh the page or page load would something like the following be close to what you mentioned about using timestamp to prevent receiving the same email <?php session_start(); $sTime = time() - 15; if($sTime > $_SESISON['submit_timestamp']){ // process form } else { // some sort of error handling if you want it. } ?>

ahh yeah that would be good, I'll google it now and see what I can find out or have a play around as feel bad keep asking how on here, is good for me to have a go myself first with it and only ask for help if I get stuck

yeah lol I have set the email address to mine for now while is being built and during the testing process

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

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

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

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";

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

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);

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

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

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

lol beat me to the little error any idea with the date being changed to 01 January 1970 lol

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?

Sorry quick one, I uploaded the php file and is displaying the error Incorrect parameter count in the call to native function 'DATE_FORMAT'

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";

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

Ahh ok got ya, all makes perfect sense as is pointless having loads of columns that is not needed to show in the email spot on with point 2 if ok to copy the coding you provided above but makes sense to have it as being between 2 dates, sorry just learning and understanding it as I go

Sorry I did take on board the bit you said about the SELECT * but wanted everything in or is it best to not have the * and list what I need one by one The conditions I am wanting is in my database table I have a date for visitor_tax, a different date for visitor_mot and a different date for visitor_insurance and I want the email to be sent out to the email address for the visitor to let them know the expiry date for them 3 things is 7 days away Hope that makes sense I'll def change the SELECT * to listing what I need one by one if is better to do it that way than having select *

Sorry am stuck on this bit, I have added in more into the $sql Command query but am getting a error $sqlCommand = "SELECT * , DATE_FORMAT(visitor_tax, '%e %M %Y') (visitor_mot, '%e %M %Y') (visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_tax, visitor_mot, visitor_insurance > DATE_ADD(CURDATE(), INTERVAL 7 DAY)"; // no limit I first tried the following $sqlCommand = "SELECT * , DATE_FORMAT(visitor_tax, visitor_mot, visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_tax, visitor_mot, visitor_insurance > DATE_ADD(CURDATE(), INTERVAL 7 DAY)"; // no limit but it didn't like that either Sorry the error I am getting with the current script which is the second lot of coding above Incorrect parameter count in the call to native function 'DATE_FORMAT'

Sorry is ok now and receiving the emails, so now will this script check for any visitor_insurance dates that is due to expire within 7 days and send me a email with the info that right? I know I need the cron to check the php file every day but just need to know if the script is right?

Thank you fastsol I change the line to $message = "Name: {$row['visitor_name']} \n Insurance Expiry Date: {$row['vidate']}"; but no email is coming through now unless it's delayed but been getting them 15 secs after I reload the page

I've got the following Barand but its still making the date yyyy-mm-dd in the email I receive $sqlCommand = "SELECT * , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_insurance > DATE_ADD(CURDATE(), INTERVAL -1 DAY)"; // no limit Below is my email coding $from = "noreply@irhwebsites.co.uk"; $to = "ianhaney@irhwebsites.co.uk"; $subject = "PHP Mail Test script"; /*$message = "This is a test to check the PHP Mail functionality";*/ $message = "Name: {$row['visitor_name']} \n Insurance Expiry Date: {$row['visitor_insurance']};"; $headers = "From:" . $from; mail($to,$subject,$message, $headers); echo "Test email sent"; The email comes out like the following Name: Ian Haney Insurance Expiry Date: 2015-06-28;

Hi Barand Thank you for the reply and coding, I got the following and it seems to be working I think, it is outputting the insurance date that expires on the 28th June 2015 $sqlCommand = "SELECT * FROM visitors WHERE visitor_insurance > DATE_ADD(NOW(), INTERVAL -1 DAY)"; // no limit Is that right? The other little thing I am trying to work out is the email sends all ok which is good but the date format in the email is 2015-06-28 where as I am trying to get it in UK date format, I have got it displaying in UK format on the page when it is being outputte, below is my whole code so far <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <html> <title>Automatic Email</title> <body> <?php $db = mysqli_connect("" , "", "") or die("Check connection parameters!"); // Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname) mysqli_select_db($db,"") or die(mysqli_error($db)); if (mysqli_connect_error()) { die ('Failed to connect to MySQL'); } else { echo 'success'; } $sqlCommand = "SELECT * FROM visitors WHERE visitor_insurance > DATE_ADD(NOW(), INTERVAL -1 DAY)"; // no limit $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); //fetch tha data from the database while ($row = mysqli_fetch_array($query)) { echo "<br><br>"; echo $row['visitor_id']; echo "<br><br>"; echo $row ['visitor_name']; echo "<br><br>"; echo date("d F Y",strtotime($row['visitor_insurance'])); echo "<br />"; /*echo $sqlCommand;*/ ini_set( 'display_errors', 1 ); error_reporting( E_ALL ); $from = "noreply@irhwebsites.co.uk"; $to = "ianhaney@irhwebsites.co.uk"; $subject = "PHP Mail Test script"; /*$message = "This is a test to check the PHP Mail functionality";*/ $message = "Some text {$row['visitor_name']}"; $message = "Some text {$row['visitor_insurance']}"; $headers = "From:" . $from; mail($to,$subject,$message, $headers); echo "Test email sent"; } // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> </body> </html> In my database I got the date columns as the type DATE, is that ok as that?

Hi, sorry got a update I am getting there I think, I put in echo $sqlcommand and echoed that out all ok so then put in the echo $row results so it displayed the results from the mysql database table and has done that ok as I put WHERE clause in and defined what WHERE clause values and outputted all ok so think the issue was with the following line $query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)"; So what would be the correct coding to be where the results are displayed if the date from visitor_mot is tomorrow for example so want it to display if the date is 1 day away, hope that makes sense My current coding is below <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <html> <title>Automatic Email</title> <body> <?php $db = mysqli_connect("" , "", "") or die("Check connection parameters!"); // Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname) mysqli_select_db($db,"") or die(mysqli_error($db)); if (mysqli_connect_error()) { die ('Failed to connect to MySQL'); } else { echo 'success'; } $sqlCommand = "SELECT * FROM visitors WHERE visitor_county='Essex' AND visitor_id='3' "; // no limit $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); /*$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)"; $result = mysqli_query($db, $query) or die(mysqli_error($db));*/ //fetch tha data from the database while ($row = mysqli_fetch_array($query)) { echo "<br><br>"; echo $row['visitor_id']; echo "<br><br>"; echo $row ['visitor_name']; echo "<br />"; /*echo $sqlCommand;*/ /*ini_set( 'display_errors', 1 ); error_reporting( E_ALL ); $from = "noreply@irhwebsites.co.uk"; $to = "ianhaney@irhwebsites.co.uk"; $subject = "PHP Mail Test script"; $message = "This is a test to check the PHP Mail functionality"; $headers = "From:" . $from; mail($to,$subject,$message, $headers); echo "Test email sent";*/ } // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> </body> </html> Feel like I am getting closer with it slowly