johram pong johram pong - 27 days ago 12
MySQL Question

how to send auto email to members who did not activate account

ok i got registration on my site which has account activation by email and i notice not all members activate their account so i need to send an email automatically to them if account is not activated within 7 days from the time of registration

i wish to run this code once daily but if it was sent to one user then next time it should not send email to the same user again to avoid email spam

here what i wrote so far but i got no idea how to achieve the rest

$query = "SELECT * FROM members WHERE active !='Yes' AND (joined > DATE_SUB(NOW(), INTERVAL 7 DAY))";
$stmt->execute();
$result = $stmt->fetch();


foreach ($result as $row) {

$id = $row['member_id'];
$to = $row['email'];
$activation = $row['active'];

$subject = "Account Activation";
$body = "<p><img src='".DIR."images/logo.png' alt='logo'></p> <p>Hello,</p><p>Thank you for registering at ".SITEURL.".</p>
<p>It seems you have still not activated you account, to activate your account, please click on this link: <a href='".DIR."activate.php?x=$id&y=$activation'>CLICK HERE</a>. If you do not activate your account within 7 days your account will automatically get deleted. </p>
<p>Regards,<br/>".SITEURL."<br/><a href='tel:".SITEMOBILE."'>".SITEMOBILE."</a></p>
<p align='center'><small><font color='red'>This is an automated message, please do not reply to this.</font></small></p>";

$mail = new Mail();
$mail->setFrom(REGEMAIL);
$mail->addAddress($to);
$mail->subject($subject);
$mail->body($body);
$mail->send();

}


your time and help is much appreciated

Answer

Your above code is OK. It is working well. You need to create a new column in the "members" table like "sent_activation_email" then when cronjob running this field should update.

So your retrieving query amend like this :

$query = "SELECT * FROM members WHERE  active !='Yes' AND (joined > DATE_SUB(NOW(), INTERVAL 7 DAY)) AND sent_activation_email != 0";

After your $mail->send() function write an update query.

.......

$mail->subject($subject);
$mail->body($body);
$mail->send();

$sql = mysql_query("UPDATE `members` SET `sent_activation_email` = '1' WHERE `id` = ".$row['member_id'].") ";
Comments