Send a reminder based on chosen date

[I-AM-OBODO]

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

[Barand]

Use a cron job to run daily. Query your event table for those events

SELECT ... WHERE CURDATE() BETWEEN eventdate - INTERVAL 10 DAY AND eventdate

[Stefany93]

^^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 December 31, 2016 by Stefany93

[I-AM-OBODO]

 

Use a cron job to run daily. Query your event table for those events

SELECT ... WHERE CURDATE() BETWEEN eventdate - INTERVAL 10 DAY AND eventdate

... INTERVAL 10 DAY AND eventdate? Is it complete?

[Barand]

As a condition on the date it is complete.
RTFM - BETWEEN
 
I don't know your application so there may be other conditions in the WHERE clause.

[I-AM-OBODO]

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

[ginerjm]

Turn on error checking and see what your query statement triggers. What is 'reminder'?

 

PS You are developing this as a funning script before setting it up as a cron job, aren't you?

[Stefany93]

 

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. 

[I-AM-OBODO]

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 January 22, 2017 by Mr-Chidi

[ginerjm]

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-AM-OBODO]

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.

[ginerjm]

Show us the EXACT script you have setup as the cron job. You can hide any passwords.

And PLEASE remember to wrap your code in the appropriate forum tags.

[I-AM-OBODO]

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

}

[ginerjm]

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 January 26, 2017 by ginerjm

[I-AM-OBODO]

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)

[ginerjm]

#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-AM-OBODO]

#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

[ginerjm]

 

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?

[I-AM-OBODO]

#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 January 30, 2017 by Mr-Chidi

[ginerjm]

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.

[I-AM-OBODO]

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

[Barand]

Locking - OP has opened separate thread.