1

I have the current problem : I run a WordPress website with Woo Commerce and every few days I refresh the product list deleting and importing over 8000 products at a time .

Now, every single time I get +/- 3500 products deleted from the database so I have to remove the photos and the thumbnails for each one .. so i have to delete more then 12.000 photos .

Using any plugin is not an option, they all freeze / stop working so I need to build my own / function or plugin and I need a starting point .

I have a list with the old products and I delete them like this :

foreach($old_product_list as $oldproduct){

    $wpdb->show_errors();
    $delq = $wpdb->get_results(
                     "  DELETE a,b,c
                        FROM wp_posts a
                        LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id ) 
                        LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id ) 
                        LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id ) 
                        LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )                    
                        WHERE a.post_title='".$mysqli->real_escape_string($oldproduct['name'])."' " );  

[.....]

1

As a rule, I suggest you try to stick as much as possible to Wordpress core functions when modifying the database, as they will handle associated data automatically - e.g. delete associated post meta, associated taxonomy info, etc. (Woocommerce stores product image data as product post_meta, see below for details.) This might slow things down in comparison to direct access to the database, but the database will be much cleaner as a result.

Needless to say, do backup your database before trying any of this...

First, you need to work with post ids.

global $wpdb;
foreach ($old_product_list as $old_product) {
   $prod_names[] = $mysqli->real_escape_string($oldproduct['name']);
}

$prod_ids = $wpdb->get_results($wpdb->prepare("
                 SELECT ID FROM {$wpdb->prefix}posts 
                 WHERE post_type='product' AND post_title IN (%s)",
                 explode(',',$prod_names)));

Now, there are three different things you may want to delete, in increasing order of 'destructiveness':

  1. Delete the references to image ids in the products post_meta
  2. Delete the 'attachment' posts associated to each image - this will remove the images from Wordpress media Gallery
  3. Delete the actual image files from the server

Point 1. This one is easy. But to do it right, you need to use wordpress functions, as the code comments hopefully make clear.

foreach ($prod_ids as $id) {
    // This will also remove all post_meta associated to the product
    // and apply any delete_post filters added by Woocommerce or other plugins
    // Second arg forces deletion, bypassing the trash
    wp_delete_post($id,true); 
} 

Points 2 and 3, simple case.

If your images are all unique to each product and not linked from anywhere, their post_parent will be the product_id, so you can use this version of https://wordpress.stackexchange.com/a/109803/40965, modified to bypass the trash:

function delete_product_attachments($id) {
    if ('product' !== get_post_type($id)) return;

    $media = get_children(array(
        'post_parent' => $id,
        'post_type' => 'attachment'
    ));

    foreach ($media as $img) {
        unlink(get_attached_file($img->ID)); //delete files from server
        wp_delete_attachment($img->ID,true); //force post deletion, bypassing trash
    }
}

add_action('before_delete_post','delete_product_attachments');

The last line makes sure that this function is called whenever wp_delete_post is called, so you don't need to change the solution to point 1 above.

Points 2 and 3, general case. If some images are shared among products (some of which are perhaps not deleted in the current batch), or linked from other posts, the above solution will break things.

Here is where we need to understand how Woocommerce handles product images. The images associated to a product are stored as post_meta '_thumbnail_id' and '_product_image_gallery' for the product's post ID. _thumbnail_id is the post_id of the image (post with post_type='attachment'); _product_image_gallery is a comma-separated string of image post ids.

Just to get you started, here is how to get all the attachment post ids to handle.

$image_ids = $wpdb->get_col($wpdb->prepare("
                 SELECT meta_value FROM {$wpdb->prefix}post_meta
                  WHERE meta_key = '_thumbnail_id' AND post_id in (%s)",
                 explode(',',$prod_ids)));
$gallery_ids = $wpdb->get_col($wpdb->prepare("
                 SELECT meta_value FROM {$wpdb->prefix}post_meta
                  WHERE meta_key = '_product_image_gallery' AND post_id in (%s)",
                 explode(',',$prod_ids)));

$gallery_ids = explode(',',$gallery_ids); //get all ids in a single string
$gallery_ids = array_unique(implode(',',$gallery_ids));
$image_ids = array_merge($image_ids,$gallery_ids);
//do something with all $image_ids

And here comes the hard part: apply the solution above only to images that can be safely deleted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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