Jump to content

Send a reminder based on chosen date


I-AM-OBODO

Recommended Posts

Hi,
I have an application where during registration a user will choose when they want to be reminded of an event. For instance the event lets say will start on 2017-02-02 and i scheduled it on 2016-12-30 and i want to be reminded everyday from 10days to the event date(2017-02-02). How do i go about it? I need idea as to know how to go about it. I dont know if cron will be good.
Thanks

Link to comment
Share on other sites

^^As the colleague above suggested, use a cron job, but be extra careful when writing the code for it. Make sure you log and email every error because if something goes wrong you won't be able to tell until users start complaining. And make sure to set the server to run the cron during the night (remember that the time in the server might be different than your current timezone)

Edited by Stefany93
Link to comment
Share on other sites

  • 2 weeks later...

hello

this is what i ended up with. The problem now is that the cron wont send?

 

$stmt = $pdo->query("SELECT *, DATEDIFF(due_date, NOW()) AS days_diff FROM xbp_bills WHERE bill_status = '$bill_status' AND CURDATE() BETWEEN due_date - INTERVAL reminder DAY AND due_date ");
$stmt->execute();
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $due_date = $row['due_date'];
    $service_provider = ucwords($row['service_provider']);
    $service_type = ucwords($row['service_type']);
    $bill_status = $row['bill_status'];  
 
}
Link to comment
Share on other sites

 

hello

this is what i ended up with. The problem now is that the cron wont send?

$stmt = $pdo->query("SELECT *, DATEDIFF(due_date, NOW()) AS days_diff FROM xbp_bills WHERE bill_status = '$bill_status' AND CURDATE() BETWEEN due_date - INTERVAL reminder DAY AND due_date ");
$stmt->execute();
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $due_date = $row['due_date'];
    $service_provider = ucwords($row['service_provider']);
    $service_type = ucwords($row['service_type']);
    $bill_status = $row['bill_status'];  
 
}

 

Is this your final script? You want to notify the user via email, no? Because you are not sending an email here, or outputting anything.

 

 

Check in the phpMyAdmin SQL console your query to make sure it is correct. 

Link to comment
Share on other sites

Is this your final script? You want to notify the user via email, no? Because you are not sending an email here, or outputting anything.

 

 

Check in the phpMyAdmin SQL console your query to make sure it is correct. 

 

This is not the final script. My final script is:

 

 

$stmt = $pdo->query("SELECT *, DATEDIFF(due_date, NOW()) AS days_diff FROM xbp_bills WHERE bill_status = '$bill_status' AND CURDATE() BETWEEN due_date - INTERVAL reminder DAY AND due_date ");

$stmt->execute();

 

$mail = new PHPMailer;

//$mail->SMTPDebug = 3;

$mail->isSMTP(); 

$mail->Host = 'localhost';

$mail->SMTPAuth = true;

$mail->Username = 'username@user.com'; 

$mail->Password = 'password.'; 

$mail->SMTPSecure = 'ssl'; 

$mail->Port = 465; 

$mail->From = 'no_reply@noreply.com';

$mail->FromName = 'Coy Sender';

 

$mail->WordWrap = 587;  

$mail->AddEmbeddedImage("../img/logo.png", "my_logo");

$mail->isHTML(true);  

$mail->Subject = 'Notification';

 

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    $email= $row['email'];

 $due_date = $row['due_date'];

    $service_provider = ucwords($row['service_provider']);

    $service_type = ucwords($row['service_type']);

    $bill_status = $row['bill_status'];  

 

$message = "

This is a reminder

";

 

$mail->addAddress($email); 

$mail->Body    = $message;

$mail->send();

$mail->ClearAddresses();

 

}

Edited by Mr-Chidi
Link to comment
Share on other sites

Turn on php error checking (see my signature) and run this NOT as a cron job. Put some debugging lines in there to ensure that things are happening correctly and work on it THAT way until it works as desired. Then set it up as a cron job and test it by sending yourself the email still.

Link to comment
Share on other sites

Turn on php error checking (see my signature) and run this NOT as a cron job. Put some debugging lines in there to ensure that things are happening correctly and work on it THAT way until it works as desired. Then set it up as a cron job and test it by sending yourself the email still.

i have ran the script and it worked fine but wont run as a cron.

Link to comment
Share on other sites

This is the Exact scrpt

 

 

