[mod] CSV user import -- matching Foreign Key vs. Email

[flug]

I had this problem with my PHPlist installation:

1. We have about 5000 users subscribed, some are members of our organization, others are just interested people.

2. About 1000 of them are members and so I like to periodically update their PHPlist information via CSV import from our membership database, which we try to keep up to date.

3. We also have several different email lists that people can subscribe to. All members are subscribed to our "announce" email list but there are six or eight other lists they can choose to join if they like.

4. Originally I had not used a foreign key with any users. However after going through the update cycle several times I realized we would have better luck with updating member emails etc. if we could match via Foreign Key.

5. So I added a Foreign Key and imported those users.

6. If you include a Foreign Key in your import record, then PHPlist matches entirely by Foreign Key and completed ignores email (as currently implemented in PHPlist 2.10.x).

7. This creates a problem in that my 1000 members are now imported completely afresh (their "new" Foreign Key in the import doesn't match with their current Foreign Key, which is blank). Thus these members' old PHPlist records are all changed to "Duplicate xxx@yyy.zzz" which renders those users inactive. Of course these users are still there--they all have newly imported/created records with the new foreign key and the newly imported attributed data.

8. The problem is that many of these members had subscribed to several different email lists, changed some of their attributes (which are not necessarily all updated via the membership database import), etc.

Even though the situation described above is a one-time problem that I have fixed by a bit of hacking, in fact the general problem of people starting out without a Foreign Key and then adding it (via a CSV list import) is an ongoing problem for us.

We are always working on those "interested people" who subscribe to our email list to join our organization. At that point they will be added to our membership database and they will get a foreign key.

Then when I import the membership database the existing PHPlist information, include list subscriptions, for that members will be lost.

The solution I believe is that PHPList should implement "soft matching" for Foreign Keys vs email.

That is to say:

1. if the Foreign Key from the import matches the Foreign Key in a current PHPlist record, then that is a match

2. if there is no Foreign Key match, then matching of import to existing records reverts to email matching

I'll explain the simple Mod to do this in a separate message.

[flug]

Here is the mod to implement "soft Foreign Key matching for CSV imports" for the "import emails with different values for attributes" function.

This should work with any 2.10.x version of PHPlist and the 2.11.x version as exists in the CVS as of 10/7/2007, but I have specifically tested it with 2.10.4.

Find the file importcsv.php in phplist/admin/commonlib/pages

Make a backup copy of this file (so you can return to this version if your mod gets messed up).

Around line 418 find these lines:

Code: Select all

      if ($cnt % 25 == 0) {
        print "<br>\n$cnt/$total";
        flush();
      }
      if ($user["systemvalues"]["foreign key"]) {
        $result = Sql_query(sprintf('select id,uniqid from %s where foreignkey = "%s"',
          $tables["user"],$user["systemvalues"]["foreign key"]));

Replace them with these lines:

Code: Select all

      if ($cnt % 25 == 0) {
        print "<br>\n$cnt/$total";
        flush();
      }

      //change, bhugh, 10/5/2007, so that imports are are "soft matched" by foreign key, then email
      //In "soft matching", imports are matched by foreign key IF AND ONLY IF there is an
      //existing foreign key in the database that matches the import foreign.   
      //If there is no matching foreign key then an attempt is made to match via email address instead.

      $foreignkey_exists=FALSE;
      if ($user["systemvalues"]["foreign key"]) {
        $result = Sql_query(sprintf('select id,uniqid from %s where foreignkey = "%s"',
          $tables["user"],$user["systemvalues"]["foreign key"]));
        $foreignkey_exists = Sql_Affected_Rows();

      }
      //if the import has a foreign key & that foreign key matches something, we go ahead & do it that way
      //otherwise we skip this & go on to try to match via email address
      if ($foreignkey_exists) {

Save the new importcsv.php file and upload it to the proper directory in your PHPlist installation.

[jocala]

Like you, I recently added a foreign key and was just preparing to import my first big batch from the outside database when (thankfully) I read your post before proceeding. You have saved me quite a bit of grief. Thank you!

I haven't decided how to handle it yet...I don't like to change the code because it's a pain to manage changes when phpList is updated. For this first import, I'll probably just create a query file from the import list that does what I want and run it through phpMyAdmin. But in the long term I'll have the same problem as you.

It sure would be nice if your "soft foreign key" mod was rolled into a future version of phpList.

Jo