Jump to content

automatic php email


ianhaney50

Recommended Posts

  • Replies 157
  • Created
  • Last Reply

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

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

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.

Link to comment
Share on other sites

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

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

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

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

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

$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 Haney

Tax expiry date: 26 June 2015
Email 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
Share on other sites

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

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

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

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

Archived

This topic is now archived and is closed to further replies.


×
×
  • 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.