Add SQL Insert for Email Templates

[coolcat-creations]

Can you please insert an example SQL Code for Email Templates?

This is the code I catched so far in a Live Presentation

INSERT INTO #__mail_templates (template_id, extension, "body", attachments, params)
VALUES
('system_template_1', 'system_plugin', 'SYSTEM TEMPLATE 1 MAIL BODY',
'{"tags": ["name", "email", "location", "messagehtml", "url", "contactname", "activate", "cancel"]}'),

('system_template_2', 'system_plugin', 'SYSTEM TEMPLATE 2 MAIL BODY',
'{"tags": ["sitename", "name", "email", "url", "contactname"]}'),

('system_template_3', 'system_plugin', 'SYSTEM TEMPLATE 3 MAIL BODY',
'{"tags": ["location", "url", "message", "bodyhtml", "contact",]}'),

('system_template_4', 'system_plugin', 'SYSTEM TEMPLATE 4 MAIL BODY',
'{"tags": ["email", "url", "contactname"]}'),

('system_template_5', 'system_plugin', 'SYSTEM TEMPLATE 5 MAIL BODY',
'{"tags": ["sitename", "contactname"]}'),

('system_template_6', 'system_plugin', 'SYSTEM TEMPLATE 6 MAIL BODY',
'{"tags": [ "sitename", "name", "email", "url", "contact"]}');

I hope the SQL is correct because I don't know how it should look like and how it works. Maybe it would be useful to know what the name of the file should be to have it automatically triggered and how to use the named tags.

[GURUPRASADSHARMA]

INSERT INTO email_templates (template_name, subject, body, created_at)
VALUES ('Welcome Email', 'Welcome to Our Service!',
'Dear [Customer Name],\n\nThank you for joining us!',
NOW());
hope it will help

[coolcat-creations]

INSERT INTO email_templates (template_name, subject, body, created_at)
VALUES ('Welcome Email', 'Welcome to Our Service!',
'Dear [Customer Name],\n\nThank you for joining us!',
NOW());
hope it will help

Can you explain?

[Hackwar]

If you want to properly insert a mail template upon installation of an extension, you would call \Joomla\CMS\Mail\MailTemplate::creatTemplate() in your installation script.

For the columns:

• template_id: Unique string to identify the template. Can basically be anything, but it would be best if it is a "speaking" name, like com_contact.contact.email_to_admin or something like that.
• extension: Internal name of the extension. This is used to filter by extension in the backend.
• language: For the initial template this has to be *
• subject: Either a translation string or the actual subject the mail should have. May contain tags for replacement.
• body: Plaintext body of the mail, including the placeholder tags. Can be a language string for the initial template.
• htmlbody: HTML body of the mail, including the placeholder tags. Can be a language string for the initial template.
• attachments: JSON encoded list of attachments to add to each mail send by this template
• params: JSON encoded object with the attribute tags, which contains an array of strings to use as tag replacements. The list of tags saved here will be available in the editor to later be replaced with content.

[coolcat-creations]

Hi @Hackwar, that's great thank you very much, so inserting the SQL is actually not really correct, right?
I will try to write a documentation from what you wrote this week.
thanks a lot!

[HLeithner]

Thanks Elisa, please create the pr when you have time.

[coolcat-creations]

@HLeithner I just wanted to add a PR but I have absolutely no Idea how.
Here is the text:

Email Templates in Joomla

This document explains how to insert email templates into Joomla, either by using direct SQL or via the MailTemplate API in your extension's installation script.

---

Table Schema (#__mail_templates)

Joomla stores its mail templates in the #__mail_templates table. Below is the schema that you need to populate:

Column	Type	Description
template_id	VARCHAR(255)	Unique identifier for the template (e.g. com_contact.contact.email_to_admin)
extension	VARCHAR(100)	The extension name (used to filter templates in the backend, e.g. com_contact)
language	CHAR(3)	Language code for the template (must be * for initial/default templates)
subject	TEXT	The mail subject (can be a translation string key or literal text; may include placeholder tags)
body	TEXT	Plain-text body of the mail (may include placeholder tags)
htmlbody	TEXT	HTML-formatted body of the mail (optional; may include placeholder tags)
attachments	TEXT	JSON-encoded array of file paths or URLs to attach, e.g. []
params	TEXT	JSON-encoded object with a tags array; lists the placeholder tags available in the editor

---

Method 1: SQL Insertion

Place an SQL file in your extension’s SQL folder so Joomla will execute it on installation. For example:

administrator/components/com_example/sql/install.mysql.5.5.sql

Inside that file, insert your templates like so:

INSERT INTO `#__mail_templates`
  (template_id, extension, language, subject, body, htmlbody, attachments, params)
VALUES
  ('com_example.user.welcome',
   'com_example',
   '*',
   'COM_EXAMPLE_WELCOME_SUBJECT',
   'Hello {name},\n\nWelcome to our service!',
   '<p>Hello {name},</p><p>Welcome to our service!</p>',
   '[]',
   '{"tags":["name"]}'),
  ('com_example.user.reset',
   'com_example',
   '*',
   'COM_EXAMPLE_RESET_SUBJECT',
   'Hi {name},\n\nUse this link to reset your password: {resetLink}',
   '<p>Hi {name},</p><p>Use this link to reset your password: <a href="{resetLink}">{resetLink}</a></p>',
   '[]',
   '{"tags":["name","resetLink"]}');

You can add as many INSERT rows as needed. Make sure you:

• Use the proper #__ prefix to allow Joomla to replace it with your configured table prefix.

• Escape any special characters (e.g., newlines as \n, quotes as \").

---

Method 2: Installation Script (PHP)

Alternatively, call the MailTemplate API in your extension’s install script. Create or update installscript.php in your component root:

<?php
// administrator/components/com_example/installscript.php
defined('_JEXEC') or die;
use Joomla\CMS\Mail\MailTemplate;
class Com_ExampleInstallerScript

{

public function install($parent)

{

// Create a "Welcome" template

MailTemplate::createTemplate(

'com_example.user.welcome',      // template_id

'com_example',                   // extension

'*',                             // language

'COM_EXAMPLE_WELCOME_SUBJECT',   // subject (language key or literal)

"Hello {name},\n\nWelcome to our service!",  // body

"<p>Hello {name},</p><p>Welcome to our service!</p>", // htmlbody

[],                              // attachments

['tags' => ['name']]            // params (tags)

);
    // Create a "Reset Password" template
    MailTemplate::createTemplate(
        'com_example.user.reset',
        'com_example',
        '*',
        'COM_EXAMPLE_RESET_SUBJECT',
        "Hi {name},\n\nReset your password: {resetLink}",
        "&lt;p&gt;Hi {name},&lt;/p&gt;&lt;p&gt;Reset your password: &lt;a href=\"{resetLink}\"&gt;{resetLink}&lt;/a&gt;&lt;/p&gt;",
        [],
        ['tags' =&gt; ['name','resetLink']]
    );
}

}

This script runs automatically on installation if referenced in your component manifest (<scriptfile>installscript.php</scriptfile>).

---

Placeholder Tags

• Defined in the params column as JSON: {"tags":["name","email","resetLink"]}.

• Use the tags in your subject, body, or htmlbody by wrapping them in curly braces, e.g. {name}.

• During mail sending, Joomla replaces these placeholders with real values.

---

File Naming and Triggering

1. SQL file: install.mysql.5.5.sql under sql/mysql/ in your component.

2. Install script: installscript.php, referenced in your manifest.

Joomla automatically executes the SQL on install and invokes the install() method in your script class.