Email tracking is an important task for web developer. You can keep the records of the emails which are opened by the receivers. How you can send email using PHPMailer and keep track of your sending emails are shown in this tutorial. But if you are working in the local server then you have to convert it to work like online server or you can use any free or paid hosting server to test the code shown in this tutorial.
Steps:
1. Create a database named emailbd and a table named strong>track_email with the following SQL.
CREATE TABLE `track_email` (
`ID` int(11) NOT NULL,
`receiver` varchar(50) NOT NULL,
`subject` varchar(100) NOT NULL,
`body` text NOT NULL,
`emaildate` timestamp NOT NULL DEFAULT current_timestamp(),
`opendate` datetime DEFAULT NULL,
`tcode` varchar(50) NOT NULL,
`status` varchar(20) NOT NULL DEFAULT ‘Not Open’
) ENGINE=InnoDB;
2. Create a folder named emailtracker in your server where you will store your necessary files.
3. Create db.php file to make the database connection. Set the proper hostname, username, password and database name based on your database server.
<?php
$db = new mysqli("localhost", "username", "password","database");
?>4. Download the following files to send email.
class.phpmailer.php
class.smtp.php
5. You have to select a gmail account that you will use for sending email and
enable less secure app access of your account.
6. Create index.php file with the following code to send email and display the current status of each email.
<?php require 'db.php'; //make database connection?>
<!DOCTYPE html>
<html>
<head>
<title>How to Track Email Open or not using PHP</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="https:///maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script src="https:///cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous">
<style type="text/css">
.bloc_left_price {
color: #c01508;
text-align: center;
font-weight: bold;
font-size: 150%;
}
.category_block li:hover {
background-color: #007bff;
}
.category_block li:hover a {
color: #ffffff;
}
.category_block li a {
color: #343a40;
}
.add_to_cart_block .price {
color: #c01508;
text-align: center;
font-weight: bold;
font-size: 200%;
margin-bottom: 0;
}
.add_to_cart_block .price_discounted {
color: #343a40;
text-align: center;
text-decoration: line-through;
font-size: 140%;
}
.product_rassurance {
padding: 10px;
margin-top: 15px;
background: #ffffff;
border: 1px solid #6c757d;
color: #6c757d;
}
.product_rassurance .list-inline {
margin-bottom: 0;
text-transform: uppercase;
text-align: center;
}
.product_rassurance .list-inline li:hover {
color: #343a40;
}
.reviews_product .fa-star {
color: gold;
}
.pagination {
margin-top: 20px;
}
footer {
background: #343a40;
padding: 40px;
}
footer a {
color: #f8f9fa!important
}
</style>
</head>
<body>
<div class="container">
<br/><center><h3>Tracking Email based on Sending Date</h3></center><br/>
<div class="row">
<div class="col">
<div class="card">
<div class="card-header bg-primary text-white"><i class="fa fa-envelope"></i> EMAIL COMPOSER
</div>
<div class="card-body">
<form method="post">
<div class="form-group">
<label for="name">Email Address</label>
<input type="email" class="form-control" id="email" name="email" aria-describedby="emailHelp" placeholder="Enter Email" required>
</div>
<div class="form-group">
<label for="email">Subject</label>
<input type="text" class="form-control" id="subject" name="subject" aria-describedby="emailHelp" placeholder="Enter Subject" required>
</div>
<div class="form-group">
<label for="message">Message</label>
<textarea class="form-control" id="message" name="message" rows="6" required></textarea>
</div>
<div class="mx-auto">
<button type="submit" name='send' class="btn btn-primary text-right">Send Email</button></div>
</form>
</div>
</div>
<?php
if(isset($_POST["send"]))
{
require 'class.phpmailer.php';
$sender = 'emailtester480@gmail.com';
$receiver= $_POST['email'];
$subject = $_POST['subject'];
$message = $_POST['message'];
$tcode = md5(rand());
$site_url = 'https://fahmidasclassroom.com/email_tracker/';
$mail = new PHPMailer;
$mail->isHTML(true);
$mail->AltBody = "This message is generated by plain text !";
$mail->IsSMTP();
$mail->SMTPSecure = 'ssl';
$mail->Host = 'smtp.gmail.com';
$mail->SMTPAuth = true;
$mail->Port = 465;
$mail->Username = 'Your gmail email address';
$mail->Password = 'Your gmail email password';
$mail->setFrom('admin@fahmidasclassroom.com', 'fahmidasclassroom');
$mail->addReplyTo('admin@fahmidasclassroom.com', 'fahmidasclassroom');
$mail->addAddress($receiver);
$mail->Subject = $subject;
$email_message = $message;
$email_message .= '<img src="'.$site_url.'tracker.php?tcode='.$tcode.'" width="1" height="1" />';
$mail->Body = $email_message;
if($mail->Send())
{
$query= "insert into track_email(receiver,subject,body, track_code) values('".$receiver."','".$subject."','".$message."', '".$tcode."')";
$db->query($query);
echo '<div class="alert alert-success"><strong> Email has been sent.</strong></div>';
}
else
echo '<div class="alert alert-danger"><strong> Email has not sent.</strong></div>';
}
?>
</div>
<div class="col-12 col-sm-7">
<div class="card bg-light mb-3">
<div class="card-header bg-success text-white text-uppercase"><i class="fa fa-envelope-open"></i> Track Email</div>
<div class="card-body">
<form method="post">
<div class="form-group">
<label for="name">Search Date:</label>
<input type="date" id="startdt" name="startdt" aria-describedby="emailHelp" placeholder="Start Date" required>
To: <input type="date" id="enddt" name="enddt" aria-describedby="emailHelp" placeholder="End Date" required>
<button type="submit" name="search" value="search" class="btn btn-success text-right">Search</button>
</div>
</form>
<?php
if(isset($_POST["search"]))
{
$start_date = date('Y-m-d', strtotime($_POST["startdt"]));
$end_date = date('Y-m-d', strtotime($_POST["enddt"]));
$result= $db->query("Select * from track_email where emaildate between '".$start_date."' and '".$end_date."'");
}
else
$result= $db->query("Select * from track_email order by emaildate desc limit 0,5 ");
if($result->num_rows)
{
?>
<table class="table table-striped">
<thead>
<tr>
<th>Email</th>
<th>Subject</th>
<th>Open Date</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php
while($row=$result->fetch_object())
{
?>
<tr>
<td><?php echo $row->receiver; ?></td>
<td><?php echo $row->subject; ?></td>
<td><?php if ($row->status == 'Open') echo date('d-m-Y H:i:s', strtotime($row->opendate)); ?></td>
<td><?php echo $row->status; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<?php
}
else
echo '<label class="text-primary">No email send yet.</label>';
?>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
7. Create tracker.php file with the following code to update the database when when an email is opened by the receiver.
<?php
require 'db.php';
if(isset($_GET["tcode"]))
{
$query = "
UPDATE track_email
SET status = 'Open', opendate = '".date("Y-m-d H:i:s", STRTOTIME(date('h:i:sa')))."'
WHERE track_code = '".$_GET["tcode"]."' AND status = 'Not Open'";
$db->query($query);
}
?>