update date column automatically issue

**ianhaney** · 10-18-2016, 03:17 PM

Hi

I have designed a email script that pings out a email when a date is within 7 days and then it updates the date_notified column in the database but just noticed it is updating all the records with the current date and time but only need it to update the records that has a email sent for the date that is within 7 days, hope that makes sense

below is the coding I put together

Code:

<?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 14 DAY
            AND IFNULL(date_notified_of_domain_expiry, '1901-01-01') < CURDATE()-INTERVAL 14 DAY

        UNION

        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
            AND IFNULL(date_notified_of_domain_expiry, '1901-01-01') < CURDATE()-INTERVAL 7 DAY
        ";
		
		

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

//fetch tha data from the database 

$current_visitor=0;
$current_email = '';
$headers = "From: email@example.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>";
	}
}


// update successful notifications
$id = $notifications;
$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id=$id"; ---->line 116

$db->query($sql) ;

// Free the results  
mysqli_free_result($query);

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

</body>
</html>

with the code as it is, I get the following error

Notice: Array to string conversion in domain-expiry-auto-email.php on line 116

I know I need to adjust the WHERE code but not sure what to, sorry

**ianhaney** · 10-18-2016, 06:31 PM

Hi

Just a update, after doing more tweaking the email is being sent but the date notified column is not updating for the record that the email has been sent out for, can any one help me please as can't work out my sql query near the bottom and why it is not updating, below is my coding

Code:

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

$current_visitor=0;
$current_email = '';
$headers = "From: noreply@example.co.uk\r\n";    
$subject = "Domain Name Expiry Date(s)";
$message = '';

//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>";
			} 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>";
	} 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'];
	
}

$db->query($sql) ;

// Free the results  
mysqli_free_result($query);

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

</body>
</html>

Thank you in advance

**ianhaney** · 10-19-2016, 04:54 PM

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

Code:

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

**DyDr** · 10-20-2016, 12:20 PM

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

If your goal is to build one email message with the information from multiple possible rows for one Owner, there are two ways to do this.

1) Using the method in the current code, where you are detecting a change in a value in the result set, you would need to order the rows by the owner's name and then detect when the owner's name changes, not when the id changes. The id is the row's id and it changes for each row.

2) Pre-process/pivot the data around the owner's name, creating an array of arrays of rows, using the owner's name as the main array index, then loop over this pre-processed data to get the owner's name and an array of row(s) that correspond to that owner, then loop over the array of row(s) to build the message body. This results in the simplest code over the first method. There's no duplication of code and a minimum of variables.

From the point where you are executing the sql query statement, this is all the code you need -

PHP Code:


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



$data = array(); // array to hold the pre-processed data

$arr_ids = array(); // you should always define variables that you are going to conditionally populate - an array of the row id's found by the query



// pivot the data around the owner's name

while ($row = mysqli_fetch_assoc($query))

{

    $arr_ids[] = $row['id']; // you should only update the records when the email is successfully sent. coding left to you as a programming exercise

    

    $data[$row['domain_name_owner']][] = $row;

}



mysqli_free_result($query); // this is done for SELECT queries, to free up the memory used by the result set. doing this at the end of the code is pointless since php is going to free up all the memory at that point anyway





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

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



// loop over the data

foreach($data as $owner=>$arr)

{

    // do any one-time (heading) processing here...

    $message = "Domain Name Owner: {$owner} \n\n";

    $to = $arr[0]['domain_owner_email']; // get email from first row

    

    // loop over the array of row(s)

    foreach($arr as $row)

    {

        // use the data from each row here...

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

    }



    // send the email here...

    if(mail($to, $subject, $message, $headers))

    {

        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(!empty($arr_ids))

{

    $sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id IN (".implode("," , $arr_ids).")";

    echo $sql;



    // be consistent in the style of statement being used

    mysqli_query($db, $sql) or die(mysqli_error($db));

}