[mod] Import emails from any database

3rd party code for phpList

[mod] Import emails from any database

Postby Sonya » 11:24am, Thu 19 Aug, 2010

This mod allows to import emails from any database. You have to provide the table name and the field where email is stored in the third-party database.

The installation is very simple. You copy 3 files into your installation and can start immediately. Please read install.txt in the attachment for the further instructions, howto guide and common issues.

Attention: This mod does not import any user attributes, just email addresses! I am not sure what happens if you do have any required user attributes defined in phplist.

Feedback is appreciated.
Attachments
phplist_import_mod_1.0.zip
Import emails from any database.
(5.87 KiB) Downloaded 844 times
Sonya
phpList newbie
 
Posts: 2
Joined: 9:00am, Thu 19 Aug, 2010

Re: [mod] Import emails from any database

Postby mamaduenn » 6:13pm, Thu 02 Sep, 2010

hey sonya , just tried your nice idea, have the .12 of phplist and your work do not run here, no 5d option to see, what can i do?
mario
mamaduenn
phpLister
 
Posts: 7
Joined: 5:02pm, Fri 27 Aug, 2010

Re: [mod] Import emails from any database

Postby Sonya » 6:47am, Fri 03 Sep, 2010

mamaduenn wrote:hey sonya , just tried your nice idea, have the .12 of phplist and your work do not run here, no 5d option to see, what can i do?
mario


If you do not see the 5th option it can only mean that you have not replaced the file admin/info/en/import.php or you use other language (not English). If you use other language, you have to translate the file above into your language and place it in your language folder.
Sonya
phpList newbie
 
Posts: 2
Joined: 9:00am, Thu 19 Aug, 2010

Re: [solved] Import emails from any database

Postby mamaduenn » 4:20pm, Mon 06 Sep, 2010

i got it, just lenguage problems , thanks
mamaduenn
phpLister
 
Posts: 7
Joined: 5:02pm, Fri 27 Aug, 2010

Re: [mod] Import emails from any database

Postby johnap » 2:18am, Fri 25 Feb, 2011

just perfect....thanks
johnap
PL Geek
 
Posts: 64
Joined: 11:11am, Tue 09 Jan, 2007

Re: [mod] Import emails from any database

Postby duffers2010 » 4:11pm, Sat 19 Mar, 2011

I can okay this mod - thanks - have been searching for a while
duffers2010
phpList newbie
 
Posts: 2
Joined: 3:59pm, Sat 19 Mar, 2011

Re: [mod] Import emails from any database

Postby Ron » 5:33am, Fri 22 Jul, 2011

sonya,
thanks for the code, just what we needed but the import although showing successful including phplist saying the users are in the database and being able to actually see the users in the mysql database, phplist will not show the addresses in the user list, it shows the id number and [] empty brackets except for 1 od the imported emails. if we click on the id number phplist reveals the address and they are correct. what do you think is causing the addresses to not show and also they are considered invalid by phplist, ie we do and invalid email check and they all are included. any ideas??
Ron
 

Re: [mod] Import emails from any database

Postby PaulS » 4:10pm, Tue 09 Aug, 2011

Thanks for the code Sonya which I have found extremely useful (& added to below).

I wanted to be able to import the first name of members as well as the email and also not select some members from my existing database. So with a few additions to Sonya's code I seem to have achieved this :D

I have posted the amended script below if anyone else wants to use it. Having said that I am new to phplist so perhaps the more experienced members here may also like to check it out and let me know if I've missed anything.

I've also added the facility to quickly auto delete the existing entries in the phplist before re-loading the information from my own database - this takes into account anyone who has left since the last newsletter and also resets the Id to start at 1. It's optional and activated with an additional checkbox on index5.php. It means I can have a completely fresh and updated database in under 60 seconds.

Other amendments to the code:
I've put most of the remote database info directly into the textboxes (shown below in CAPS and needs to be changed to individual requirements). This saves a lot of time & typos when inporting the info
There is also code to make sure that the first name starts with a capital letter.
Additional code is shown with #NEW in the comments
Code: Select all
<?php
require_once dirname(__FILE__).'/accesscheck.php';
if (!ALLOW_IMPORT) {
    print '<p>'.$GLOBALS['I18N']->get('import is not available').'</p>';
    return;
}

# import from a different database

