php email reminder script

ianhaney · October 18, 2016

Hi

I am using the php email reminder script that I had help with when was doing the script and have got the email sending out after bit of tweaking to suit this new project but the date notified column is not updating to the current date and time, below is the coding I have so far

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?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 
        u.id
        , domain_name_owner
        , url
        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date
        , domain_owner_email
        FROM websites u
        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
        ";
		
$query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db));

//fetch the data from the database 

$current_visitor=0;
$current_email = '';
$headers = "From: noreply@domain.co.uk\r\n";    
$subject = "Domain Name 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['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['id'];
		$current_email = $row['domain_owner_email'];
		$message = "Domain Name Owner: {$row['domain_name_owner']} \n\n";
		$renewals = array();
	}
	$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiry_date']}\n";

}
// 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>";
	}
	
	$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id = " . $row['id'];
	
}


// update successful notifications
$id = $notifications;

$db->query($sql) ;

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);
?>

</body>
</html>

I do need to the script to email out when a domain name is due to expire in 7 days

Sorry have tried to solve it on my own

cyberRobot · October 18, 2016

Have you checked if the query is throwing errors? Note that you are doing that with the first query:

mysqli_select_db($db,"") or die(mysqli_error($db));

Try doing the same here:

$db->query($sql);

ianhaney · October 18, 2016

I put the line of code

mysqli_select_db($db,"") or die(mysqli_error($db));

under the line

$db->query($sql);

and no errors are being returned

cyberRobot · October 18, 2016

Actually, you need to do something like this:

$db->query($sql) or die(mysqli_error($db));

At least I think that works with the object form of mysqli_query().

Sorry for the confusion.

ianhaney · October 19, 2016

Thank you for replying

I changed the coding to the following and it is producing the following error

//$db->query($sql);
$db->query($sql) or die(mysqli_error($db));

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

the query I have is below

$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id = " . $row['id'];

ianhaney · October 19, 2016

I have been playing around with the coding and now got the following code

$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id = '" . $row['id'] . "'";
	print $sql;

the error has gone and on the page, it is outputting the following

UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id = ''

it's just the where clause that needs sorting so it only updates the rows that the email gets pinged out for and not all the rows/records

at the mo it is not updating the date_notified_of_domain_expiry column with the current date and time

ianhaney · October 19, 2016

I have been having a go bit more with the coding and have now the following and it is updating the date_notified_of_domain_Expiry column now but is doing it for all records and not just the one row that the email has been sent out for, below is my updated coding

$sqlCommand = "SELECT 
        u.id
        , domain_name_owner
        , url
        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date
        , domain_owner_email
        FROM websites u
        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
		";

$id = 'id';
	
	$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id = $id";
	print $sql;

I just can't get it to update only the one row for the date is within 7 days, it should be only updating row 4 but instead is updating all rows 1-4

do I need another where clause in after $id so would be AND second where clause

Edited October 19, 2016 by ianhaney

ianhaney · October 19, 2016

Hi, I have a update, I have been playing around with the script and have now managed to get it to send the email and update just the records that have dates that are going to expire in 7 days but it don't seem to be looping through to check all dates and include multiple dates in the email, can someone check my coding please as my email only includes one date where as it should include two dates

my code is below



<?php


$db = mysqli_connect("localhost" , "", "") 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 

        u.id

        , domain_name_owner

        , url

        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date

        , domain_owner_email

        FROM websites u

        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY

        ";


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


$current_visitor=0;

$current_email = '';

$headers = "From: noreply@domain.co.uk\r\n";    

$subject = "Domain Name Expiry Date(s)";

$message = '';


//fetch the data from the database 

while ($row = mysqli_fetch_array($query)) {


$arr_ids[] = $row['id'];


// has visitor_id changed

if ($row['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>";

} else { 

   echo "<b>Error in Sending of Email to $to</b><br><br>";

}

}

$current_visitor = $row['id'];

$current_email = $row['domain_owner_email'];

$message = "Domain Name Owner: {$row['domain_name_owner']} \n\n";


}

$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiry_date']}\n";


}

// 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>";

} else { 

echo "<b>Error in Sending of Email to $to</b><br><br>";

}


if (isset($arr_ids)){

$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id IN (";

$sql .= implode("," , $arr_ids);

$sql .= ");";

print $sql;


}


//$db->query($sql);

$db->query($sql) or die(mysqli_error($db));


// Free the results  

mysqli_free_result($query);


//close the connection

mysqli_close($db);

}

?>

Sign In

php email reminder script

Recommended Posts

ianhaney

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information