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 for different purposes, such as promoting multiple events. When you consider the amount of variables and data that goes into each of these templates, the task can seem a bit daunting.

I recently starting using the popular Handlebars.js templating engine to help create email templates that will pull the data we already have from a MySQL database. I figure if the data already exists…there is no reason to recreate it.

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.