So I have subscribers who want my newsletter in French, and others who want it in English.
I used to have a single list and deal with a language attribute, but after a conversation with Anna and Sam I realized it would be a lot easier to have one list for the English speakers, and another one for the French speakers. Then each of them can have the subscribe page, confirmation emails, autoresponders, etc. in the correct language. (I’ve been doing it wrong for 10+ years! Ah well.
I set up the new subscribe page on my French site so new subscribers will be added to the French mailing list, but I also wanted to batch-move all my current French-language subscribers to this new list.
I went into the database and simply updated the phplist_listuser records to set the new list number for users who have the value “french” in their language preference, like so:
update phplist_listuser t1, phplist_user_user_attribute t2
set t1.listid = 3 {my new french list)
where t1.userid = t2.userid
and t2.attributeid = 5 {language attribute}
and t2.value = 4; {value: French}
Things look fine so far as I can tell, but since I’m not 100% familiar with the DB schema, I wanted to get your opinion: does this look kosher to you? Is the phplist_listuser table the only place where the list subscription is referenced? Or is there a table dependency I missed?
I will be grateful for your thoughts! And of course, if there’s an easy, non-SQL-query way to do that in the interface, I’ll know for next time. ^^
I don’t know how many lists you have, but if some of the French speaking members are already on more than one list, you could be asking for trouble.
I suggest you add into your WHERE
The mapping of the user to a list is in the phplist_listuser table, so you should be ok.
It might be a good idea to clone a copy of your database, and try it out on the clone before you do it on the production database. you can momentarily change the config file to the point at the clone, and confirm that it worked, then switch back to the original database and run the command on that.
I had to run this one on a system a long time ago when I realized that I imported a large bunch to the wrong list.
UPDATE phplist_listuser t1 JOIN phplist_user_user t2 ON t1.userid = t2.id
SET t1.listid = 92
WHERE
t1.listid = 41
AND t2.entered >='2012-10-31%'
AND t2.entered <='2012-11-02%';
It worked flawlessly.
If something does go sour, I tend to rely on DB Dumps and server backups. You should never trust your backups unless you occasionally test their integrity.
I like @danwaterloo 's idea to have 2 live DB’s running and will use that method when I run more complicated queries.
I had a similar case, where I was creating a second list and wanted to have all subscribers from the first list on it. We’re splitting our newsletters in two lists and send both new newsletters to all of the list, letting them opt out of one of the lists if they wish. So here’s what I did:
insert into `php_listuser` (userid, listid, entered, modified)
SELECT userid, 5, now(), now()
FROM `php_listuser`
WHERE listid = 3
Where 5 is the ID of the new list and 3 the ID of the existing list.
Having said this, I wonder why there’s no easy way in the UI to move or copy all subscribers from one list to another.
You can do exactly that on the List Membership page, which shows the members of a list. At the bottom of the page are some commands to move or copy subscribers.
