Importing from CSV to Update Foreign Key

We are using phpList 3.0.11 (and I’m still getting used to it after years of v. 2.x). Recently the organization has a new customer database they want to coordinate with, which includes a foreign key. To this point, we have not been using the foreign key option. So now I have a CSV with thousands of records to import, all of which have a foreign key, and thousands of email addresses in phpList without a foreign key. We need to update the records in phpList, adding that foreign key. However, phpList is set up so that, if there is a foreign key column on the CSV and a matching email record is found, it will change the existing record to “Duplicate email@email.com” and create a new record instead. I need to update and not replace. Any suggestions?

I think there is a check box for replace or make duplicate in the import process…

From everything I can find, that’s not the case if you have a foreign key column – that’s what I would have liked. I had found a way to modify the importcsv.php file under ver. 2.x, but everything has changed under 3.x

Do you have “Overwrite Existing” checked?

Yes and it will overwrite if the foreign key matches. But if the foreign key does not match, or if there is no foreign key, it will keep the old and create a new record. You can choose which will be the “duplicate” – new or old – but that’s it.

The problem is spelled out at the beginning: “If you have a column called Foreign Key, this will be used for synchronisation between an external database and the phpList database. The foreignkey will take precedence when matching an existing subscriber.” I would require the option to make the email address column take precedence.

Looks like I may have found a way to use some code found at https://mantis.phplist.org/view.php?id=11845. The basics behind the code is to check if the imported foreign key is the same as the present foreign key. If they are, then foreign key takes precedence. If they are not, then it reverts to using email address only for matching and will update the foreignkey from what is being imported.

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 ***

I took it slow, testing everything along the way, and it all seems to have worked. :smile:

mmm, that fix is all the way from version 2.

I think you have had a bug here. I will ask the developers.

But isn’t that what you want??

I fear you are really misunderstanding how foreign key works here. The very point of a foreign key is that the email does not take precedence. If you want to match by email just match by email - delete the foreign key column from the file you want to upload and just match by email.

So, you know you don’t HAVE to use the key…

You’re not following the issue here. The problem is that I had thousands of records in phplist without a foreign key because, in the past, that was not needed. Now it’s needed, I couldn’t add a foreign key through an import because the blank foreign key would take precedence and create duplicate records. By making the coding change, I was able to coordinate the foreign keys from the two databases. Now I can proceed.

I am planning to return to the existing coding, in case this may cause issues in the future, but as a one-time import to coordinate the foreign keys from two different databases, this worked.

I just don’t think that can be right - I have done a transition like this before with a few thousand emails. at first I had copied and pasted just the emails so there was no FK and then a year or so later I imported the data including the FK and other attributes. It matched by email and just added the FK and attributes to the profile, and did not make duplicates.

@duncanc @Dragonrider @michiel - that is correct yes? If you have a subscriber with the email hello@email.com in your system (with no other data) and then at a later date you want to import the record Foreign key: 142, First Name: John Surname: Smith Shoe Size: 9 and Email hello@email.com, all the new data including the Foreign key will just be added to the existing subscriber profile? Then from that point on your Foreign key is synced.

If this is genuinely not working then it might be a bug?

Well that’s interesting, because it sure didn’t work for me without this code

I will do some testing now.

mmm, you’re right. This is very odd. I will make a bug report for you.

Here is the report https://mantis.phplist.org/view.php?id=17752 - I made it very detailed for you and took my time. Can you read it and make sure you agree with all of the details exactly? Especially the steps to reproduce. Thanks.

Thanks Anna, I appreciate that! And yes, what you have there on the bug report looks great.

1 Like

So this bug should be resolved in the next version :smile:

1 Like

Thanks for the report @jimbits

Hi @jimbits - I am going to be thanking you in the release notes for the new version on Monday (for spotting this bug) could you spruce up your profile a little on phplist.org, maybe add an avatar etc :smile: Feel free to add your company name and self-promote if it helps :smiley: