automatic php email

Barand · 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.

ianhaney50 · 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

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

ianhaney50 · 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

Barand · June 24, 2015

http://php.net/manual/en/mysqli.query.php

ianhaney50 · 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 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.

ianhaney50 · 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?

Barand · 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.

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

Barand · 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

ianhaney50 · 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?

Barand · June 24, 2015

Forget the last post of mine - full of errors. Working on a revised version.

ianhaney50 · June 24, 2015

Ok no worries, thank you so much really appreciate it

Barand · 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)";
	}
		
}

ianhaney50 · 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: 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

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

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

Barand · June 25, 2015

Yes

ianhaney50 · June 25, 2015

Ok sorry, done that but getting the following error

Unknown column 'v.visitor_id' in 'order clause'

Barand · 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

ianhaney50 · 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

ianhaney50 · 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?

Barand · June 25, 2015

That is precisely what that test script I sent you does.

ianhaney50 · 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?

Barand · 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.

Sign In

automatic php email

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information