automatic php email

ianhaney50 · June 23, 2015

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

ianhaney50 · June 23, 2015

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

Edited June 23, 2015 by ianhaney50

ianhaney50 · June 23, 2015

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

Barand · June 23, 2015

There are several lines per visitor, each date on a separate row in the results.

You have to loop through the rows to process the dates but only output and send the email when the visitor id changes.

Note I added the renewal_id to the query for subsequent updating of the notification dates (in a single update query at the end)

$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 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
ORDER BY v.visitor_id, renewal_date;";

$query = mysqli_query($conn, $sqlCommand) or die (mysqli_error($conn)); 

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

$conn->query($sql) ;

ianhaney50 · June 23, 2015

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

Barand · June 23, 2015

Your email count needs to be in the main loop. At the moment it is only counting the final email message.

ianhaney50 · June 23, 2015

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

Barand · June 23, 2015

No - not right.

The $count=1 needs to be before the loop otherwise it gets set to 1 for every record.
You should only process the count on change of visitor, when email sent. You are currently counting every record and there could be several records per email.

ianhaney50 · June 23, 2015

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?

Barand · June 23, 2015

Is the mail() call working OK? Are you getting the success or error messages output?

Barand · June 23, 2015

Is the mail() call working OK? Are you getting the success or error messages output?

ianhaney50 · June 23, 2015

Hi, sorry for my late reply

No not getting no output message saying success or failed

the php page itself is blank

Barand · June 23, 2015

You may not be seeing anything because the date_notified values have been updated so no records are now selected by the query.

Try resetting the renewals table

UPDATE renewal SET date_notified = NULL;

and check if no records found

$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 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
ORDER BY v.visitor_id, renewal_date;";

$query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); 

if ($query->num_rows==0 ) {                 //   ADD
	echo "No reminders due";            //   THESE
}                                           //   LINES

ianhaney50 · June 23, 2015

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?

Barand · June 23, 2015

phpmyadmin of course.

ianhaney50 · June 23, 2015

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

ianhaney50 · June 23, 2015

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

Edited June 23, 2015 by ianhaney50

ianhaney50 · June 23, 2015

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?

ianhaney50 · June 23, 2015

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

Barand · June 23, 2015

You get those undefined errors because those fields were not selected in the query and therefore not in the results

Try this

$sql = "SELECT 
v.visitor_id
, visitor_name
, visitor_email
, visitor_firstline
, visitor_secondline
, visitor_town
, visitor_county
, visitor_postcode
, visitor_tel
, visitor_mobile
, visitor_model
, visitor_plate
, DATE_FORMAT(MAX(IF(item_id=2,renewal_date,0)), '%e %M %Y') as motdue
, DATE_FORMAT(MAX(IF(item_id=3,renewal_date,0)), '%e %M %Y') as taxdue
, DATE_FORMAT(MAX(IF(item_id=1,renewal_date,0)), '%e %M %Y') as insdue
, DATE_FORMAT(MAX(date_notified), '%e %M %Y') as date_notified
FROM visitor v
    INNER JOIN renewal USING (visitor_id)
GROUP BY v.visitor_id";

$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>";
			echo "<tbody>";
			//fetch the data from the database 
while ($row = mysqli_fetch_array($query)) {
		
            
			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>" . $row['taxdue'] . "</td>";
				echo "<td>" . $row['motdue'] . "</td>";
				echo "<td>" . $row['insdue'] . "</td>";
				echo "<td>" . $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);
}

ianhaney50 · June 23, 2015

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

Edited June 23, 2015 by ianhaney50

Barand · June 23, 2015

There were times when thought this was going to be a job for life :-)

ianhaney50 · June 23, 2015

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

ianhaney50 · June 24, 2015

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

ianhaney50 · June 24, 2015

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?

Sign In

automatic php email

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Barand

Posted Images

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

Join the conversation

Important Information