$stmt=$pdo->prepare("SELECT *, DATEDIFF(NOW(), due_date) AS days_diff FROM bills
LEFT JOIN login_details ON bills.user_id = login_details.user_id
WHERE xbp_bills.bill_status = 'OVERDUE'");
$stmt->execute();

//$message = htmlspecialchars($mess);
$mail = new PHPMailer;

//$mail->SMTPDebug = 3;                               // Enable verbose debug output

$mail->isSMTP();                                      // Set mailer to use SMTP
$mail->Host = 'localhost';  // Specify main and backup SMTP servers
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Username = 'mails@mail.com';                 // SMTP username
$mail->Password = 'password';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to
$mail->From = 'no_reply@noreply';
$mail->FromName = 'Bill Sender';

$mail->WordWrap = 587;                                 // Set word wrap to 50 characters
$mail->AddEmbeddedImage("../img/logo.png", "logo");
$mail->isHTML(true);                                  // Set email format to HTML
$mail->Subject = 'Notification on Bill Overdue for Payment';

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $user_email = $row['username'];
    $trans_ref = $row['trans_ref'];;
    $due_date = $row['due_date'];
    $days_diff = $row['days_diff'];
    $service_provider = ucwords($row['service_provider']);
    $service_type = ucwords($row['service_type']);
    $amount_paid = number_format($row['amount_paid'],2);
    $bill_status = $row['bill_status'];    

$message = "
<div class='messages'>
<h3><img src='cid:my_logo'> </h3>
<br>
<div style='font-size:15px;'>Email Notification on Bill Overdue for Payment </h4>
<p>Dear: $user_email</p>
<p>
The following bills are overdue for payment:
</p>
<table width='80%' border='0' cellspacing='0' cellpadding='0'>
  <tr style='font-weight:bold;'>
    <td>Service Provider</td>
    <td>Services</td>
    <td>Amount Due</td>
    <td>Due Date</td>
    <td>Overdue Days</td>
    <td> </td>
  </tr>
  <tr>
    <td>$service_provider</td>
    <td>$service_type</td>
    <td>$amount_paid</td>
    <td>$due_date</td>
    <td>$days_diff</td>
    <td>Click to Pay</td>
  </tr>
</table>

<h3>Yours<br>
Team Mate</h3>
";

$mail->addAddress($user_email);     // Add a recipient
$mail->Body    = $message;
$mail->send();
$mail->ClearAddresses();

}
Link to comment
Share on other sites

1 - While your message refers to multiple bills you do realize that you are sending ONE email for EACH record(aka 'bill') that you retrieve? Moving on...

 

2 - where do you turn on php error checking?

 

3 - where is the rest of the script? It is important to see it for a cron job examination.

 

4 - have you ever done a cron job before?

 

PS - is the column 'username' really an email address as you later imply?

Edited by ginerjm
Link to comment
Share on other sites

1 - While your message refers to multiple bills you do realize that you are sending ONE email for EACH record(aka 'bill') that you retrieve? Moving on...

       (the intention though is to send bills to user, bills are sent based on the numbers of bills each user has and not just one)

 

2 - where do you turn on php error checking?

      (my error checking is where i have my db credentials)

 

3 - where is the rest of the script? It is important to see it for a cron job examination.

       (there's no other script. or maybe i don't get what you are trying to say)

 

4 - have you ever done a cron job before?

     (working cron? nope.)

 

PS - is the column 'username' really an email address as you later imply?

        (yes its the username)

Link to comment
Share on other sites

#1

you say moving on. So your current script is going to send multiple emails to a customer if he/she has multiple bills.

 

#2 It would be nice to see the error checking code

 

#3 & #4 Since you have not made a cron job yet, I wanted to see the whole script to be sure it is correct. If you don't want to show us that as I asked, then I guess we're done here.

 

PS - if as you say that the username column is a name then how can you use it as an email address?

Link to comment
Share on other sites

#1

you say moving on. So your current script is going to send multiple emails to a customer if he/she has multiple bills.

 

#2 It would be nice to see the error checking code

 

#3 & #4 Since you have not made a cron job yet, I wanted to see the whole script to be sure it is correct. If you don't want to show us that as I asked, then I guess we're done here.

 

PS - if as you say that the username column is a name then how can you use it as an email address?

 

1. yes. the script is suppose to send all the bills a user has

 

2. this is where i have my error reporting

 

3. this is all the code there is or is it incomplete?

 

4. i said the username is the email and not name

 

 

 
error_reporting(0);
$database='dbname';
$user='username';
$password='password.';
$dsn="mysql:host=localhost;dbname=$database";

try {
$pdo = new PDO($dsn,$user,$password);
} catch(PDOException $e) {
die ('Failed to connect');

}

 

thanks

Link to comment
Share on other sites

 

1 - I agree that it will send the all the bills. I was just pointing out that you will send multiple emails instead of just one.

 

2 - your error reporting is turned off. See my signature for how to do it correctly.

 

4 - from your post #15:

