[Closed] How to extract translated content from Database using queries

Hello,

I will share our dev team's feedback to your request:

"It is better to use a plugin like the import/export plugin than a SQL query.

Some posts rely on icl_strings and icl_string_translations table and pulling them just like that won't be sufficient (depends on the page builder)."

https://wpml.org/download/wpml-export-and-import/?section=changelog

Please note that I am directly discussing this with our dev team and my colleague is an expert when it comes to database work.

I will ask again, but I am going to urge you to review the following documentation and give the plugin we mentioned a try. In the documentation I share with you, it's used in conjunction with WP All Import.

https://wpml.org/documentation/related-projects/wpml-export-and-import/

Please let me know your results after going through the documentation shared. You should be able to do this with just 1 export/import, no need for bulk work.

Hi there,

There is an automatic function that closes our tickets after a certain time passes with inactivity, you should be ok for now.

If by chance it does close automatically please open a new ticket and reference this one or ask to be connected with me and we will be able to continue.

When you do have time, yes, please review the documentation and test it then let me know your results.

I also had some feedback from our developer team, while providing a full solution with SQL will indeed require some custom work (which we do not cover in the forum) we hope that the following provides some hints and help.

--------------------------------------------------------

The query that you are using is fetching the information only from the posts table.

Many page builders store their info in the postmeta table - so it depends on actually what the postmeta has for that same post.

Along with the wp_posts, he should probably export the data for the postmeta as well - it all depends on the page builder...

For which you would probably need another query like:

SELECT * FROM `wp_postmeta`
INNER JOIN wp_icl_translations AS t ON wp_postmeta.post_id = t.element_id INNER JOIN wp_posts AS p on wp_postmeta.post_id = p.ID
WHERE post_author = 2

Also if you would like to export only the translated content, perhaps add a condition to check for source_language_code being not NULL:

SELECT *
FROM `wp_posts`
INNER JOIN `wp_icl_translations` AS t ON wp_posts.ID = t.element_id
WHERE post_author = 2 AND t.source_language_code IS NOT NULL

This will list only the translated posts and not the originals.