query to create woocommerce products from images

4 comments

1. I’d agree that doing this in SQL would be a lot more to learn than PHP. With WordPress functions you can run a query on all the images ( WP_Query ) and then loop through the results and use the info from the images to create a new post using [wp_insert_post()][2]. Finally you can update that post’s _thumbnail_id meta key ( [update_post_meta()][3], which stores the ID of the featured image.

   Totally untested:

   function wpa_convert_images_to_products(){
   
       $args = array(
       'post_type' => 'attachment', // Only bring back attachments
       'post_mime_type' => 'image', // Only bring back attachments that are images
       'posts_per_page' => '-1', // get them all
       'post_status' => 'inherit', // Attachments default to "inherit", rather than published. Use "inherit" or "all".
       );
   
       $images = new WP_Query( $args );
   
   
       if ( $images->have_posts() ) while( $images->have_posts() ) {
   
           $images->the_post();
   
           $post = array(
               'post_title' => get_the_title(),
               'post_excerpt' => get_the_excerpt(),
               'post_type' => 'product',
           );
           $post_id = wp_insert_post( $post );
   
           update_post_meta( $post_id, '_thumbnail_id', get_the_ID() );
       }
   
   
   }
   
   add_action('admin_init', 'wpa_convert_images_to_products' );
   

   You would only want to run this ONCE, or you will get a small ton of new products.

   Editing to add that you can ensure that this only runs one time with the use of a transient.

   function wpa_convert_images_to_products(){
   
     if ( false === get_transient( 'wpa_convert_images_to_products' ) ) ) {
   
       $args = array(
         'post_type' => 'attachment', // Only bring back attachments
         'post_mime_type' => 'image', // Only bring back attachments that are images
         'posts_per_page' => '-1', // get them all
         'post_status' => 'inherit', // Attachments default to "inherit", rather than published. Use "inherit" or "all".
        );
   
       $images = new WP_Query( $args );
   
       if ( $images->have_posts() ) {
   
         while( $images->have_posts() ) {
   
           $images->the_post();
   
           $post = array(
             'post_title' => get_the_title(),
             'post_excerpt' => get_the_excerpt(),
             'post_type' => 'product',
           );
           $post_id = wp_insert_post( $post );
   
           update_post_meta( $post_id, '_thumbnail_id', get_the_ID() );
   
         }
   
         set_transient( 'wpa_convert_images_to_products', $images );
   
       }
   
   }
   add_action( 'admin_init', 'wpa_convert_images_to_products' );
   

2. You want to create Woocommerce products, and to do a good job you don’t need only the post object and the thumbnail: you need taxonomies and custom fields, too: I think your product will have – at least – a price, isn’t it?

   Once you want to bulk create products I suppose that having same taxonomies and same custom fields for all of them is good for you (anyway manually changes are always possible).

   I will suggest an approach, and give related code, but note is all not tested.

   First of all, manually create one product: it will be your reference product.

   Set everything for it: prices, variation, taxonomies… everything. Set also the thumbnail, off course.

   Now just take note of the post ID of this product.

   Second step is create a function that loops through your attachments and creates posts, assigns thumbnails, custom fields and taxonomies.

   ADVICE: please note that woocommerce use some custom tables to save some informations, my method ignore this tables, so you will not bulk assign to posts information/settings about downloadable products. If you want create downloadable products from images you have to modify function before use it. Moreover some WooCommerce plugin use custom tables, too: be aware of this before running the function.

   function wpa_convert_images_to_products($ref_id = 0, $skip_images = array() ) {
   
      // following line ensure that present function is runned once
      if (  get_transient('convert_images_to_products_done') ) return;
   
      //try to remove php limits in execution time an memory
      @set_time_limit (0);
      @ini_set('memory_limit', -1);
   
      if ( ! post_type_exists( 'product' ) || ! $ref_id )
          wp_die('Reference post id is not valid or product post type is not registered.');
   
      $reference = get_post($ref_id);
   
      if ( ! $reference )
          wp_die('Given reference post id is not valid.');
   
      $ref_thumb = get_post_thumbnail_id( $ref_id );
      if ( ! is_int($ref_thumb) ) $ref_thumb = null;
   
      // get reference attributes
      $product_vars = get_object_vars($reference);
      unset($product_vars['ID']);
      unset($product_vars['post_date']);
      unset($product_vars['post_date_gmt']);
      unset($product_vars['post_modified']);
      unset($product_vars['post_modified_gmt']);
      unset($product_vars['comment_count']);       
   
      // get reference custom fields
      $ref_fields = get_post_custom( $ref_id );
   
      // get reference taxonomies
      $all_tax = get_object_taxonomies('product');
      if ( ! empty($all_tax) ) {
        $ref_tax = wp_get_object_terms( $ref_id, $all_tax, array('fields' => 'all') );
      }
   
      // skip reference thumbnail and images passed as second param in function
      $skip_images = array_merge( (array)$skip_images, array($ref_thumb) );
   
      $args = array(
       'post__not_in' => $skip_images,
       'post_type' => 'attachment',
       'post_mime_type' => 'image',
       'posts_per_page' => '-1',
       'post_status' => 'inherit',
      );
      $images = new WP_Query( $args );
   
      $errors = array();  
   
      // start loop through images  
   
      if ( $images->have_posts() ) :
   
        // after that any other function call will fail
        set_transient( 'convert_images_to_products_done', 1);
   
        global $wpdb;
   
        while( $images->have_posts() ) :
          $images->the_post();
          global $post;
          $image = $post->ID;
          $excerpt = get_the_excerpt();
          if ( empty($excerpt) ) $excerpt = get_the_title();
   
          $product_vars['post_title'] = get_the_title();
          $product_vars['post_excerpt'] = $excerpt;
          $product = wp_insert_post( $product_vars );
   
          if ( intval($product) ) {
   
             // insert custom fields
             if ( ! empty($ref_fields) ) {
               $meta_insert_query = "INSERT INTO $wpdb->postmeta ';
               $meta_insert_query .= '(meta_key, meta_value) VALUES ";
               $values = '';
               foreach ( $ref_fields as $key => $array ) {
                 if ( $key != '_thumbnail_id' ) {
                   foreach ( $array as $value ) {
                     if ( $values != '' ) $values .= ', ';
                     $values .= $wpdb->prepare( '(%s, %s)', $key, maybe_serialize($value) );
                   }
                 }
               }
               if ( $values != '' ) {
                 $meta_insert_query .= $values;
                 if ( ! $wpdb->query( $meta_insert_query ) ) {
                   $error = 'Fail on inserting meta query for product ';
                   $error .= $product . '. Query: ' . $meta_insert_query;
                   $errors[] =  $error;
                 }
               }
             }
   
             // insert taxonomies
             if ( ! empty($ref_tax) && ! is_wp_error( $ref_tax ) ) {
               $taxonomies = array();
               foreach ( $ref_tax as $term ) {
                 if ( ! isset($taxonomies[$term->taxonomy]) ) 
                     $taxonomies[$term->taxonomy] = array();
                 $taxonomies[$term->taxonomy][] = $term->slug;
               }
               foreach ( $taxonomies as $tax => $terms ) {
                 $set_tax = wp_set_post_terms( $product, $terms, $tax, false );
                 if ( ! is_array($set_tax) ) {
                   $error =  'Fail on insert terms of taxonomy ';
                                   $error .=  $tax . ' for product' . $product;
                   if ( is_string( $set_tax ) )
                                      $error .= ' First offending term ' . $set_tax;
                   if ( is_wp_error($set_tax) )
                                      $error .= ' Error: ' . $set_tax->get_error_message();
                   $errors[] = $error;
                 }
               }
             }
   
             if ( ! set_post_thumbnail( $product, $image ) ) {
                 $error = 'Set thumbnail failed for product ';
                 $error .= $product . ' image ' . $image;
                 $errors[] = $error; 
             }
          } else {
             $errors[] = 'Insert post failed for image with id ' . $image;
          }   
   
       endwhile;
   
       else : 
   
         wp_die('You have no media.');
   
       endif;
   
        wp_reset_postdata();
   
       if ( ! empty($errors) )
          wp_die('<p>' . implode('</p><p>', $errors) . '</p>');
   
   }
   

   …Yes, it’s a monster function…

   Third and last step is the easiest one, you have to call the function just created and pass the reference product id as param. There are many way to do this task, here just one:

   function launch_convert_images_to_products() {
     //
     // REPLACE THE 0 IN FUNCTION PARAM BELOW WITH YOUR REFERENCE PRODUCT ID
     //
        wpa_convert_images_to_products( 0 );
     //
     //
   }
   add_action('admin_init', 'launch_convert_images_to_products', 30);
   

   Note that normally the function will create products for all your images. I added a second param to wpa_convert_images_to_products function that can be an array of images id you want to skip.

   Another way to create products from images selectively is create a custom taxonomy for attachments (info here) and then use appropriate tax_query in the images WP_Query to select which images to convert.

   Hope it helps.

3. No. There is no command in MySQL that can convert an entry to a woocommerce product specific format. The reason is that the database doesn’t “know” that format.

   There is a command language to do that. You can use an SQL query on the database to change the database directly (without using PHP).

   It is sometimes easier to query the database directly to manipulate large amounts of data. That is one reason for using a database in the first place. Ultimately, that is what Woo Commerce is doing in PHP.

   Given that you are asking this question, it is unlikely that you already possess the knowledge to write this query without a (perhaps steep) learning curve. If it is really going to take a long time to convert the products by hand, pay a professional who is familiar with the format to covert the data for you. The time you save may be worth the cost. Contacting the Woo Commerce authors might be a good resource for finding that person.

   If you have some time and you want to do this yourself, the first thing to do is to find out what the Woo Commerce format is for product storage.

4. INSERT INTO wp_hgwrxz_posts (  
   post_author,  
   post_date,  
   post_date_gmt,  
   post_content,  
   post_title,  
   post_excerpt,  
   post_status,  
   comment_status,  
   ping_status,  
   post_password,  
   post_name,  
   to_ping,  
   pinged,  
   post_modified,  
   post_modified_gmt,  
   post_content_filtered,  
   post_parent,  
   guid,  
   menu_order,  
   post_type,  
   post_mime_type,  
   comment_count)  
   
   VALUES (
     '1',  
     current_date(),  
     current_date(),  
     'HTML',  
     'Custom Printed Tape',  
     '',  
     'publish',  
     'open',  
     'closed',  
     '',  
     'custom-printed-tape',  
     '',  
     '',  
     current_time(),  
     current_time(),  
     '',  
     '0',  
     'http://zastil.co.uk/?post_type=product&p=14',  
     '0',  
     'product',  
     '',  
     '0'
   )
   

Comments are closed.