Friday, July 28, 2017

Removing Duplicate Emails From Different Lists in Sendy



Sendy is a PHP self hosted web application that allows you to manage email lists and send emails through Amazon SES and other services.

One of the missing features in Sendy however, is the ability to remove duplicates from different lists. For example if the same email is subscribed to listX and listY, it's not possible to automatically remove that email.

The below script is an addition to the Sendy installation, and was successfully tested on Sendy v2.1.1.3.


Install/upload the script:


1- Create a new PHP file called remove-duplicates-diff.php
2- Copy/Paste the below script to your file and save the file or get it from github: https://github.com/etiennerached/sendy-remove-duplicates

<?php /* Developed By Etienne Rached */?>

<?php include('includes/header.php');?>         

<?php include('includes/login/auth.php');?>     

<?php include('includes/create/main.php');?>    

<?php include('includes/helpers/short.php');?>

<?php include('includes/create/timezone.php');?>

<?php include('js/create/main.php');?>          



<?php                                   

if(isset($_POST['mainList']) && is_numeric($_POST['mainList']) && isset($_POST['compareList']) && is_numeric($_POST['compareList']))

{                               

        $mainList = mysqli_real_escape_string($mysqli, $_POST['mainList']);

        $compareList = mysqli_real_escape_string($mysqli, $_POST['compareList']);



        if($compareList == 0)

        {       

                $q = 'SELECT id,email FROM `subscribers` WHERE email IN (SELECT email FROM subscribers WHERE list<>' . $mainList . ' AND userID = ' . get_app_info('main_userID') .')  AND list='.$mainList. ' AND userID = ' . get_app_info('main_userID');

        }                       

        else                            

        {                               

                $q = 'SELECT id,email FROM `subscribers` WHERE email IN (SELECT email FROM subscribers WHERE list<>' . $mainList . ' AND list=' . $compareList .' AND userID = ' . get_app_info('main_userID') .')  AND list='.$mainList.' AND userID = ' . get_app_info('main_userID');

        }                                       

                                                

        $r = mysqli_query($mysqli, $q);                 

        if ($r)                                         

        {                                               

                $count = 0;                             

                while($row = mysqli_fetch_array($r))    

                {

                        //Storing for future use

                        $id = $row['id'];

                        $email = $row['email'];

                        

                        //Delete from DB

                        $query = "DELETE FROM subscribers WHERE id=" . $id . ' AND userID = ' . get_app_info('main_userID');

                        mysqli_query($mysqli, $query);

                        $count++;

                }

                echo '<h2 style="color:red">' . $count . ' ' . _('emails has been found & removed') . '</h2>';

        }

        else

        {

                echo '<h2 style="color:red">' . _('No duplicates were found') . '</h2>';

        }

}



?>


<link rel="stylesheet" type="text/css" href="css/datepicker.css" />

<div class="span2">

        <?php include('includes/sidebar.php');?>

</div>


<form action="<?php echo get_app_info('path')?>/remove-duplicates-diff" method="POST" accept-charset="utf-8" class="form-vertical" id="remove-duplicates-diff">

        <div class="control-group">

                <label class="control-label"><?php echo _('Remove Duplicates From');?></label>

                <div class="controls">



                        <select id="mainList" name="mainList">

                        <?php

                                $q = 'SELECT * FROM lists WHERE  userID = '.get_app_info('main_userID').' ORDER BY name ASC';

                                $r = mysqli_query($mysqli, $q);

                                if ($r && mysqli_num_rows($r) > 0)

                                {

                                        while($row = mysqli_fetch_array($r))

                                        {

                                                $list_id = stripslashes($row['id']);

                                                $list_name = stripslashes($row['name']);

                                                $list_selected = '';





                                                echo '<option value="'.$list_id.'" data-quantity="'.get_list_quantity($list_id).'" id="'.$list_id.'" '.$list_selected.'>'.$list_name.'</option>';

                                        }

                                }

                                ?>

                        </select>

                </div>



                <label class="control-label"><?php echo _('Compare with');?></label>

                <div class="controls">

                        <select id="compareList" name="compareList">

                                <option value="0">All</option>

                                <?php

                                        $q = 'SELECT * FROM lists WHERE  userID = '.get_app_info('main_userID').' ORDER BY name ASC';

                                        $r = mysqli_query($mysqli, $q);

                                        if ($r && mysqli_num_rows($r) > 0)

                                        {

                                                while($row = mysqli_fetch_array($r))

                                                {

                                                        $list_id = stripslashes($row['id']);

                                                        $list_name = stripslashes($row['name']);

                                                        $list_selected = '';



                                                        echo '<option value="'.$list_id.'" data-quantity="'.get_list_quantity($list_id).'" id="'.$list_id.'" '.$list_selected.'>'.$list_name.'</option>';

                                                }

                                        }

                                        ?>

                        </select>

                </div>

                <button type="submit" class="btn" id="remove-duplicates-diff-btn"><?php echo _('Remove Duplicates');?></button>

        </div>

</form>

<?php include('includes/footer.php');?>



3- Upload the file to your Sendy root directory
4- Login to Sendy and access the file by going to your url/remove-duplicates-diff, (example: mySendyWebsite.com/remove-duplicates-diff)
5- Click on a list that you want to clean from duplicates. For example if you have a list called X and a list called Y that have similar emails, and you want to remove the duplicates from Y, click on the list Y.
6- It might take between a second and a minute depending on how big your email lists are.


Helpful? Donations are welcomed!


Any questions or comments? Leave Them below!