View Issue Details

IDProjectCategoryView StatusLast Update
0017752phplist applicationUser Managementpublic13-08-15 10:52
Reportergingerling 
PrioritynormalSeveritymajorReproducibilityhave not tried
Status resolvedResolutionfixed 
Product Version3.0.13 
Target Versionnext minorFixed in Version3.2.0 
Summary0017752: Cannot merge in Foreign Key infromation when matching by email
DescriptionIf you have a database full of subscribers with just emails (or emails and some attributes) and you later want to merge in a foreign key (FK), a duplicate is created. The import information says it is matched by foreign key.

Steps To Reproduce- import email "joe.blogs@hotmail.com" by copy and paste method. FK remains blank in subscriber profile.

- Create csv with Foreign Key,FIRST NAME,SURNAME,email
13569,Jane,Bloggs,Joe.Blogs@hotmail.com and import it by csv import.

- check that Overwrite Existing: is ticked, so it should match by email and then add in the FK and names to the existing profile. do the import

- import results will say:

1 emails succesfully imported to the database and added to 0 lists.
0 emails subscribed to the lists
1 duplicate emails found.
Subscriber data was updated for 1 subscribers
1 subscribers were matched by foreign key, 0 by email

- a duplicate subscriber profile is created.

Additional Informationthoughts

I don't understand how it can say "1 subscribers were matched by foreign key" because there is NO key in the initial profile to match with.

Logically the system must be matching by email in order to make the duplicate - or else how would it know it was a duplicate.

I am fairly sure this used to work as I only added my FK data for Ethical Pets after a few years, which is what makes me think this is a new bug. Also, when I had that incident with EP during the manual and I ended up with loads of duplicates, this may have been the cause.

Initially reported here: https://discuss.phplist.org/t/importing-from-csv-to-update-foreign-key/565/11 (a confusing report)

https://mantis.phplist.org/view.php?id=11845 report was used to fix the issue

"Because the coding for phpList as a whole has changed, rather than editing the importcsv.php file, as was the instructions from back in 2007, I edited the \admin\actions\import2.php file. Other than that, enough of the basic code was the same, thankfully!

Existing code (around line 206):
$result = Sql_query(sprintf('select id,uniqid from %s where foreignkey = "%s"', $tables["user"], $user["systemvalues"]["foreignkey"]));

Changed to:
// *** START MY CHANGES ***
$foreignkey_exists=FALSE;
$result = Sql_query(sprintf('select id,uniqid from %s where foreignkey = "%s"', $tables["user"], $user["systemvalues"]["foreignkey"]));
$foreignkey_exists = Sql_Affected_Rows();
}
if ($foreignkey_exists) {
// *** END MY CHANGES *** "
TagsNo tags attached.

Relationships

related to 0011845 resolvedmichiel CSV import with Foreign Key causes problems 
related to 0017792 assignedgingerling New menu item not on pootle 

Activities

gingerling

14-07-15 11:47

manager   ~0056421

This bug is serious because you can no-longer merge duplicate accounts into one. Effectively, from the moment you install you must use FK, otherwise you can never use it.

michiel

14-07-15 11:55

manager   ~0056422

no, that's not true. You can happily do that, the only thing is you end up with duplicates (which are not being sent any mails because the email address is invalid).

I agree it's not nice, but it has worked like that for probably the best part of 10 years.

gingerling

14-07-15 12:18

manager   ~0056423

How do you do that? Last time I personally had this issue (on .com) we agreed it was not possible. Can you tell me how to do it and I will test.

When you say "I agree it's not nice, but it has worked like that for probably the best part of 10 years." do you mean the foreign key match has always had this bug? It seemed to work fine when I used it a few years ago, and though most of the manual.

michiel

14-07-15 12:38

manager   ~0056424

https://github.com/phpList/phplist3/commit/bbade62b41807e2cd2254ebab93c1c6f05d54560