Jump to content

php email reminder script

ianhaney

By ianhaney
in PHP Coding Help

 Share

Recommended Posts

ianhaney Advanced Member

ianhaney

Posted
Posted

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

Link to comment
Share on other sites
ianhaney Advanced Member

ianhaney

Posted
  • Author
Posted

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'];
Link to comment
Share on other sites
ianhaney Advanced Member

ianhaney

Posted
  • Author
Posted

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

Link to comment
Share on other sites
ianhaney Advanced Member

ianhaney

Posted
  • Author
Posted (edited)

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

ianhaney

Posted
  • Author
Posted
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);

}

?>

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.

×
 Share
Go to topic listing
×
×
  • 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.