We have been looking for an easy way to programmatically send email notifications to customers and came up with small php application to do just that. As it turned out we never used it but hey since I made it might as well share it.
I know, there are lots of services and applications out there that do just that, but where’s the fun in that right. Besides our requirements were a bit restrictive, we needed to automatically send emails based on a table’s entries in our database (populated by other software that handle workflows). Now in our case we use a Sybase Database, but given the fact that we have lots of different databases for all sorts of stuff in our organisation we though it would be best to implement a PHP + PDO connect everywhere application, you know just in case we wanted to use it again somewhere. Besides PDO, we used mustache as Template Engine and swift as Email Engine.
Description
Sends notifications depending on a database table. The input data must have the following format:
'id' => NUMBER, 'from' => 'EMAIL@ADDRESS.COM', 'to' => 'EMAIL@ADDRESS.COM', 'subject' => 'STRING', 'notification_method' => 'email', (NOTE, for now only "email" notification method is supported) 'template' => 'NAME OF TEMPLARE FILE WITHOUT THE .mustache EXTENSION', (NOTE: ALL TEMPLATES MUST EXIST UNDER "templates" FOLDER) 'data_json'=> 'JSON STING WITH DATA SPECIFIC TO THE TEMPLATE'
This program will send all the emails that are derived from the “selectquery” in the config.php which checks records with status = 0. It then runs the “updatequerystatus1” which updates the status to 1. Then it sends the notification using a mustache template. It then updates the status of the send email to 2 (which means notification was send). If an error occurs the status of the record is set to 3
The program will not populate the database table, it will only read, send notification and mark as send.
It can connect to either Oracle, MySQL, Microsoft SQL Server, Sybase using PDO.
Template engine is mustache ( http://mustache.github.io/ ).
Email engine is swift ( http://swiftmailer.org/ )
Note Example of data_json:
'[{"word":"watachikita"}, {"word":"triskitsikitsikita"}, {"word":"anginara"}]'
Note Example template:
<!DOCTYPE html> <head> </head> <body> <h4> Data </h4> {{#.}} <p> {{word}} </p> {{/.}} <hr> <span>Powered by Notificator.<br></span> </body> </html>
Prerequisites
Requires at least PHP 5.2.x to run, but works great in 5.3, 5.4 and 5.5, too.
Installation
- Install PHP 5.2.x or later
- Make sure the desired PDO extensions are enabled on php.ini, for example for mysql:
extension=php_pdo_mysql.dll
- Make sure the desired PDO extensions are enabled on php.ini, for example for mysql:
- Copy the notificator folder on your machine
Basic Usage
- The notificator table on the desired database can be populated by a 3rd party application or service. In order to send the notfication the new records must be marked with
status=0
. - Run the php from the command line. For example:
C:\php\php.exe -f C:\code\notificator\notify.php
Config File
The config file defines the email server and the database server. The program can connect to any type of database as described above.
$notify_options = array( "mailserveraddress" => "smtpmail.example.com", // smtp server location "mailserverport" => "25", // smtp server port "connection" => array ( "server" => "10.10.10.10", // Location "port" => "5000", // Port "user" => "user", // Database user "pass" => "password", // Password "name" => "dbname", // Database name "provider" => "SYBASE" // available providers are mysql,sqlsrv,SYBASE,oci ), "convertencoding" => true, // Convert encoding for subject and json_data? "in_charset" => "ISO-8859-7", // Input character set (same as database) "out_charset" => "UTF-8", // Output character set "table" => "notificator", "selectquery" => "select id, notif_from, notif_to, subject, notification_method, template, data_json from notificator where status = 1", // Do not change unless you know what you are doing "updatequerystatus1" => "update notificator set status=1, send_date=getDate() where status=0", // Do not change unless you know what you are doing "updatequerystatus2" => "update notificator set status=2, send_date=getDate() where status=1", // Do not change unless you know what you are doing "updatequerystatus3" => "update notificator set status=3, send_date=getDate() where status=1", // Do not change unless you know what you are doing "updatequerystatusid" => "update notificator set status=2, send_date=getDate() where id=:id" // Do not change unless you know what you are doing );
Database Table used
create table notificator ( id int identity , notif_from varchar(1000) not null , notif_to varchar(1000) not null , subject varchar(1000) not null , notification_method varchar(100) not null , template varchar(100) not null , data_json varchar(3000) null , create_date datetime not null , send_date datetime null , status tinyint DEFAULT 0 not null )