This is the technical support forum for WPML - the multilingual WordPress plugin.

Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.

This topic contains 2 replies, has 2 voices.

Last updated by jernejO 1 month, 3 weeks ago.

Assigned support staff: Bobby.

Author Posts
August 29, 2019 at 8:48 am #4478807

jernejO

I am trying to:

Remove media duplicates in database AFTER WPML has already been removed!
Even if I reinstall WPML to try to remove images, this does not work since all WPML tables were removed! I tried to manually reconfigure WPML (Add appropriate languages etc, and even so the images are not reconnected of course)
So this is not useful: https://wpml.org/forums/topic/remove-media-duplicates/

Link to a page where the issue can be seen:

All my pages where I removed WPML totally because we do not need it anymore.

I expected to see:

Clean Media Library without duplicates. An option to clean database of old WPML entries in non-WPML tables that WPML fiddled with!

Instead, I got:

Very messed up Media Library with plenty of duplicates and sometimes 3 or 4 same pictures (multiple IDs, but 1 image on disk).

I would expect WPML would clean up after itself, since that's what the option on "Reset" said.

How can we clean the database of image duplicates and just keep the originals?
This is messed up right now on 15+ of our pages.

Thank you for hopefully a straightforward answer.

August 29, 2019 at 11:26 am #4480491

Bobby
Supporter

Languages: English (English )

Timezone: America/Los_Angeles (GMT-07:00)

Hello,

unfortunately as the WPML database tables have been now dropped the options are quite limited down to manually removing the duplicates from within the Media dashboard using bulk delete.

you could try using the following script but it would require WPML to be installed and since the tables have been dropped i cannot guarantee that it will work 100%

global $wpdb, $sitepress;

$langs = 3; // set how many languages site has

$query = "SELECT ID, post_title, count(ID) AS cnt FROM {$wpdb->prefix}posts WHERE post_type LIKE 'attachment' GROUP BY post_title HAVING cnt > " .  $langs;
$duplicated = $wpdb->get_results($query);

$issues = array();
$i = 0;
foreach ($duplicated as $duplicat) {
    
    $trid = $sitepress->get_element_trid($duplicat->ID, 'post_attachment');
    
    if (!$trid) {
        continue;
    }
    
    
    $translations = $sitepress->get_element_translations($trid, 'post_attachment' );
    
    if (count($translations) < 1) {
        continue;
    }
    
    foreach ($translations as $lang => $tr) {
        $issues[$i]['notin'][] = $tr->element_id;
    }
    
    $issues[$i]['post_title'] = $duplicat->post_title;

    $i++;
}


foreach ($issues as $issue) {
$notin = implode(",", $issue['notin']);
$query = "DELETE FROM {$wpdb->prefix}posts WHERE `ID` NOT IN ($notin) AND `post_title` LIKE '" . $issue['post_title'] . "' AND `post_type` LIKE 'attachment'";

$result = $wpdb->query($query);
 
}

as always please create a database backup before performing any custom scripts

August 29, 2019 at 12:37 pm #4480927

jernejO

My issue is resolved now using a custom MYSQL command I wrote. Added to Github as well if it helps someone. Use at your own risk ofcourse. hidden link