ianhaney50

Ahh ok, am guessing there a issue then if some are being notified twice on the same day and none are being notified again after 7 days?

Ahh don't think so I have it as the following $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db));

I cleared out the renewal table date notified using UPDATE renewal SET date_notified = NULL; and is still sending the emails out and displaying the data which is good but is hard to tell if the second email gets sent out as guessing won't know until the date is within 7 days as guess the email is being sent cause the dates are within 14 days?

Hi I have added more visitors in and got the coding in as below and has sent the emails and also is outputting the data on the php page so not sure if the coding below is working or is just picking up on the first part of the coding, ie the sqlCommand coding and ignoring the sqlCommand2 coding $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db)); Below is what is being outputted on the php page Name: Ian Haney Tax expiry date: 26 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 27 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 28 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 30 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 30 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 26 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 26 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 28 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 29 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 28 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 28 June 2015 Email Successfully Sent Name: Ian Haney Tax expiry date: 28 June 2015 Email Successfully Sent I looked on the link you provided, I look at that site sometimes but goes over my head that site cause is difficult to understand the way they lay it out etc.

Def is not the following way $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand, $sqlCommand2) or die (mysqli_error($db)); as I got a error saying the following Warning: mysqli_query() expects parameter 3 to be long, string given in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 56

I put it in and got nothing through or displayed on the php page the following coding, does it work by seeing if the date in the database is within 14 days for example, is that right? $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; If so thought the 25-06-2015 one would show? The coding I got is below $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand, $sqlCommand2) or die (mysqli_error($db)); or would I need it like the following $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db));

At the mo I got the three dates as 25-06-2015, 25-07-2015 and 25-08-2015 I'll put that coding in with the second sql query and see what happens lol

I thought can it be done with multiple sql queries so like the following $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; Would that work?

Sorry got a little question to see if is possible Is it possible to have the email that is sent out send if a date is within 14 days and then a second email sent out if a date is within 7 days Is that possible, sorry

haha I bet got another little part to do for this project it but am going to try and work it out for myself first and see how far I get with it

Ahhh thank you so much, is perfect wish I was able to help more than I did, I know I must of annoyed you at times through my stupidity, so sorry if I did

