Skip to content

Instantly share code, notes, and snippets.

@medelling24

medelling24/mailpoet.php

Created September 26, 2016 21:01
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save medelling24/9c6e93587d2172821aed7ffcf4d59528 to your computer and use it in GitHub Desktop.
Save medelling24/9c6e93587d2172821aed7ffcf4d59528 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
mailpoet.php
## problem 1
<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('mycorp');
$id = $_GET['id'];
$query = "SELECT subject, body FROM newsletters WHERE id=". $id;
$response = mysql_query($query);
$newsletter = mysql_fetch_assoc($response);
// I would like to send a json instead echo
$newsletter_json = array(
'subject' => $newsletter['subject'],
'body' => $newsletter['body'],
'url' => $_SERVER['PHP_SELF']. '?id='. $id
);
echo json_encode($newsletter_json);
?>
## problem 3
json structure
Size:
1 : 1 column
2 : 2 columns
3 : 3 columns
Types:
0 text
1 image
Value:
Text or the image url
{
"blocks": [
{
"size" : 2,
"type" : 1,
"value": "image url"
},
{
"size" : 2,
"type" : 1,
"value": "image url"
},
{
"size" : 1,
"type" : 0,
"value": "text text text"
},
{
"size" : 3,
"type" : 0,
"value": "txt"
},
{
"size" : 3,
"type" : 0,
"value": "txt"
},
{
"size" : 3,
"type" : 0,
"value": "txt"
}
]
}
Complete code and tests
https://jsfiddle.net/ayfvehxx/3/
Core
//HTML
<body>
<div class="row">
<div id="newsletter">
</div>
</div>
//Javascript
var data = getJson(); //Get the json
var div = document.getElementById('newsletter');
blocks = data.blocks;
console.log(blocks);
blocks.forEach(function(block){
var size;
var content;
if(block.size == 1)
size = 'col-md-12 col-sm-12 col-xs-12 col-lg-12';
else if(block.size == 2)
size = 'col-md-6 col-sm-6 col-xs-6 col-lg-6';
else if(block.size == 3)
size = 'col-md-4 col-sm-4 col-xs-4 col-lg-4';
if(block.type == 0)
content = '<p>'+block.value+'</p>';
else
content = '<img src="'+block.value+'" height="50"/>'; //should have a class with the height
var newsletter = '<div class="col '+size+'">'+content+'</div>';
div.innerHTML = div.innerHTML + newsletter;
});
//in this case get the json from a variable
function getJson(){
var data = '{ "blocks": [ { "size" : 2, "type" : 1, "value": "http://www.mailpoet.com/wp-content/uploads/2016/04/mailpoet-new-logo-2016.png" }, { "size" : 2, "type" : 1, "value": "http://www.mailpoet.com/wp-content/uploads/2016/04/mailpoet-new-logo-2016.png" }, { "size" : 1, "type" : 0, "value": "text text text" }, { "size" : 3, "type" : 0, "value": "txt" }, { "size" : 3, "type" : 0, "value": "txt" }, { "size" : 3, "type" : 0, "value": "txt" } ] }';
var json = JSON.parse(data);
return json;
}
## problem 2
//DB
Subscriber
id - bigint
name - varchar(255)
email - varchar(100)
List
id - bigint
name - varchar(255)
SubscriberListRelation
id - bigint
subscriber_id - bigint FK
list_id - bigint FK
NewsLetter
id - bigint
subject - varchar(255)
body - text
scheduled - datetime / can be null
creation_date - datetime
NewsLetterListRelation
id - bigint
list_id - bigint FK
newsletter_id - bigint FK
Notification
id - bigint
name - varchar(255)
trigger - varchar(255)
NotificationListRelation
id - bigint
notification_id - bigint FK
list_id - bigint FK
<?php
$host='localhost';
$dbname = 'newsletter';
$user = 'root';
$pass = '';
$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
/*
* Create a new subscriber based on the email
* @params
* name: subscriber name
* email: subscriber email
*
* @returns
* json with status and if there is an error
*
* */
function postSubscriber($name, $email){
if(!userExists($email)) {
createUser($name,$email);
echo json_encode(array("error"=>false,"status"=>"Created"));
}
else{
echo json_encode(array("error"=>true,"status"=>"User already exists"));
}
}
/*
* Create a new list based on the name
*
* @params
* name: list name
*
* @returns
* json with status and if there is an error
*
* */
function postList($name){
if(!listExists($name)) {
createList($name);
echo json_encode(array("error"=>false,"status"=>"Created"));
}
else{
echo json_encode(array("error"=>true,"status"=>"List already exists"));
}
}
/*
* Create list - subscriber relation
*
* @params
* subscriber_id: subscriber id
* list_id: list id
*
* @returns
* json with status and if there is an error
*
* */
function postListSubscriberRelation($subscriber_id, $list_id){
if(!listSubscriberRelationExists($subscriber_id, $list_id)) {
createListSubscriberRelation($subscriber_id, $list_id);
sendWelcomeEmail($subscriber_id, $list_id);
echo json_encode(array("error"=>false,"status"=>"Created"));
}
else{
echo json_encode(array("error"=>true,"status"=>"Relation already exists"));
}
}
/*
* Create newsletter and send it
*
* @params
* subject: newsletter subject
* body: newsletter body
* lists: array of list id
* schedule: optional, datetime to send the newsletter in the future
*
* @returns
* json with status and if there is an error
*
* */
function postNewsletter($subject, $body, $lists, $schedule = null){
$newsletter_id = createNewsletter($subject, $body, $schedule);
if($schedule!=null) {
foreach ($lists as $list) {
$subscribers = getSubscribers($list);
foreach ($subscribers as $subscriber) {
$subscriber = getSubscriber($subscriber);
sendNewsLetter($newsletter_id, $subscriber);
}
}
}
echo json_encode(array("error"=>false,"status"=>"Sent"));
}
/*
* exists functions
* determine if a object exists in the DB or not
*
* */
function userExists($email){
$query = "SELECT COUNT(id) FROM subscribers WHERE email = " . $email;
if ($response = $db->query($query)) {
if ($response->fetchColumn() > 0) {
return true;
}
else {
return false;
}
}
}
function listExists($name){
$query = "SELECT COUNT(id) FROM lists WHERE name = " . $name;
if ($response = $db->query($query)) {
if ($response->fetchColumn() > 0) {
return true;
}
else {
return false;
}
}
}
function listSubscriberRelationExists($subscriber_id, $list_id){
$query = "SELECT COUNT(id) FROM subscriber_list_relation WHERE subscriber_id = " . $subscriber_id . " AND list_id = " . $list_id;
if ($response = $db->query($query)) {
if ($response->fetchColumn() > 0) {
return true;
}
else {
return false;
}
}
}
/*
*
* Insert functions
*
* */
function createUser($name,$email){
$query = "INSERT INTO subscribers(name, email) VALUES ($name, $email)";
$stmt = $db->prepare($query);
$stmt->execute();
}
function createList($name){
$query = "INSERT INTO lists(name) VALUES ($name)";
$stmt = $db->prepare($query);
$stmt->execute();
}
function createNewsletter($subject, $body, $schedule){
$query = "INSERT INTO newsletter(subject, body, schedule) VALUES ($subject, $body, $schedule)";
$stmt = $db->prepare($query);
$stmt->execute();
$id = $db->lastInsertId();
return $id;
}
function createListSubscriberRelation($subscriber_id, $list_id){
$query = "INSERT INTO subscriber_list_relation(subscriber_id, list_id) VALUES (".$subscriber_id.", ".$list_id.")";
$stmt = $db->prepare($query);
$stmt->execute();
}
/*
* Getters
*
* */
function getSubscribers($list){
$query = "SELECT s.id, s.name, s.email FROM subscribers s INNER JOIN subscriber_list_relation slr ON slr.list_id = ".$list;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetchAll();
}
function getSubscriber($id){
$query = "SELECT id, name, email FROM subscribers WHERE id = " . $id;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetch();
}
function getList($id){
$query = "SELECT id, name FROM lists WHERE id = " . $id;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetch();
}
function getNewsLetter($id){
$query = "SELECT id, subject, body FROM newsletter WHERE id = " . $id;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetch();
}
function getSubscriberFromEmail($email){
$query = "SELECT id, name, email FROM subscribers WHERE email = " . $email ;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetch()['id'];
}
function getListFromName($name){
$query = "SELECT id, name FROM lists WHERE name = ". $name;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetch()['id'];
}
/*
*
* Sent email functions
*
* */
function sendWelcomeEmail($subscriber_id, $list_id){
$subscriber = getSubscriber($subscriber_id);
$list = getList($list_id);
echo "Recipient: " . $subscriber["name"] ." < ". $subscriber["email"] . ">".
"Subject: Welcome to " . $list["name"] . " newsletter" .
"Good morning and have a nice day
- Jill";
}
function sendNewsLetter($newsletter_id, $subscriber){
$newsletter = getNewsLetter($newsletter_id);
echo "Recipient: " . $subscriber["name"] ." < ". $subscriber["email"] . ">".
"Subject: " . $newsletter["subject"].
$newsletter["body"];
}
//test file
class NewsletterTest extends TestCase
{
public function testPostSubscriber(){
postSubscriber("test@test.com", "test");
$this->assertTrue(userExists("test@test.com"));
}
public function testPostList(){
postList("test");
$this->assertTrue(listExists("test"));
}
function testPostListSubscriberRelation(){
postSubscriber("test@test.com", "test");
postList("test");
$subscriber_id = getSubscriberFromEmail("test@test.com");
$list_id = getListFromName("test");
createListSubscriberRelation($subscriber_id, $list_id);
$this->assertTrue(listSubscriberRelationExists($subscriber_id, $list_id));
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment