1

I have hundreds of art images in my WP media library, each of which I want to convert into a woocommerce product, and set as featured image of that product.

Rather than manually adding new products and setting featured images, which would take days of repetitive work, is there a way to do this automatically in MySQL?

  • Why would you want to do this in MySQL? That is almost certainly going to be the hard way. – s_ha_dum Jul 24 '13 at 13:28
  • well, the WP database is in MySQL. so I figure some MySQL command would do this. – pdme Jul 24 '13 at 13:52
  • Yes, the database is MySQL but it is almost always easier to manipulate that data with PHP – s_ha_dum Jul 24 '13 at 13:58
  • but I want to make changes to the actual database. only once. not manipulate the data that I extract from the database... – pdme Jul 24 '13 at 14:08
4

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' );
  • it's the right approch, but you need also taxonomies and custom fields to obtain products you can use in real world.. Of couse can be added manually later, but once you are looping images why don't do the complete job? – gmazzap Jul 25 '13 at 22:59
  • I agree, but the OP didn't give more to go on, so it is more of a proof of concept. Once you have inserted the post and have the $post_id, it is a trivial matter to use wp_set_object_terms() to add product categories/tags (though difficult to say if the OP could differentiate between the images just from querying the images), since media images don't usually have taxonomies. Also, price could be added (since it is merely post meta), but again how to automatically tell which images are what price? – helgatheviking Jul 26 '13 at 0:05
  • In my solution see I suggest to create a reference product and than just cloning it (taxonomies, meta) and changing only the the title, the excerpt and the thumbnail based on related image. – gmazzap Jul 26 '13 at 1:26
  • thanks a lot, both of you. I chose @helgatheviking's answer because it is simple and I don't have any custom fields or taxonomies. – pdme Jul 27 '13 at 9:24
5

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.

  • The transient is a wicked clever idea to ensure the function is only run once. +1 for that – helgatheviking Jul 26 '13 at 3:55
0

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.

  • WooCommerce products are merely a custom post type called 'product'. – helgatheviking Jul 25 '13 at 16:43
-1
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&#038;p=14',  
  '0',  
  'product',  
  '',  
  '0'
)
  • 1
    How would that be an answer to the question, can't identify how, please add an explanation to clarify. – Nicolai Mar 26 '15 at 20:05

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.