coatse Posted April 13, 2022 Share Posted April 13, 2022 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/ Share on other sites More sharing options...
gw1500se Posted April 13, 2022 Share Posted April 13, 2022 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. Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/#findComment-1595296 Share on other sites More sharing options...
kicken Posted April 13, 2022 Share Posted April 13, 2022 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/#findComment-1595298 Share on other sites More sharing options...
coatse Posted April 14, 2022 Author Share Posted April 14, 2022 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 Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/#findComment-1595327 Share on other sites More sharing options...
ginerjm Posted April 14, 2022 Share Posted April 14, 2022 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. 1 Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/#findComment-1595328 Share on other sites More sharing options...
kicken Posted April 14, 2022 Share Posted April 14, 2022 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? 1 Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/#findComment-1595329 Share on other sites More sharing options...
gizmola Posted April 20, 2022 Share Posted April 20, 2022 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) Quote Link to comment https://forums.phpfreaks.com/topic/314696-sending-an-email-when-expiry-date-is-in-1-month/#findComment-1595541 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.