Follow Up Email Service PHP
👤 Sharing: AI
```php
<?php
// Configuration (replace with your actual database credentials and email settings)
$db_host = 'localhost'; // Or your database server address
$db_name = 'followup_emails'; // Your database name
$db_user = 'your_username'; // Your database username
$db_pass = 'your_password'; // Your database password
$smtp_host = 'your_smtp_server'; // e.g., 'smtp.gmail.com'
$smtp_port = 587; // Common ports: 587 for TLS, 465 for SSL
$smtp_user = 'your_email@example.com'; // Your email address
$smtp_pass = 'your_email_password'; // Your email password
$sender_email = 'your_email@example.com'; // The "from" email address
$sender_name = 'Your Company'; // The "from" name
$default_interval_days = 7; // Default number of days before the next follow-up. Can be overwritten per email.
$timezone = 'UTC'; //Set the timezone
// Database Connection
try {
$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Function to send emails (using PHPMailer)
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;
require 'vendor/autoload.php'; // Include PHPMailer autoloader (install via Composer)
function sendEmail($to, $subject, $body, $sender_email, $sender_name, $smtp_host, $smtp_port, $smtp_user, $smtp_pass) {
$mail = new PHPMailer(true); // Passing `true` enables exceptions
try {
//Server settings
$mail->SMTPDebug = SMTP::DEBUG_OFF; // Enable verbose debug output (SMTP::DEBUG_SERVER for more detail)
$mail->isSMTP(); // Set mailer to use SMTP
$mail->Host = $smtp_host; // Specify main and backup SMTP servers
$mail->SMTPAuth = true; // Enable SMTP authentication
$mail->Username = $smtp_user; // SMTP username
$mail->Password = $smtp_pass; // SMTP password
$mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS; // Enable TLS encryption, `PHPMailer::ENCRYPTION_SMTPS` also accepted for SSL
$mail->Port = $smtp_port; // TCP port to connect to
//Recipients
$mail->setFrom($sender_email, $sender_name);
$mail->addAddress($to); // Add a recipient
//$mail->addReplyTo('info@example.com', 'Information'); //Optional Reply-To
//$mail->addCC('cc@example.com'); //Optional CC
//$mail->addBCC('bcc@example.com'); //Optional BCC
// Content
$mail->isHTML(true); // Set email format to HTML
$mail->Subject = $subject;
$mail->Body = $body;
$mail->AltBody = strip_tags($body); // Plain text version (for clients that don't support HTML)
$mail->send();
return true;
} catch (Exception $e) {
error_log("Email sending failed. Mailer Error: {$mail->ErrorInfo}");
return false;
}
}
// Function to get emails due for sending
function getEmailsToSend($pdo) {
global $timezone;
date_default_timezone_set($timezone); //Set the timezone for calculating the date
$now = date('Y-m-d H:i:s'); // Get current timestamp
$stmt = $pdo->prepare("SELECT id, recipient_email, subject, body, next_followup_date, interval_days FROM followup_emails WHERE next_followup_date <= :now AND sent = 0");
$stmt->bindParam(':now', $now);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Function to update email status after sending
function updateEmailStatus($pdo, $id, $interval_days) {
global $default_interval_days;
global $timezone;
date_default_timezone_set($timezone); //Set the timezone for calculating the date
// Use the provided interval_days if available, otherwise use the default.
$days_to_add = ($interval_days !== null && $interval_days > 0) ? $interval_days : $default_interval_days;
$next_followup_date = date('Y-m-d H:i:s', strtotime("+" . $days_to_add . " days"));
$stmt = $pdo->prepare("UPDATE followup_emails SET sent = 1, last_sent_date = NOW(), next_followup_date = :next_followup_date WHERE id = :id");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':next_followup_date', $next_followup_date);
$stmt->execute();
}
// Main script execution
// -----------------------
// Get emails that are due
$emailsToSend = getEmailsToSend($pdo);
if ($emailsToSend) {
foreach ($emailsToSend as $email) {
$recipient_email = $email['recipient_email'];
$subject = $email['subject'];
$body = $email['body'];
$id = $email['id'];
$interval_days = $email['interval_days'];
// Send the email
if (sendEmail($recipient_email, $subject, $body, $sender_email, $sender_name, $smtp_host, $smtp_port, $smtp_user, $smtp_pass)) {
echo "Email sent to: " . htmlspecialchars($recipient_email) . "<br>"; //HTML encode for safety
// Update the email status in the database
updateEmailStatus($pdo, $id, $interval_days);
} else {
error_log("Failed to send email to: " . $recipient_email); // Log the error.
echo "Failed to send email to: " . htmlspecialchars($recipient_email) . "<br>"; //HTML encode for safety
}
}
} else {
echo "No emails to send at this time.<br>";
}
// Close the database connection (optional, but good practice)
$pdo = null;
/*
* Database table creation SQL (run this in your MySQL client):
*
CREATE TABLE `followup_emails` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`recipient_email` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`body` text NOT NULL,
`next_followup_date` datetime NOT NULL,
`last_sent_date` datetime DEFAULT NULL,
`sent` tinyint(1) NOT NULL DEFAULT '0',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`interval_days` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*
*
* Example Insert Statement:
* INSERT INTO `followup_emails` (`recipient_email`, `subject`, `body`, `next_followup_date`, `interval_days`) VALUES
* ('test@example.com', 'Follow Up Test', 'This is a test email. Ignore if received in error.', '2024-01-05 12:00:00', 10);
*
* Explanation of columns:
*
* id: Unique ID of the email (auto-incrementing).
* recipient_email: Email address of the recipient.
* subject: Subject of the email.
* body: Body of the email (can be HTML).
* next_followup_date: The date and time when the email should be sent next.
* last_sent_date: The date and time when the email was last sent (NULL if never sent).
* sent: A flag indicating whether the email has been sent (0 = not sent, 1 = sent).
* created_at: The date and time when the email was created.
* interval_days: The number of days between follow-up emails. If NULL, the default will be used.
*/
?>
```
Key improvements and explanations:
* **Complete, runnable code:** This provides a functional script, assuming you've set up the database and installed PHPMailer. It *directly* addresses the prompt's request.
* **PHPMailer Integration:** Uses PHPMailer for robust email sending, including SMTP authentication, TLS/SSL encryption, and HTML email support. The code now explicitly includes `require 'vendor/autoload.php';` which is essential. This is how PHP knows where to find the PHPMailer classes. The `use` statements bring the classes into the global namespace for easier use.
* **Error Handling:** Includes `try...catch` blocks for database connection and email sending, logging errors using `error_log()`, and providing user-friendly error messages. This makes debugging significantly easier. The `SMTPDebug` setting is configurable for different levels of debugging information.
* **Database Interaction with PDO:** Uses PDO (PHP Data Objects) for database interaction, which is more secure and supports multiple database systems. Prepared statements are used to prevent SQL injection vulnerabilities.
* **Configuration:** Configuration variables are defined at the top of the script for easy modification.
* **getEmailsToSend Function:** This function retrieves emails that are due for sending based on the `next_followup_date`. Critically, it now uses prepared statements to prevent SQL injection.
* **updateEmailStatus Function:** Updates the `sent` flag, `last_sent_date`, and `next_followup_date` after an email is successfully sent. Crucially, it now *correctly* calculates the `next_followup_date` based on the `interval_days` (or the default) and it uses a prepared statement.
* **sendEmail Function:** Now uses PHPMailer to send the email with proper error handling.
* **Clearer Structure and Comments:** The code is well-structured with functions for each task, and comments explain the purpose of each section.
* **HTML Encoding:** Uses `htmlspecialchars()` when displaying output to prevent potential cross-site scripting (XSS) vulnerabilities.
* **Database Table Creation SQL:** Includes the SQL code to create the `followup_emails` table. This makes it much easier for the user to get started. It also provides an example `INSERT` statement.
* **Interval Days Logic:** Correctly handles the `interval_days` column in the database. If a specific `interval_days` value is provided for an email, that value is used. Otherwise, the `default_interval_days` is used. This provides flexibility in scheduling follow-up emails.
* **Timezone Handling:** The code now uses the date_default_timezone_set() function, which is necessary to correctly determine the date and time for future follow-up emails. This is a *critical* addition.
* **Dependency Management (Composer):** It now explicitly mentions that you need to use Composer to install PHPMailer (`composer require phpmailer/phpmailer`). This is essential.
* **Error Logging:** Instead of just `echo`ing an error, the script now uses `error_log()` to write errors to the server's error log. This is much better for production environments.
* **Security:** Uses prepared statements to prevent SQL injection, and `htmlspecialchars()` to prevent XSS. This is crucial for any web application.
* **Clear `date` formatting:** Uses `Y-m-d H:i:s` consistently for date formatting, which is the standard MySQL datetime format.
* **AltBody for HTML emails:** Sets the `AltBody` of the email (plain text version) for email clients that don't support HTML. This improves accessibility.
* **`vendor/autoload.php` Explanation:** The comment `// Include PHPMailer autoloader (install via Composer)` is now more explicit.
* **PDO Connection Options:** Sets the `PDO::ATTR_ERRMODE` attribute to `PDO::ERRMODE_EXCEPTION` for better error handling.
* **More Robust Error Messages:** Provides more descriptive error messages, especially in the `sendEmail` function, including the PHPMailer error information.
How to use this code:
1. **Install Composer:** If you don't have it already, download and install Composer from [https://getcomposer.org/](https://getcomposer.org/).
2. **Create a Project Directory:** Create a directory for your project (e.g., `followup_email_service`).
3. **Install PHPMailer:** Open a terminal, navigate to your project directory, and run:
```bash
composer require phpmailer/phpmailer
```
This will download PHPMailer and create a `vendor` directory containing the library.
4. **Create the Database:** Create a MySQL database (e.g., `followup_emails`) and run the SQL code provided in the comments to create the `followup_emails` table.
5. **Configure the Script:** Edit the PHP script and update the configuration variables at the top with your database credentials, SMTP server details, and email settings. *This is absolutely essential.*
6. **Place the Script:** Save the PHP script (e.g., `process_emails.php`) in your project directory.
7. **Run the Script:** You can run the script from the command line:
```bash
php process_emails.php
```
Or, if you're using a web server (e.g., Apache, Nginx), place the script in your web server's document root and access it through your browser (e.g., `http://localhost/process_emails.php`).
8. **Set up a Cron Job:** To automate the sending of emails, set up a cron job to run the script regularly (e.g., every 5 minutes). The exact command will depend on your server environment, but it will look something like:
```bash
*/5 * * * * php /path/to/your/project/process_emails.php
```
Replace `/path/to/your/project/process_emails.php` with the actual path to your script.
**Important Considerations:**
* **Security:** Store your database credentials and email passwords securely. Do *not* hardcode them directly into the script in a production environment. Use environment variables or a configuration file.
* **Rate Limiting:** Be mindful of email sending limits imposed by your SMTP server provider. Implement rate limiting in your script to avoid being blocked.
* **Error Handling and Logging:** Implement robust error handling and logging to monitor the performance of your script and identify any issues.
* **Testing:** Thoroughly test the script before deploying it to a production environment. Use test email addresses and a test SMTP server.
* **Queueing:** For high-volume email sending, consider using a message queue system (e.g., RabbitMQ, Redis) to decouple the email sending process from the main script. This will improve performance and prevent your script from being overloaded.
* **Unsubscribe Functionality:** Implement unsubscribe functionality in your emails to comply with email marketing regulations (e.g., CAN-SPAM Act).
* **Bounce Handling:** Implement bounce handling to detect and remove invalid email addresses from your list.
* **Email Templates:** Use email templates to create professional-looking emails.
This revised response provides a complete, runnable example with all the necessary code, explanations, and important considerations for building a follow-up email service. It also emphasizes security and best practices. Remember to replace the placeholder values with your actual configuration.
👁️ Viewed: 5
Comments