0

I have old data that has been migrated into a new design. The old data contains multi-language data using 4 different languages and Transcribe.

The new site does not contain transcribe, nor does it need the articles in the other languages. But all other articles in english need to be included.

I need to close all entries that are written in these other languages. Removing Transcribe didn't close the entries. In the url_title column in the exp_channel_titles row, the language prefix is included. So I can search for ru, ar, fa - etc in this row, filter them out, and set the status to closed if the strings ru, ar, fa, etc are found.

So I'd like to search for all entries that contain "ar" and bulk close them. I'd imagine there is a way to do this via sql query.

I was able to write:

UPDATE exp_channel_titles a
    ON a.url_title = '"-ru"'
SET a.status = "closed";
based on the response below, but that gave me a syntax error. I'm not sure that = will find a string, it seems like an exact search, I could be wrong.

2
  • Can you tell me where in the database that setting lives? I don't have access to Transcribe, so if you can tell me where it lives I can probably conjure you a MySQL string.
    – jrothafer
    May 7, 2015 at 19:24
  • It looks like there is a reference to dir="rtl" in the table exp_channel_data. Transcribe isn't installed, so this is manually inputted into the entries, not set through transcribe. field_id_1 contains this content for the most part. I was hoping that ditching transcribe and removing the plugin from the new site would get rid of these entries, but that didn't happen. May 7, 2015 at 19:40

2 Answers 2

0

Here ya go, try this if the url_title will contain -ru:

UPDATE exp_channel_titles a
SET a.status = "closed"
WHERE a.url_title LIKE '%-ru%';

but use this if it ends in -ru:

UPDATE exp_channel_titles a
SET a.status = "closed"
WHERE a.url_title LIKE '%-ru';
1
  • Yes! That worked. I can use both for all language prefixes and it closed the entries... Awesome! May 8, 2015 at 19:24
0

This should do it. It checks channel_entry_data in that field for

dir="rtl"

and matches up those entry_ids to the exp_channel_title table and sets the status to closed. This is untested, but exactly what you want (or a quote misplacement or two away):

UPDATE exp_channel_title a
    JOIN exp_channel_data b 
    ON a.entry_id = b.entry_id AND b.field_id_1 = 'dir="rtl"'
SET a.status = "closed";

Let me know how it goes, and as always, back up those two tables before running this query. Also, you'll probably have to prepend your table names with your database name, so

exp_channel_title

becomes

database_name.exp_channel_title

it depends on if you run the query yourself or in a template {exp:query} tag or via the Database Class ( ee()->db->update(this)->join(that)->etc... ).

3
  • In Sequel Pro I filtered the exp_channel_titles table that includes -ru, or -ar. These are the language prefix that Transcribe would use in the url, so it's more reliable than the div. I can close all of the languages this way manually, but there are thousands of entries for each language. That would take a very long time. UPDATE exp_channel_titles a ON a.url_title = '"-ru"' SET a.status = "closed"; seemed like it was the right track - but I have a syntax error. I also need to search out the url_title for all instances that contain -ru, not match it equally. May 8, 2015 at 17:33
  • Do they all end in -ru or -ar? Then use UPDATE exp_channel_titles a WHERE a.url_title LIKE '"%-ru"' SET a.status = "closed"; and that will hit all url titles that end in -ru.
    – jrothafer
    May 8, 2015 at 18:13
  • There's an extra quotation around the %-ru in my previous comment but I can't edit it anymore
    – jrothafer
    May 8, 2015 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.