Bulk moving subscribers to a new list

Hi again!

Clotilde from Chocolate & Zucchini – http://chocolateandzucchini.com

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. :wink:

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. ^^

Many thanks,

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


Thanks a lot Chris! I appreciate your pointing this out.
I only had one list to begin with, so I should be ok on that front (famous last words? ;).

1 Like

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

Thanks so much – great tips!


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.

1 Like

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.

Thanks, @duncanc! I wasn’t aware of that. It turns out I should scroll to the bottom more often! :wink: