Batch changing links in autoresponder mails

Hallo

I don’t know where are texts from emails for autoresponders saved. I looked in sql but didn’t find them. Is there any way to search and replace any html text strings (html links) in all autoresponders posts. I have to change domain and to edit 3 x 366 posts (courses for each day in 3 laungages) but manually it would takes eons :frowning:
Thanks in advance for any suggestions :slight_smile:

@dreamcreator Campaigns are held in the message table in the message or textmessage columns.

Thanks a lot for quick answer and a hint.
I deleted table, before exportet and search replaced the domain.
by importing was my only concern that the file could only be 2gb but my exported file was 4,6gb. Now I am not sure if all really was imported.
Otherwise I don’t know how to make it partionally.

@dreamcreator Not sure that I understand what you mean, but if you have the original table then you should be able to replace all occurences of the first website address with the second using sql

update
 `phplist_message`
set message = replace(message, 'https://domain1', 'https://domain2')
LIMIT 50

If the domain occurs only in URLs then this should be ok.

I am not so fit with databases :frowning:
I don’t see a point update or so and afraid to make it wrong way.
And limit 50 sounds also not good whan I have about 1200 messages :slight_smile:

The domains to change are ind messages texts itself. There are links to audiofiles they now are on other domain.

So far it seems to have worked my way to delete messages table, rewrite them via find and replace and then to import again.

Only this 2gb max file size by importing was my concern. But even with 4,6gb it was done without failure alert or so.

Thank you for your kidly support

I am subscriber to my autoresponder too for tests.
Even if I see the letter for today in sql changed with new domain is in the email today with this post still old domain in link to see,
How is it to explain? What could I do?

by sqlnew link but under admin/autoresponder management are the links still with old domain. How is it possible?

@dreamcreator I do not think this is to do with the plugin. The plugin simply submits a campaign when there are subscribers to send to.
If you use a template, then that might have links that need changing.

In template I just wrote manually ne domain, that appears only in addition (footer) of posts.

When I imported a table list_message.sql All messages have the links with new domain there. I just ask me why when looking in admin menu the posts are still to see with old links. Is there some cache or similar? The messages are only stored in messages, are they?

Example: 1 SQL entry (with subdomain) 2. message under admin (still domain)

It is the same message!

sql again

Please, I really need your help. I hope you can find out what could be here the reason.

@dreamcreator i don’t understand what the two screenshots are meant to show but it looks like you might not have changed all of the occurrences of the domain.

This sql will list the id of any campaign that still has the domain

SELECT id
FROM phplist_message
where message like '%href="http://innerpeace.istok.de%'

sorry but I don’t understand what you have posted.
All occurrences are changed by sql, I see this.
But the pictures show one and the same message
1-on sql with changed to subdomain( innerpeace.istok.de, as I wanted)
2-on admin of phplist, same message shows it still unchanged (main domain) what I actually changed by sql.

And this by all about 1200 messages :frowning:

In other words

Begin of message Lektion1 on sql entry:

<p><strong>Teil I</strong></p>
<p><strong><a href="http://innerpeace.istok.de/lessons/001_EINKURS_SonShipRadio_AB_NichtsWasIchSeheBedeutetIrgendetwas_96_dyn.mp3" target="_blank" rel="noopener">LEKTION 1</a></strong></p>

Begin of message Lektion 1 on php admin seite showing in html:

`<p><strong>Teil I</strong></p>
<p><strong><a href="http://innerpeace.de/lessons/001_EINKURS_SonShipRadio_AB_NichtsWasIchSeheBedeutetIrgendetwas_96_dyn.mp3" target="_blank" rel="noopener">LEKTION 1</a></strong></p>


What die’s entry in sql by this message like uuid 54eec3f4-5649-4153-b2c7-ccd27fc9ebce means? Is this some cache or whatever?

@dreamcreator In sql you must be looking at a different table. Possibly you imported into the wrong table.

In the database tables usually have a prefix, the default is phplist, so phplist_message would be the message table. You can verify what the prefix is by looking at the page through menu System> Verify the DB structure

Database is xiwglwbv_list
Table is list_message
Believe me have done it right.
Otherwise I could see entries changed there.
The import was ok but why messages don’t change.
Is there any place more where it is stored?

@dreamcreator I have just noticed that the message field is duplicated in the messagedata table. The “name” column is “message” and the id column is the message id.
That must be what phplist is using when in the editor.

Do you mean list_messagedata or list_message?
In messagedata is only : name. id, data.
Name is: all-lists-targetlist-cat-selected
ID is Numer
Data is:on

Nothing more

I see nothing double there. Where do you see this?
Only in list_message is fulltext of message to see, nowhere else.
Nothing double in list_message

But anyway where would it be stored the content with old domain?

Yes, that is what I previously wrote.
Look for “message” in the name column and the message id for the id column. The data column will then be the content that phplist is showing in the editor.

I think that you should be able to delete all the rows whose name column is “message” because phplist will then get the content from the message table. You can try that for one message to confirm that it works.

I really really don’t understand what you are writing about.

As written , in list_messagedata are only 3 entries

Name is: all-lists-targetlist-cat-selected
ID is Numer (I guess message number)
Data is:on

Nowhere is there message or whatever.

I don’t what how should be there.

Could you explain from where are this entries with old domain name?

I now restored all to before changes and list_messagedata looks absolutely identically by the way.

Can I somehow start again maybe otherway to change everywhere a string “innderpeace.de” with “innerpeace.de”?

@dreamcreator There should be several rows in messagedata table for each campaign. I don’t know why you do not have something similar to this

An alternative approach is to use a rewrite directive in the web server .htaccess file so that any requests for the old URL are automatically redirected to the new URL. If you are not familiar with .htaccess then you should consult your system administrator.