PS - is the column 'username' really an email address as you later imply?

 

(yes its the username)

Sounds to me like you are saying that "username" is a NAME and not an EMAIL

 

And lastly - can you not post the entire proposed cron job from start tag to finish so that it can be examined?

Do you know what the proper hashbang line is for you installation?

Link to comment
Share on other sites

#1

you say moving on. So your current script is going to send multiple emails to a customer if he/she has multiple bills.

 

#2 It would be nice to see the error checking code

 

#3 & #4 Since you have not made a cron job yet, I wanted to see the whole script to be sure it is correct. If you don't want to show us that as I asked, then I guess we're done here.

 

PS - if as you say that the username column is a name then how can you use it as an email address?

 

I really do not understand why you dont understand me. I said the script is to send just a mail with the bills due in a table. All the bills should be tabulated.

I tried the script manually but its sending multiple mails i.e if a customer has 4 bills, it sends 4 different mail but that is not what i want, all i want is a single mail to be sent to the customer detailing the bill details in a table. If you can help, please do help and lets get it over with. Below is my whole script:

 
$bill_status = "OVERDUE";

$st = $pdo->prepare("SELECT *, DATEDIFF(NOW(), due_date) AS days_diff FROM bills
LEFT JOIN login_details ON bills.user_id = login_details.user_id
WHERE bills.bill_status = '$bill_status'");
$st->execute();
$row_sent = $st->rowCount();


//$message = htmlspecialchars($mess);
$mail = new PHPMailer;

//$mail->SMTPDebug = 3;                               // Enable verbose debug output

$mail->isSMTP();                                      // Set mailer to use SMTP
$mail->Host = 'localhost';  // Specify main and backup SMTP servers
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Username = 'mails@mail.com';                 // SMTP username
$mail->Password = 'pasword';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to
$mail->From = 'no_reply@noreply.com';
$mail->FromName = 'Coy Name';

$mail->WordWrap = 587;                                 // Set word wrap to 50 characters
$mail->AddEmbeddedImage("../img/logo.png", "logo");
$mail->isHTML(true);                                  // Set email format to HTML
$mail->Subject = 'Notice';


while($rows = $st->fetch(PDO::FETCH_ASSOC)){
    $username = $rows['username'];
    
$message= "
<div class='messages'>
<h3><img src='cid:my_logo'> </h3>
<br>
<div style='font-size:15px;'>Email Notification on Bill Due for Payment </h4>
<p>Dear: $username</p>
<p>
The following bills are due for payment:
</p>
<table width='80%' border='0' cellspacing='0' cellpadding='0'>
  <tr style='font-weight:bold;'>
    <td>Trans Ref</td>
                <td>Due Date</td>
                <td>Days Overdue</td>
                <td>Service Provider</td>
                <td>Service Type</td>
                <td>Amount Paid</td>
                <td>Bill Status</td>
                <td>Recurring</td>
  </tr>

<h3>Yours<br>
Coy Name</h3>
";    

$message .= "        <tr>";
$message .= "            <td>".$rows['trans_ref']."</td>";
$message .= "            <td>".$rows['due_date']."</td>";
$message .= "            <td>".$rows['days_diff']."</td>";
$message .= "            <td>".$rows['service_provider']."</td>";
$message .= "            <td>".$rows['service_type']."</td>";
$message .= "            <td>".$rows['amount_paid']."</td>";
$message .= "            <td>".$rows['bill_status']."</td>";
$message .= "            <td>".$rows['recurring']."</td>";
$message .= "        </tr>";

$mail->addAddress($username);     // Add a recipient
$mail->Body    = $message;
$mail->send();
$mail->ClearAddresses();

}
 
Edited by Mr-Chidi
Link to comment
Share on other sites

I give up. At least we agree that your current logic sends out 1 email per bill so you have to alter your method. As for the above being your "whole script", I disagree and won't ask again.

 

And I still don't understand why you use username as both the salutation in your body

 

(Dear $username)

 

and as the email address:

 

$mail->addAddress($username); // Add a recipient

 

Doesn't make sense.

 

Good luck.

Link to comment
Share on other sites

I give up. At least we agree that your current logic sends out 1 email per bill so you have to alter your method. As for the above being your "whole script", I disagree and won't ask again.

 

And I still don't understand why you use username as both the salutation in your body

 

(Dear $username)

 

and as the email address:

 

$mail->addAddress($username); // Add a recipient

 

Doesn't make sense.

 

Good luck.

The dear username shouldnt be of any worries to you and that isnt my problem either. my problem is getting the script to send just one mail containing all the bills. just so you know: it is dear username which translates to: dear myemailaddress.com

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • 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.