Jump to content

automatic php email


ianhaney50
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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 by ianhaney50
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by ianhaney50
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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