if ($require_login && !isSuperUser()) {
    $access = accessLevel("import4");
    if ($access == "owner")
        $subselect = " where owner = ".$_SESSION["logindetails"]["id"];
    elseif ($access == "all")
        $subselect = "";
    elseif ($access == "none")
        $subselect = " where id = 0";
}

function connectLocal() {
    $database_connection = Sql_Connect(
            $GLOBALS["database_host"],
            $GLOBALS["database_user"],
            $GLOBALS["database_password"],
            $GLOBALS["database_name"]);
    $GLOBALS["database_connection"] = $database_connection;
    return $database_connection;
}
function connectRemote() {
    $database_connection = Sql_Connect($_POST["remote_host"],
            $_POST["remote_user"],
            $_POST["remote_password"],
            $_POST["remote_database"]);
    $GLOBALS["database_connection"] = $database_connection;
    return $database_connection;
}
#NEW This clears the database of all entries and resets the ID so they start at 1
    $delete_old = $_POST["delete_old"] == "yes"?"1":"0";
    if ($delete_old==1) {
    mysql_query("truncate phplist_user_user");
    mysql_query("truncate phplist_listuser");
   }
#End
$result = Sql_query("SELECT id,name FROM ".$tables["list"]." $subselect ORDER BY listorder");
while ($row = Sql_fetch_array($result)) {
    $available_lists[$row["id"]] = $row["name"];
    $some = 1;
}
if (!$some)
# @@@@ not sure about this one:
    echo $GLOBALS['I18N']->get('No lists available').', '.PageLink2("editlist",$GLOBALS['I18N']->get('add_list'));
#foreach ($_POST as $key => $val) {
#  print "$key => $val<br/>";
#}