Sorry been playing around with it some more and I took out the date_notified part in the sql query as figured that was why nothing was showing so now got the table showing with records but the following is missing from the table address line 1 address line 2 town county postcode telephone number mobile number I have the following errors Notice: Undefined index: visitor_firstline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 92 Notice: Undefined index: visitor_secondline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 93 Notice: Undefined index: visitor_town in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 94 Notice: Undefined index: visitor_county in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 95 Notice: Undefined index: visitor_postcode in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 96 Notice: Undefined index: visitor_tel in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 97 Notice: Undefined index: visitor_mobile in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 98 Notice: Undefined index: visitor_tax in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 101 Notice: Undefined index: visitor_firstline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 92 Notice: Undefined index: visitor_secondline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 93 Notice: Undefined index: visitor_town in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 94 Notice: Undefined index: visitor_county in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 95 Notice: Undefined index: visitor_postcode in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 96 Notice: Undefined index: visitor_tel in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 97 Notice: Undefined index: visitor_mobile in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 98 Notice: Undefined index: visitor_tax in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 101 Notice: Undefined index: visitor_firstline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 92 Notice: Undefined index: visitor_secondline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 93 Notice: Undefined index: visitor_town in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 94 Notice: Undefined index: visitor_county in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 95 Notice: Undefined index: visitor_postcode in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 96 Notice: Undefined index: visitor_tel in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 97 Notice: Undefined index: visitor_mobile in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 98 Notice: Undefined index: visitor_tax in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 101 Notice: Undefined index: visitor_firstline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 92 Notice: Undefined index: visitor_secondline in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 93 Notice: Undefined index: visitor_town in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 94 Notice: Undefined index: visitor_county in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 95 Notice: Undefined index: visitor_postcode in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 96 Notice: Undefined index: visitor_tel in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 97 Notice: Undefined index: visitor_mobile in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 98 Notice: Undefined index: visitor_tax in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 101 01 January 1970 Notice: Undefined index: visitor_mot in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 102 01 January 1970 Notice: Undefined index: visitor_insurance in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 103 01 January 1970 Notice: Undefined index: date_notified in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/admin/index.php on line 104 The coding I have is below $sql = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sql) or die (mysqli_error($db)); if($result = mysqli_query($db, $sql)){ if(mysqli_num_rows($result) > 0){ echo "<div id='column-whole-main'>"; echo "<table cellspacing='0'>"; echo "<thead>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Name</th>"; echo "<th>Email</th>"; echo "<th>Address Line 1</th>"; echo "<th>Address Line 2</th>"; echo "<th>Town</th>"; echo "<th>County</th>"; echo "<th>Postcode</th>"; echo "<th>Telephone Number</th>"; echo "<th>Mobile Number</th>"; echo "<th>Vehicle Model</th>"; echo "<th>Vehicle Number Plate</th>"; echo "<th>Vehicle Tax Expiry Date</th>"; echo "<th>Vehicle MOT Expiry Date</th>"; echo "<th>Vehicle Insurance Expiry Date</th>"; echo "<th>Email Sent</th>"; echo "</tr>"; echo "</thead>"; //fetch the data from the database while ($row = mysqli_fetch_array($query)) { echo "<tbody>"; echo "<tr>"; echo "<td>" . $row['visitor_id'] . "</td>"; echo "<td>" . $row['visitor_name'] . "</td>"; echo "<td>" . $row['visitor_email'] . "</td>"; echo "<td>" . $row['visitor_firstline'] . "</td>"; echo "<td>" . $row['visitor_secondline'] . "</td>"; echo "<td>" . $row['visitor_town'] . "</td>"; echo "<td>" . $row['visitor_county'] . "</td>"; echo "<td>" . $row['visitor_postcode'] . "</td>"; echo "<td>" . $row['visitor_tel'] . "</td>"; echo "<td>" . $row['visitor_mobile'] . "</td>"; echo "<td>" . $row['visitor_model'] . "</td>"; echo "<td>" . $row['visitor_plate'] . "</td>"; echo "<td>" . date("d F Y",strtotime($row['visitor_tax']));"</td>"; echo "<td>" . date("d F Y",strtotime($row['visitor_mot']));"</td>"; echo "<td>" . date("d F Y",strtotime($row['visitor_insurance']));"</td>"; echo "<td>" . date("d F Y G:i:s",strtotime($row['date_notified']));"</td>"; echo "</tr>"; } echo "</table>"; echo "</div>"; // Close result set mysqli_free_result($result); } else{ echo "No records matching your query were found."; } } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($db); } Sorry am trying to work it out myself at the same time

Think I worked it out and why I am getting No records matching your query were found. on the admin side I am guessing because the php page has been called and a email has been sent out to the date_notified column has updated to the current time so no results are displaying, is that right?

Hi Sorry have altered some coding below - UPDATE Sorry last thing I promise I have made a table that will list all visitors and their info etc. and was working but obviously changing all the coding etc. it has stopped working and is displaying nothing now apart from the header and footer coding // Attempt select query execution $sql = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sql) or die (mysqli_error($db)); //NEW LINES ADDED if ($query->num_rows==0 ) { echo "No reminders due"; } $current_visitor=0; $renewals = array(); $renewals = array(); if($result = mysqli_query($db, $sql)){ if(mysqli_num_rows($result) > 0){ echo "<div id='column-whole-main'>"; echo "<table cellspacing='0'>"; echo "<thead>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Name</th>"; echo "<th>Email</th>"; echo "<th>Address Line 1</th>"; echo "<th>Address Line 2</th>"; echo "<th>Town</th>"; echo "<th>County</th>"; echo "<th>Postcode</th>"; echo "<th>Telephone Number</th>"; echo "<th>Mobile Number</th>"; echo "<th>Vehicle Model</th>"; echo "<th>Vehicle Number Plate</th>"; echo "<th>Vehicle Tax Expiry Date</th>"; echo "<th>Vehicle MOT Expiry Date</th>"; echo "<th>Vehicle Insurance Expiry Date</th>"; echo "<th>Email Sent</th>"; echo "</tr>"; echo "</thead>"; //fetch the data from the database while ($row = mysqli_fetch_array($query)) { echo "<tbody>"; echo "<tr>"; echo "<td>" . $row['visitor_id'] . "</td>"; echo "<td>" . $row['visitor_name'] . "</td>"; echo "<td>" . $row['visitor_email'] . "</td>"; echo "<td>" . $row['visitor_firstline'] . "</td>"; echo "<td>" . $row['visitor_secondline'] . "</td>"; echo "<td>" . $row['visitor_town'] . "</td>"; echo "<td>" . $row['visitor_county'] . "</td>"; echo "<td>" . $row['visitor_postcode'] . "</td>"; echo "<td>" . $row['visitor_tel'] . "</td>"; echo "<td>" . $row['visitor_mobile'] . "</td>"; echo "<td>" . $row['visitor_model'] . "</td>"; echo "<td>" . $row['visitor_plate'] . "</td>"; echo "<td>" . date("d F Y",strtotime($row['datedue']));"</td>"; echo "<td>" . date("d F Y",strtotime($row['datedue']));"</td>"; echo "<td>" . date("d F Y",strtotime($row['datedue']));"</td>"; echo "<td>" . date("d F Y G:i:s",strtotime($row['date_notified']));"</td>"; echo "</tr>"; } echo "</table>"; echo "</div>"; // Close result set mysqli_free_result($result); } else{ echo "No records matching your query were found."; } } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($db); } Sorry am trying to do it myself and going by the other php page with all the coding that works to get bits from and get it working, sorry thought would be better for me to have a go than keep asking

Ok cool done that and now the php page has returned the following output on the page Name: Ian Haney Tax expiry date: 23 June 2015 Insurance expiry date: 24 June 2015 M.O.T expiry date: 25 June 2015 Email Successfully Sent Just added a new visitor and put in dates of 25-06-2015, 25-07-2015 and 25-08-2015 and is working perfect as now emails with the date that is due to expire within 14 days Thank you so so much for your help and coding, really appreciate it I have picked up somethings along the way through this, is just getting the head around it

The first line UPDATE renewal SET date_notified = NULL; Do I put that in phpmyadmin to reset the renewal table or adjust the php coding?

Hi, sorry for my late reply No not getting no output message saying success or failed the php page itself is blank

Ahh ok I decided to take it out so now the coding is below $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $current_visitor=0; $current_email = ''; $headers = "From: noreply@irhwebsites.co.uk\r\n"; $subject = "Expiry Date(s)"; $message = ''; $renewals = array(); $notifications = array(); //fetch the data from the database while ($row = mysqli_fetch_array($query)) { // has visitor_id changed if ($row['visitor_id'] != $current_visitor) { // send email to current visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add renewal ids to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } $current_visitor = $row['visitor_id']; $current_email = $row['visitor_email']; $message = "Name: {$row['visitor_name']} \n\n"; $renewals = array(); } $message .= "{$row['description']} expiry date: {$row['datedue']}\n"; $renewals[] = $row['renewal_id']; // store renewal id for updates } // send email to final visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } // update successful notifications $idlist = join(',', $notifications); $sql = "UPDATE renewal SET date_notified = NOW() WHERE renewal_id IN ($idlist)"; $db->query($sql) ; // Free the results mysqli_free_result($query); Is that all ok as not receiving any emails?

Ahh ok, is this right? $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $current_visitor=0; $current_email = ''; $headers = "From: noreply@irhwebsites.co.uk\r\n"; $subject = "Expiry Date(s)"; $message = ''; $renewals = array(); $notifications = array(); //fetch the data from the database while ($row = mysqli_fetch_array($query)) { $count = 1; // has visitor_id changed if ($row['visitor_id'] != $current_visitor) { // send email to current visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add renewal ids to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } $current_visitor = $row['visitor_id']; $current_email = $row['visitor_email']; $message = "Name: {$row['visitor_name']} \n\n"; $renewals = array(); } $message .= "{$row['description']} expiry date: {$row['datedue']}\n"; $renewals[] = $row['renewal_id']; // store renewal id for updates if ($count % 5 == 0) { sleep(5); // this will wait 5 secs every 5 emails sent, and then continue the while loop } $count++; } // send email to final visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } // update successful notifications $idlist = join(',', $notifications); $sql = "UPDATE renewal SET date_notified = NOW() WHERE renewal_id IN ($idlist)"; $db->query($sql) ; // Free the results mysqli_free_result($query);

Hi Thank you so much, just to confirm I have the following php coding now <?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 { /*SUCCESS MSG*/ echo ''; } $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $current_visitor=0; $current_email = ''; $headers = "From: noreply@irhwebsites.co.uk\r\n"; $subject = "Expiry Date(s)"; $message = ''; $renewals = array(); $notifications = array(); /*$count = 1;*/ //fetch the data from the database while ($row = mysqli_fetch_array($query)) { // has visitor_id changed if ($row['visitor_id'] != $current_visitor) { // send email to current visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); echo "<b>Email Successfully Sent</b><br><br>"; // success, so add renewal ids to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } $current_visitor = $row['visitor_id']; $current_email = $row['visitor_email']; $message = "Name: {$row['visitor_name']} \n\n"; $renewals = array(); } $message .= "{$row['description']} expiry date: {$row['datedue']}\n"; $renewals[] = $row['renewal_id']; // store renewal id for updates } // send email to final visitor if ($current_visitor != 0) { $to = $current_email; $sendmail = mail($to, $subject, $message, $headers); if ($sendmail) { echo nl2br($message); /*if ($count % 5 == 0) { sleep(5); // this will wait 5 secs every 5 emails sent, and then continue the while loop } $count++;*/ echo "<b>Email Successfully Sent</b><br><br>"; // success, so add to notifications $notifications = array_merge($notifications,$renewals); } else { echo "<b>Error in Sending of Email to $to</b><br><br>"; } } // update successful notifications $idlist = join(',', $notifications); $sql = "UPDATE renewal SET date_notified = NOW() WHERE renewal_id IN ($idlist)"; $db->query($sql) ; // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> Only confirming as not received any email yet? Sorry

I just changed renewal_date to datedue in the email script part and still get a blank white page on the php page which will worry about later but the email comes through with the following Name: Ian Haney Car Tax Expiry Date: 23 June 2015 Car MOT Expiry Date: 23 June 2015 Insurance Expiry Date: 23 June 2015 which all them dates is from the date_notified column in the renewal table The coding line I changed from is below $message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['renewal_date']} \n Car MOT Expiry Date: {$row['renewal_date']} \n Insurance Expiry Date: {$row['renewal_date']}"; TO $message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['datedue']} \n Car MOT Expiry Date: {$row['datedue']} \n Insurance Expiry Date: {$row['datedue']}"; Sorry trying to suss it on my own but not having much luck, sorry I just received another email where all the dates in the email are 24-06-2015 am confused now

Sorry sussed the unknown column error I had the following $sqlCommand2 = "UPDATE visitors SET date_notified = NOW() WHERE visitor_id = " . $row['visitor_id']; I forgot to change it to renewal instead of visitors so now looks like $sqlCommand2 = "UPDATE renewal SET date_notified = NOW() WHERE visitor_id = " . $row['visitor_id']; It is working now apart from the php page is not displaying anything and no email being received, the dates in the database are below Tax: 23-06-2015 Insurance: 24-06-2015 MOT: 25-06-2015 My coding for the auto-email.php page is below <?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 { /*SUCCESS MSG*/ echo ''; } $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); /*$count = 1;*/ //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 = "{$row['visitor_email']}"; $subject = "{$row['visitor_name']} Expiry Date(s)"; $message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['renewal_date']} \n Car MOT Expiry Date: {$row['renewal_date']} \n Insurance Expiry Date: {$row['renewal_date']}"; $headers = "From:" . $from; $sendmail = mail($to,$subject,$message, $headers); /*if ($count % 5 == 0) { sleep(5); // this will wait 5 secs every 5 emails sent, and then continue the while loop } $count++;*/ if ($sendmail) { echo "Email Successfully Sent"; } else { echo "Error in Sending of Email"; } if($sendmail){ $sqlCommand2 = "UPDATE renewal SET date_notified = NOW() WHERE visitor_id = " . $row['visitor_id']; $query2 = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db)); } else { echo "do nothing"; } } // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?>

