Sending an email when expiry date is in 1 month

[coatse]

Hi All,

I hope you are all well.

 

I have been building a license manager for my business using PHP and MySQL. Its all going well but I've hit a snag. I know how to send an email and tested it using PHP Mailer. But What I am struggling in doing is fetching the data from the table and putting it into the the body of the email. Please see my code below. This may not be the best way to do this but I'm still learning

 

<?php
    // Import PHPMailer classes into the global namespace
    // These must be at the top of your script, not inside a function
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\Exception;
    //Load Composer's autoloader
    require 'scripts/vendor/autoload.php';
    require 'database.php';
    $results=mysqli_query($conn,"SELECT * FROM customersubscriptions WHERE SubRenew =30");
    ?>
    <?php
    if (mysqli_num_rows($result) > 0) {
    ?>
    <?php
    $i=0;
    while($row = mysqli_fetch_array($result)) {
    ?>
    <?php
    $mail = new PHPMailer(true);
    try {
        //Server settings
        $mail->SMTPDebug = 2;
        $mail->isSMTP();
        $mail->Host = 'smtp.office365.com';
        $mail->Port = 587;
        $mail->SMTPSecure = 'TLS';
        $mail->SMTPAuth = true;
        $mail->Username = 'james@jhe.co.uk';                 // SMTP username
        $mail->Password = 'Coatez1989#';                        // SMTP password (NB Dummy password)


        //Recipients
        $mail->setFrom('reports@jhe.co.uk', 'JHE Reports');
        $mail->addAddress('james@jhe.co.uk', 'James Coates');     // Add a recipient


        //Content
        $mail->isHTML(true);                       // Set email format to HTML
        $mail->Subject = 'Contact Us Message';
        $mail->Body    = "The new password is " . $row['SubRenew'] . ".";
        $mail->AltBody = 'This is the body in plain text for non-HTML mail clients';

        $mail->send();
        header('Location: tempage.php');
    } catch (Exception $e) {
        echo 'Message could not be sent. Mailer Error: ', $mail->ErrorInfo;
    }
}
?>

 

Many Thanks

 

Edited April 13, 2022 by Barand

[gw1500se]

First of all you have way too many unnecessary <?PHP and ?>. You have retrieved your data from the database but never use that result anywhere. All you need to do is create a string formatted with the data so it says what you want and put it in the body. Am I missing something?

P.S. Don't use '*' in your select query. Specify only those columns you will actually use.

[kicken]

As mentioned, you have a lot of unnecessary PHP tags.   You're also missing a closing brace for your if statement and have a typo in your variable name ($results vs $result).

You also don't need to recreate the mailer object on every loop iteration as you go through the query results.  You can move it's creation and iteration further up the script then just send emails in the loop.

<?php
// Import PHPMailer classes into the global namespace
// These must be at the top of your script, not inside a function
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

//Load Composer's autoloader
require 'vendor/autoload.php';
require 'database.php';

$mail = new PHPMailer(true);
$mail->SMTPDebug = 2;
$mail->isSMTP();
$mail->Host = 'smtp.example.com';
$mail->Port = 587;
$mail->SMTPSecure = 'TLS';
$mail->SMTPAuth = true;
$mail->Username = 'username';                 // SMTP username
$mail->Password = '***';                       // SMTP password (NB Dummy password)


//From
$mail->setFrom('username@example.com', 'User');
$mail->addAddress('username@example.com', 'User');     // Add a recipient

$result=mysqli_query($conn,"SELECT * FROM customersubscriptions WHERE SubRenew =30");
if (mysqli_num_rows($result) > 0) {
	$i=0;
	while($row = mysqli_fetch_array($result)) {
		try {
			//Content
			$mail->isHTML(true);                       // Set email format to HTML
			$mail->Subject = 'Contact Us Message';
			$mail->Body    = "The new password is " . $row['SubRenew'] . ".";
			$mail->AltBody = 'This is the body in plain text for non-HTML mail clients';

			$mail->send();
		} catch (Exception $e) {
			echo 'Message could not be sent. Mailer Error: ', $mail->ErrorInfo;
		}
	}
}
header('Location: tempage.php');

If you need to send mail to an email address that you pull from your database, move the call to addAddress into the loop and add a call to clearAddresses.

Edited April 13, 2022 by kicken

[coatse]

Hi Kicken,

I hope your well.

Thanks for you reply. I have re writen the code with clearAddresses() command I am no longer getting any errors but all I get is blank screen.

 

<?php
error_reporting(E_ALL); ini_set('display_errors', 1);
/**
 * This example shows how to send a message to a whole list of recipients efficiently.
 */

//Import the PHPMailer class into the global namespace
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
use PHPMailer\PHPMailer\SMTP;
error_reporting(E_STRICT | E_ALL);

date_default_timezone_set('Etc/UTC');

require 'scripts/vendor/autoload.php';

//Passing `true` enables PHPMailer exceptions
$mail = new PHPMailer(true);
$mail->SMTPDebug = 3;

$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; //SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = 587;
$mail->Username = 'james@jhe.co.uk';
$mail->Password = '*****';
$mail->setFrom('reports@jhe.co.uk', 'JHE Reports');

$mail->Subject = 'Expiring Subscription for Customer';


//Connect to the database and select the recipients from your mailing list that have not yet been sent to
//You'll need to alter this to match your database
$mysql = mysqli_connect('localhost', 'root', '****');
mysqli_select_db($mysql, 'license_manager');
$result = mysqli_query($mysql, 'SELECT * FROM customersubscriptions WHERE SubRenew = 30');

foreach ($result as $row) {
        $mail->addAddress('james@jhe.co.uk', 'James Coates');
				$mail->Body("This subscription is expiring on" . $row['subRenew']);
				$mail->send();
				if (!$mail->Send()) {
		    echo 'Something went wrong';
		} else {
		    echo 'Complete.';
		}
    //Clear all addresses and attachments for the next iteration
    $mail->clearAddresses();
    $mail->clearAttachments();
}

Any Ideas?

 

Many Thanks

Coatse

[ginerjm]

Look at the cleaned up block of your code loop;

foreach ($result as $row) 
{
	$mail->addAddress('james@jhe.co.uk', 'James Coates');
	$mail->Body("This subscription is expiring on" . $row['subRenew']);
	$mail->send();		// DROP THIS LINE!!!
	if (!$mail->Send())
		echo 'Something went wrong';
	else
		echo 'Mail sent to somebody Complete.';
	//Clear all addresses and attachments for the next iteration
	$mail->clearAddresses();
	$mail->clearAttachments();
}

As you can see you were doing the send (?) twice although function names are different.

Secondly - where are you using the results of your query loop? I see a static address but nothing from your loop.

[kicken]

Are you sure you're getting results from your query?

Your query/code doesn't make a whole lot of sense anyway.  Your querying for records where SubRenew = 30, and the only data you're using as a result of that query is the SubRenew column which is guaranteed to be 30 at that point so why bother?

 

[gizmola]

Some good advice and questions from ginerjm and kicken.

What is the structure of your customersubscriptions table?

As pointed out, the SubRenew column can not simultaneously be an integer (30) AND a subscription end date.  

I would expect that you would have a DATE, DATETIME or TIMESTAMP column, and use some sort of aging calculation in your query.  

So for example, let's assume that SubRenew is a MySQL Date column.  A query like this would find rows where the renewal is 30 days from today:

SELECT * FROM customersubscriptions WHERE SubRenew = DATE_ADD(CURDATE(), INTERVAL 30 DAY)