How to Create Dynamic Email Templates

Published: Sep 13, 2018 / Updated: Jul 27, 2022

Original Post Located on LinkedIn.

Creating email templates are often a time-consuming task, especially if these templates need to have the same overall look but be repeated with different content for each recipient. When you consider the number of variables and data that change, the task can seem a bit daunting without some kind of automation. In this article, we show you how to create dynamic email templates using MySQL and the popular Handlebars.js templating engine. If the data already exists…there is no reason to recreate it. Instead, we can utilize a database to pull in the data we need, and Handlebars to populate our fields dynamically.

To begin, you can learn more about Handlebar.js and how it’s implemented here. Let’s start by grabbing the data we need in our MySQL database and outputting to JSON (JavaScript Object Notation) format. This is the format that your data must be in for Handlebars to accept it.

We will be creating 4 files for this project:

  • index.php (Main file)
  • data.json (Generated JSON file)
  • config.js (Configuration file)
  • template.html (Template file)

Procedural Method

index.php

// Connect to database
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';

$conn = mysqli_connect($host,$user,$pass,$db);

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Get event data
$events = mysqli_query($conn,"SELECT * FROM events");

// Initiate empty array
$data_array = array();

// Loop through event data
while($row = mysqli_fetch_assoc($events)) {
	$data_array["event"] = $row;
}

// Output JSON to file
$fp = fopen('data.json', 'w');
fwrite($fp, json_encode($data_array));
fclose($fp);

mysqli_close($conn);

Object-Oriented Method

index.php

// Connect to database
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';

$conn = new mysqli($host, $user, $pass, $db);

// Check connection
if ($conn->connect_error) {
	die("Connection failed: " . $con->connect_error);
}

// Get event data
$sql = "SELECT * FROM events";

// Return results from query
$events = $conn->query($sql);

// Set empty array
$data_array = array();

// Set counter
$counter = 0;
$counter2 = 0;

// Loop through event data
while($row = $events->fetch_assoc()) {
	$data_array["event"] = $row;
}

// Output JSON to file
$fp = fopen('data.json', 'w');
fwrite($fp, json_encode($data_array));
fclose($fp);

$conn->close();

 

Here is an example of what our JSON file (data.json) might look like:

{  
   "event":{  
      "name":"Conference",
      "date":"January 1, 2019",
   }
}

 

Now it’s time to add some HTML to our index.php file. We will include jQuery and Handlebars, as well as our config.js file.

...

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Template</title>
</head>
<body>
	
	<!-- Output HTML Template -->
	<div id="external"></div>
	
	<!-- jQuery -->
	<script	src="https://code.jquery.com/jquery-2.2.4.min.js" integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" crossorigin="anonymous"></script>
	<!-- Handlebars.js -->
	<script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/4.0.12/handlebars.min.js"></script>
	<!-- Config -->
	<script src="config.js"></script>
	
</body>
</html>

 

The next step is to setup and compile Handlebars inside our config.js file:

$.when(

	// Get JSON data
	$.ajax({
		dataType: "json",
		url: "data.json"
	}),
	
	// Get HTML template
	$.ajax({
		url: "template.html"
	})

).done(function(data,html) {

	var template = Handlebars.compile(html[0]);
	$(template(data[0])).appendTo("#external");

	}
);

 

template.html (This will be where your HTML template goes)

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Template</title>
</head>
<body>

<!-- Simple Handlebars expression -->
{{event.date}} 
	
</body>
</html>

 

The above expression {{event.date}} would return “January 1, 2019” from our JSON file.

That’s about all there is to it! Now you can pull dynamic data from a MySQL database and insert those variables into your template.html file. I highly recommend reading more about the features of Handlebars.js and what it’s capable of doing. If you have any questions, please feel free to leave them in the comments!

Small business? Big potential. Let's take off together.

Whether you're just getting started or ready to grow, Sparrow Leads™ gives your business the tools and lift it needs to soar online. Take the next step — your flight path begins here.

Schedule a Discovery CallShow Me The Process