#NEW Additional fields added below to collect first name & cancellation status - an additional click box added to opt for deletion of all entries in existing phplist_user_user & listuser databases before update. Info for remote database added directly into text boxes.
if (!$_POST["remote_host"] ||
        !$_POST["remote_user"] ||
        !$_POST["remote_password"] || !$_POST["remote_database"]) {
    printf( '
  <p>'.$GLOBALS['I18N']->get('remote_server').'</p>
  <form method=post>
  <table>
  <tr><td>'.$GLOBALS['I18N']->get('server').'</td><td><input type=text name="remote_host" value="localhost" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('user').'</td><td><input type=text name="remote_user" value="DATABASE USER NAME" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('passwd').'</td><td><input type=text name="remote_password" value="password" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('database').'</td><td><input type=text name="remote_database" value="DATABASE NAME" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('table_prefix').'</td><td><input type=text name="remote_prefix" value="TABLE NAME" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('usertable_prefix').'</td><td><input type=text name="remote_userprefix" value="FIELD NAME FOR EMAIL" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('usertable_prefix2').'</td><td><input type=text name="remote_userprefix2" value="FIELD NAME FOR FIRST NAME" size=30></td></tr>
  <tr><td>'.$GLOBALS['I18N']->get('usertable_prefix3').'</td><td><input type=text name="remote_userprefix3" value="FIELD NAME FOR OPTIONAL CONDITION" size=30></td></tr>
',$_POST["remote_server"],$_POST["remote_user"],$_POST["remote_password"],
            $_POST["remote_database"],$_POST["remote_prefix"],$_POST["remote_userprefix"],$_POST["remote_userprefix2"],$_POST["remote_userprefix3"]);
    print '<tr><td colspan=2>';
    if (sizeof($available_lists) > 1)
        print $GLOBALS['I18N']->get('select_lists').'<br/>';
    print '<ul>';
    foreach ($available_lists as $index => $name) {
        printf('<li><input type=checkbox name="lists[%d]" value="%d" %s>%s</li>',
                $c,$index,is_array($_POST["lists"]) && in_array($index,array_values($_POST["lists"]))?"checked":"",$name);
        $c++;
    }
    printf('
  </ul></td></tr>
<tr><td>'.$GLOBALS['I18N']->get('users_as_html').'</td><td><input type="checkbox" name="markhtml" value="yes" %s></td></tr>
<tr><td colspan=2>'.$GLOBALS['I18N']->get('info_overwrite_existing').'</td></tr>
<tr><td>'.$GLOBALS['I18N']->get('overwrite_existing').'</td><td><input type="checkbox" name="overwrite" value="yes" %s></td></tr>
<tr><td>'.$GLOBALS['I18N']->get('delete_old').'</td><td><input type="checkbox" name="delete_old" value="yes" %s></td></tr>
  <tr><td colspan=2><input type=submit value="'.$GLOBALS['I18N']->get('continue').'"></td></tr>
  </table></form>
  ',$_POST["markhtml"] == "yes"?"checked":"",$_POST["overwrite"] == "yes"?"checked":"",$_POST["delete_old"] == "yes"?"checked":""
    );
#NEW additional code added to end of previous line for the deletion of existing database entries option
} else {
    set_time_limit(600);
    ob_end_flush();
    include_once("structure.php");
    print $GLOBALS['I18N']->get('connecting_remote')."<br/>";
    flush();
    $remote = connectRemote();
    if (!$remote) {
        Fatal_Error($GLOBALS['I18N']->get('cant_connect'));
        return;
    }
 #NEW additional entries for first name & cancel status
    $remote_table = array(
            "mail_field" => $_POST["remote_userprefix"],
            "first_name" => $_POST["remote_userprefix2"],
            "cancel" => $_POST["remote_userprefix3"],
            "users" => $_POST["remote_prefix"],
    );
    print $GLOBALS['I18N']->get('getting_data').$_POST["remote_database"]."@".$_POST["remote_host"]."<br/>";

    $usercnt = Sql_Fetch_Row_Query("select count(*) from {$remote_table["users"]}");
    print $GLOBALS['I18N']->get('remote_has')." $usercnt[0] ".$GLOBALS['I18N']->get('users')."<br/>";
    if (!$usercnt[0]) {
        Fatal_Error($GLOBALS['I18N']->get('no_users_to_copy'));
        return;
    }
    $totalusers = $usercnt[0];
    flush();

    print '<h1>'.$GLOBALS['I18N']->get('copying_users').'</h1>';
    # copy the users
    $usercnt = 0;
    $existcnt = 0;
    $newcnt = 0;
    $htmlemail = $_POST["markhtml"] == "yes"?"1":"0";
    while ($usercnt < $totalusers) {
        set_time_limit(60);
        connectRemote();
#NEW Additional code for first name & cancel status
        $req = Sql_Query("select {$remote_table["mail_field"]}, {$remote_table["first_name"]} from {$remote_table["users"]} where {$remote_table["cancel"]} is NULL limit $usercnt,1");
        $user = Sql_Fetch_Array($req);
        $usercnt++;
        $new = 0;
        if ($usercnt % 20 == 0) {
            print "--------------------------------------<br/>";
            print "<b>$usercnt / $totalusers</b><br/>";
            print "--------------------------------------<br/>";
            flush();
        }
        connectLocal();
        $query = "";
        $exists = Sql_Fetch_Row_Query(sprintf('select id from %s where email = "%s"',$tables["user"],$user[$remote_table["mail_field"]]));
        if ($exists[0]) {
            $existcnt++;
            print $user[$remote_table["mail_field"]] .' '.$GLOBALS['I18N']->get('exists_locally')." ..";
            if ($_POST["overwrite"]) {
                print " .. ".$GLOBALS['I18N']->get('overwrite_local')."<br/>";
                $query = "replace into ".$tables["user"] . " set id = ".$exists[0].", ";
            } else {
                print " .. ".$GLOBALS['I18N']->get('keep_local')."<br/>";
            }
            $userid = $exists[0];
        } else {
            $newcnt++;
            $new = 1;
            print $user[$remote_table["mail_field"]] .' '. $GLOBALS['I18N']->get('new_user')."<br/>";
            $query = "insert into ".$tables["user"]. " set ";
        }
        if ($query) {
            $exists = true;
            do {
                $uniqueId = md5(uniqid(mt_rand(0,1000).$email));
                $rs = Sql_Fetch_Row_Query("select count(*) returnCount from {$tables["user"]} WHERE uniqid='".addslashes($uniqueId)."'");
                $exists = ($rs[0]["returnCount"]==0)?false:true;
            } while ($exists == true);
            $user = array(
                    "email"      =>   $user[$remote_table["mail_field"]],
                    "entered"      =>   date ("Y-m-d H:m:s"),
                    "confirmed"   =>   1,
                    "uniqid"      =>   addslashes($uniqueId),
                    "htmlemail"  =>  $htmlemail,
                    $extradata2 = $user[$remote_table["first_name"]],
                    "extradata" => ucwords(strtolower($extradata2))
                                );
#NEW Additional code above to add first name and change first letter to uppercase
            foreach ($DBstruct["user"] as $colname => $colspec) {
                if (array_key_exists($colname, $user)) {
                    $query .= sprintf('%s = "%s",',$colname,addslashes($user[$colname]));
                }
            }
            $query = substr($query,0,-1);
            #print $query . "<br/>";
            Sql_Query("$query");
            $userid = Sql_Insert_id();
        }
        if (is_array($_POST["lists"]))
            foreach ($_POST["lists"] as $list) {
                Sql_Query(sprintf('replace into %s (listid,userid,entered) values(%d,%d,"%s")',
                        $tables["listuser"], $list["listid"], $userid, addslashes($user['entered'])));
            }
    }
    print "$totalusers / $totalusers<br/>";
    flush();
    # @@@@ Not sure about this one:
    printf('%s %d %s %s %d %s<br/>',$GLOBALS['I18N']->get('Done'),$newcnt,
            $GLOBALS['I18N']->get('new users'),
            $GLOBALS['I18N']->get('and'),
            $existcnt,$GLOBALS['I18N']->get('existing users'));
}

