Barand Posted June 24, 2015 Share Posted June 24, 2015 I have a suspicion that approach would send the same notifications twice on the same day in some instances but the way to find out is to set up some test data and see. Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514778 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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 Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514779 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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)); Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514780 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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 Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514781 Share on other sites More sharing options...
Barand Posted June 24, 2015 Share Posted June 24, 2015 http://php.net/manual/en/mysqli.query.php Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514786 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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 HaneyTax expiry date: 26 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 27 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 28 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 30 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 30 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 26 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 26 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 28 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 29 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 28 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 28 June 2015Email Successfully SentName: Ian HaneyTax expiry date: 28 June 2015Email 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. Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514793 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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? Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514794 Share on other sites More sharing options...
Barand Posted June 24, 2015 Share Posted June 24, 2015 Are you processing the results from the first query before you call and process the second query? If not, the results from the second overwrite the results from the first. Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514814 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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)); Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514826 Share on other sites More sharing options...
Barand Posted June 24, 2015 Share Posted June 24, 2015 I set up a test script to run each day for 15 days (to simulate running each day, it's quicker than waiting couple of weeks for CURDATE() to catchup) $db->query("UPDATE renewals SET date_notified=NULL"); // clear dates $results = array(); $dt1 = new DateTime('2015-06-24'); // date of first run $dp = new DatePeriod($dt1, new DateInterval('P1D'), 15); foreach ($dp as $d) { $rundate = $d->format('Y-m-d'); reminders($db, $rundate, 14, $results); reminders($db, $rundate, 7, $results); } echo '<pre>'; foreach ($results as $dt=>$darray) { echo $dt."\n"; foreach ($darray as $days=>$ids) { printf ("\t%2d\t%s\n", $days, join(', ', $ids)); } } echo '<pre>'; function reminders($db, $rundate, $days, &$results) { $sql = "SELECT v.visitor_id , renewal_id , renewal_date FROM visitor v INNER JOIN renewal USING (visitor_id) WHERE renewal_date BETWEEN '$rundate' AND '$rundate'+INTERVAL $days DAY AND IFNULL(date_notified, '1901-01-01') < '$rundate'-INTERVAL $days DAY ORDER BY renewal_date, renewal_id"; $res = $db->query($sql); while (list($vid, $rid, $rdate) = $res->fetch_row()) { $db->query("UPDATE renewal SET date_notified = '$rundate' WHERE renewal_id=$rid"); $results[$rdate][$days][] = $rid; } } The results show the renewals emailed/updated each day by the 14 day and 7 day versions of your query. Note that, as I suspected, some are notified twice on the same day. None were notified again after 7 days. RESULTS 2015-06-26 14 8 2015-06-27 14 12 2015-06-29 14 10 2015-07-02 14 11 7 11 2015-07-08 14 9 7 9 Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514845 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 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? Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514849 Share on other sites More sharing options...
Barand Posted June 24, 2015 Share Posted June 24, 2015 Forget the last post of mine - full of errors. Working on a revised version. Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514851 Share on other sites More sharing options...
ianhaney50 Posted June 24, 2015 Author Share Posted June 24, 2015 Ok no worries, thank you so much really appreciate it Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514852 Share on other sites More sharing options...
Barand Posted June 24, 2015 Share Posted June 24, 2015 I got renewal and run dates in a twist with previous results. This time it looks OK RunDate Qry RenewDate(id) ----------------------|---|-------------------------------------- 2015-06-12 14 2015-06-26( <-+ 2015-06-13 | 14 2015-06-27(12) <-+ | 2015-06-15 | | 14 2015-06-29(10) | | <-+ 2015-06-18 | | | 14 2015-07-02(11) | | | <-+ 2015-06-20 | | | | 7 2015-06-26( | <-+ | | 2015-06-21 | | | 7 2015-06-27(12) <-+ | | 2015-06-23 | | 7 2015-06-29(10) <-+ | 2015-06-24 | 14 2015-07-08(9) <-+ | 2015-06-26 | | 14 2015-07-10(7) | <-+ | 7 2015-07-02(11) | | <-+ 2015-07-02 | | 7 2015-07-08(9) <-+ | 2015-07-04 | 7 2015-07-10(7) <-+ Code $db->query("UPDATE renewal SET date_notified=NULL"); // clear dates $results = array(); $dt1 = new DateTime('2015-06-12'); // date of first run $dp = new DatePeriod($dt1, new DateInterval('P1D'), 30); foreach ($dp as $d) { $rundate = $d->format('Y-m-d'); reminders($db, $rundate, 14, $results); reminders($db, $rundate, 7, $results); } echo '<pre>'; foreach ($results as $dt=>$darray) { echo $dt."\n"; foreach ($darray as $days=>$ids) { printf ("\t%2d\t%s\n", $days, join(', ', $ids)); } } echo '<pre>'; function reminders($db, $rundate, $days, &$results) { $sql = "SELECT v.visitor_id , renewal_id , renewal_date FROM visitor v INNER JOIN renewal USING (visitor_id) WHERE renewal_date BETWEEN '$rundate' AND '$rundate'+INTERVAL $days DAY AND IFNULL(date_notified, '1901-01-01') < '$rundate'-INTERVAL $days DAY ORDER BY renewal_id"; $res = $db->query($sql); while (list($vid, $rid, $rdate) = $res->fetch_row()) { $db->query("UPDATE renewal SET date_notified = '$rundate' WHERE renewal_id=$rid"); $results[$rundate][$days][] = "$rdate($rid)"; } } Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514858 Share on other sites More sharing options...
ianhaney50 Posted June 25, 2015 Author Share Posted June 25, 2015 Hi Sorry for my late reply I have the following coding but am getting the error Fatal error: Call to a member function fetch_row() on a non-object in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 143 $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)); $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)); $current_visitor=0; $current_email = ''; $headers = "From: [email protected]\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); $db->query("UPDATE renewal SET date_notified=NULL"); // clear dates $results = array(); $dt1 = new DateTime('2015-06-12'); // date of first run $dp = new DatePeriod($dt1, new DateInterval('P1D'), 30); foreach ($dp as $d) { $rundate = $d->format('Y-m-d'); reminders($db, $rundate, 14, $results); reminders($db, $rundate, 7, $results); } echo '<pre>'; foreach ($results as $dt=>$darray) { echo $dt."\n"; foreach ($darray as $days=>$ids) { printf ("\t%2d\t%s\n", $days, join(', ', $ids)); } } echo '<pre>'; function reminders($db, $rundate, $days, &$results) { $sql = "SELECT v.visitor_id , renewal_id , renewal_date FROM visitors v INNER JOIN renewal USING (visitor_id) WHERE renewal_date BETWEEN '$rundate' AND '$rundate'+INTERVAL $days DAY AND IFNULL(date_notified, '1901-01-01') < '$rundate'-INTERVAL $days DAY ORDER BY renewal_id"; $res = $db->query($sql); while (list($vid, $rid, $rdate) = $res->fetch_row()) { $db->query("UPDATE renewal SET date_notified = '$rundate' WHERE renewal_id=$rid"); $results[$rundate][$days][] = "$rdate($rid)"; } } $db->query($sql) ; // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> UPDATE: I changed the visitor to visitors as didn't realise it had visitor as the table name near the end in the sql query line so changed it and the auto-email.php page is outputting the following Name: Ian HaneyTax expiry date: 26 June 2015Email Successfully Sent 2015-06-12 14 2015-06-26(105)2015-06-20 7 2015-06-26(105)2015-07-12 14 2015-07-26(104) Notice: Undefined variable: sql in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 150 Warning: mysqli::query(): Empty query in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 150 Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514886 Share on other sites More sharing options...
Barand Posted June 25, 2015 Share Posted June 25, 2015 The last code I gave you was a separate script to verify which renewal dates would be process when. It was not for inclusion in your other code. You should be able to combine the 14 and 7 day queries into a single query using a UNION. $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 UNION 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)); Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514908 Share on other sites More sharing options...
ianhaney50 Posted June 25, 2015 Author Share Posted June 25, 2015 Ahh ok, sorry I have put the coding back to how it was before I think, I am not getting any errors and getting the email so guessing is all ok so the following script $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 UNION 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)); Do I put that in in place of the current coding 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)); $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)); Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514915 Share on other sites More sharing options...
Barand Posted June 25, 2015 Share Posted June 25, 2015 Yes Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514917 Share on other sites More sharing options...
ianhaney50 Posted June 25, 2015 Author Share Posted June 25, 2015 Ok sorry, done that but getting the following error Unknown column 'v.visitor_id' in 'order clause' Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514918 Share on other sites More sharing options...
Barand Posted June 25, 2015 Share Posted June 25, 2015 Sorry, had to make a couple of other adjustments having combined the two queries into a UNION Try 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 , renewal_date FROM visitor 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 UNION 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 , renewal_date FROM visitor 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 visitor_id, renewal_date Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514920 Share on other sites More sharing options...
ianhaney50 Posted June 25, 2015 Author Share Posted June 25, 2015 Looks ok now, got a blank white page with no errors I'll reset the date_notified column so it sends a email and outputs the data on the php page, one sec Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514921 Share on other sites More sharing options...
ianhaney50 Posted June 25, 2015 Author Share Posted June 25, 2015 Works perfect the date I put in is 26 June 2015 is there a way to test it to see if it does send another email when the date is within 7 days? Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514922 Share on other sites More sharing options...
Barand Posted June 25, 2015 Share Posted June 25, 2015 That is precisely what that test script I sent you does. Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514930 Share on other sites More sharing options...
ianhaney50 Posted June 25, 2015 Author Share Posted June 25, 2015 Ahh sorry that other one that you said was separate so I just make that a php page upload it and test it? Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514936 Share on other sites More sharing options...
Barand Posted June 25, 2015 Share Posted June 25, 2015 Works perfect the date I put in is 26 June 2015 is there a way to test it to see if it does send another email when the date is within 7 days? Yes, a separate page. Though it is unlikely that 26 June will be re-sent as it already well inside the 7 day limit. Link to comment https://forums.phpfreaks.com/topic/296918-automatic-php-email/page/6/#findComment-1514938 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.