View Issue Details

IDProjectCategoryView StatusLast Update
0011845phplist applicationSubscriber Importpublic13-07-15 21:04
Reporterbhugh 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version2.11.3 
Target Version4.0.xFixed in Version2.11.7 
Summary0011845: CSV import with Foreign Key causes problems
DescriptionWhen a user already exists in the database with no foreign key, and then updated records are imported via a CSV file import that now includes a foreign key, the records are not matched.

This creates a problem because list subscription and other information (RSS frequency, attributes) are lost for that user.

Also it makes it essentially impossible to start out with a database of users with no foreign key and then later decide to add a foreign key. When the database with this new "foreign key" field is imported, under current PHPlist (through 2.11.3) there is no way to match the new & old users.

My suggested solution is is to change the way users are matched if the Foreign Key field exists in the CSV import file:

 * Currently (PHPlist 2.10.1 thru 2.11.3) if Foreign Key exists in the import file record, matching is attempted via Foreign Key. If no match is located via Foreign Key, then the record is imported as a new record (which may be marked "Duplicate" depending on other settings).

 * My suggested change is to change to a "Soft Match" scenario when importing a file with Foreign Key field. If the Foreign Key in the import file matches a Foreign Key in the PHPlist database, then that is a match. However if no Foreign Key match is located, then match is attempted via the Email field.

This would allow a Foreign Key to be added (via CSV file import) to users that currently exist in the PHPlist database.

BTW I consider this "major" because I imported over 1200 users with a new foreign key. Most already existed in the PHPlist database. Due to the presence of the Foreign Key all 1200 existing entries were moved to "Duplicate". That meant that all list subscriptions, RSS frequency selection, and other existing info for those 1200 users was lost. It took some pretty fancy footwork to recover from that loss of data.

This was a major problem for me I and would imagine that I will be far from the only person to try to do something like this.

More about this issue is at http://forums.phplist.com/viewtopic.php?p=37643

The solution is pretty simple and included below in the additional info section.
Additional InformationHere is the mod to implement "soft Foreign Key matching for CSV imports" for the "import emails with different values for attributes" function.

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

Around line 418 find these lines:

-----
      if ($cnt % 25 == 0) {
        print "
\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:

-----

      if ($cnt % 25 == 0) {
        print "
\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) {

-----
TagsNo tags attached.

Relationships

related to 0017752 resolvedmichiel Cannot merge in Foreign Key infromation when matching by email 

Activities

shimfish

23-06-10 11:06

reporter   ~0051040

The *real* fix can be found at http://forums.phplist.com/viewtopic.php?f=24&t=24027&p=60387&hilit=foreignkey#p60387

The problem is that the code uses 'foreign key' instead of 'foreignkey'

h2b2

23-06-10 13:01

manager   ~0051042

Thanks for referring to this fix. Could you mention which phpList version you are running?
The above report is related to development release v2.11.3. The forum thread you mentioned refers to phpList v2.10.9. However in v2.10.12 admin/commonlib/pages/importcsv.php has changed compared to v2.10.9.

shimfish

23-06-10 13:06

reporter   ~0051043

This fix worked for me on v2.10.10

I assumed it hadn't been fixed on more recent versions as this bug report was still open.

shimfish

23-06-10 13:29

reporter   ~0051044

From a quick look at the svn repo, it does appear that this fix is still valid for v2.11.3 though I should stress I haven't tried using anything other than v2.10.10.

The importcsv.php still seems to refer to 'foreign key' whereas structure.php uses 'foreignkey'

(I also missed this ticket was for a future version as I only noticed that it was opened in 2007....)

h2b2

24-06-10 00:12

manager   ~0051045

OK, thanks for the feedback.

michiel

23-05-12 02:18

manager   ~0051590

importcsv.php line 632 (and elsewhere) uses "foreignkey" without space.

So, kind of presume this fixed, but feel free to re-open, if you can replicate the problem on the latest SVN code (or next dev-release)