?>
   


I've tested it and it all seems to work as intended. When sending newsletters I start with Dear [EXTRADATA] as the 1st line which personalises the email.
PaulS
phpList newbie
 
Posts: 2
Joined: 3:30pm, Tue 09 Aug, 2011

Re: [mod] Import emails from any database

Postby sintsu » 9:07am, Mon 17 Oct, 2011

Hi, all
I try to use this mod to import about 200000 emails (no other attributes),

but the page will crush and return to the original import5.php when progress about 20000 lists

I cannot use the code such as bigdumpto import

because it won't append the extra attribute in mysql that phplist needed (such as uniqid)

Is there other way to prevent the situation, or it's a configuration issue such as apache2 or MySQL?

Thanks for any opinion reply.
sintsu
PL Nut
 
Posts: 21
Joined: 5:42am, Fri 07 Oct, 2011

Re: [mod] Import emails from any database

Postby panthe » 7:30pm, Sat 14 Apr, 2012

Hi,
I've imported successfully a lot of user from another different DB but now the new imported user can't subscribe.
They will receive mail without the possibility of unsubscribe directly.
Someone can help me with this issue?

Thanks
panthe
panthe
phpLister
 
Posts: 7
Joined: 9:30am, Tue 03 Apr, 2012

Re: [mod] Import emails from any database

Postby stephenchoularton » 7:10am, Sat 23 Feb, 2013

Hi Sonya

I think this is great but its stopped putting people on the required list. See attached.

Can you help?
Attachments
list content.jpg
list content.jpg (146.54 KiB) Viewed 10419 times
import report.jpg
import report.jpg (125.88 KiB) Viewed 10419 times
stephenchoularton
phpList newbie
 
Posts: 2
Joined: 2:36am, Wed 21 Nov, 2012

Re: [mod] Import emails from any database

Postby Dragonrider » 8:26am, Sat 23 Feb, 2013

stephenchoularton wrote:Hi Sonya

I think this is great but its stopped putting people on the required list.

Can you help?


Sonya hasn't been active here since September 2010, I would assume she is no longer supporting this item.
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: [mod] Import emails from any database

Postby PaulS » 3:42pm, Tue 20 Jan, 2015

Just reviving an old topic that I last contributed to way back in 2011!!

I appreciate that Sonya is no longer around but maybe someone else can help?

Using the code (from earlier in this topic) I have been successfully importing info from another database every month to send out a members newsletter. The whole process was seamless - login to phpList, click a couple of tick boxes, wait a few minutes and my up-to-date list of members was ready for me!

The problem I have now is that it doesn't work with the latest version of phpList (V3.0.5). I could use the CSV import system but this is a lot more involved than what I had previously.

I have searched this forum (& others) and it's a topic that comes up regularly (so there is a demand) but no-one seems to have the answer.

Does anyone have a solution - if so at what price?

Thanks :D
PaulS
phpList newbie
 
Posts: 2
Joined: 3:30pm, Tue 09 Aug, 2011


Return to Contributions: Plug-ins, Add-ons, Mods

Who is online

Users browsing this forum: No registered users and 2 guests