Hi I have changed the line of coding in the email script to the following $message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['datedue']} \n Car MOT Expiry Date: {$row['datedue']} \n Insurance Expiry Date: {$row['datedue']}"; Is that right? it has got rid of the errors apart from the following Unknown column 'date_notified' in 'field list' I checked the renewal table and the column is called date_notified

Oh right cool what do you mean the processing, sorry, do you mean the output within the email? The php page is displaying the following 29 Ian Haney Notice: Undefined index: taxdate in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 59 Notice: Undefined index: motdate in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 59 Notice: Undefined index: vidate in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 59 Email Successfully Sent Unknown column 'date_notified' in 'field list'

Thank you so much, is perfect now just thought would test the email to make sure that sends still and have not received anything yet, I know the other day it was delayed a bit but thought it would come through by now, is it ok to quickly glance over the coding and make sure is all ok, it looks all ok from what I can see <?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 { /*SUCCESS MSG*/ echo ''; } $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND date_notified < CURDATE()-INTERVAL 14 DAY;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); /*$count = 1;*/ //fetch tha data from the database while ($row = mysqli_fetch_array($query)) { /*echo $sqlCommand;*/ ini_set( 'display_errors', 1 ); error_reporting( E_ALL ); $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); /*if ($count % 5 == 0) { sleep(5); // this will wait 5 secs every 5 emails sent, and then continue the while loop } $count++;*/ if ($sendmail) { echo "Email Successfully Sent"; } else { echo "Error in Sending of Email"; } if($sendmail){ $sqlCommand2 = "UPDATE visitors SET date_notified = NOW() WHERE visitor_id = " . $row['visitor_id']; $query2 = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db)); } else { echo "do nothing"; } } // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?>