Jump to content
ianhaney

php email reminder script

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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'];

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

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.