2

I have inherited a WordPress installation with WooCommerce already set up and with about 1,000 products and variations.

I've done some Googling and searching here but haven't found adequate answers to my questions.

Firstly, can anyone guide me to writing a MySQL query to modify all prices based on based on text included in a product name? For example, if the product starts with '123-' change the price to 100.

Secondly, and fairly closely related, is that none of the products have Shipping Classes. I've created the Shipping Classes and would like to update them by a similar method using the same update criteria.

I have absolutely no idea what to search for and replace. When I have looked at the database it doesn't make much sense. I have an export of the database via a free plugin, but that doesn't have the field names the same as the database has them. I do understand from other posts here that products and prices, etc are in the wp_posts table. I've also read on here that I need to update both _price and _regular_price but my database dump only has 'Price' so I don't know if it is _price or _regular_price or both.

Any guidance would be appreciated.

So far the best I can figure would be:

UPDATE wp_posts
SET <Price>,<Regular Price> = 100
WHERE LEFT(<Product Name>,4) LIKE '123-'

UPDATE wp_posts
SET <Shipping Class> = <Class Value>
WHERE LEFT(<Product Name>,4) LIKE '123-'

Unfortunately I also don't know whether to use the Shipping Class slug or the full Shipping Class name.

C.Bru
  • 21
  • 4
  • Knowing that my example is not adequate I've been continuing to look around. I've found this interesting question on here which has been helpful but has not yet fully answered my questions: http://stackoverflow.com/questions/9878985/need-to-get-productdata-out-of-mysql-database – C.Bru Aug 01 '16 at 23:55
  • For anyone answering this question later, I have used a plug in to do the tasks I needed. I would have preferred to learn and do it that way, but ran out of time. Instead of deleting this question, I've left it here in case anyone else finds it useful. – C.Bru Aug 09 '16 at 02:17
  • What plugin did you use? – Kaah Nov 28 '16 at 13:14
  • 2
    @Kaah, I used WooCommerce Advanced Bulk Edit -- a paid plugin purchased from Envato. – C.Bru Nov 30 '16 at 10:50

1 Answers1

1

Shipping class solution as follows:

Shipping class definitions are stored in the wp_terms table and the wp36_term_taxonomy table. See the read_product_data method in the class-wc-product-data-store-cpt.php class. It has the following code: $set_props['shipping_class_id'] = current( $this->get_term_ids( $product, 'product_shipping_class' ) );

How do I find the shipping class id in WooCommerce? also explores this usage. The wp36_term_relationships table holds the link between the term and the product information, e.g.

object_id term_taxonomy_id

6183------54

where 6183 is the identifier of the product and 54 is identifier of the shipping class.

In order to set the shipping class we therefore need to insert records into the wp36_term_relationships table.

The following SQL will acheive this:

-- Delete any existing entries, if necessary.  Add other criteria as required, e.g. post_title like '123-%'.

-- Note that the prefix wp36 may be different for your installation.  Be sure to include to include term_taxonomy_id as a criterion otherwise, you will delete all of the other terms information such as categories and tags.

Delete from wp36_term_relationships where term_taxonomy_id = 54 and object_id in (select id from wp36_posts where post_type = 'Product')

-- Now create the relationship records

INSERT INTO wp36_term_relationships (object_id, term_taxonomy_id, term_order) SELECT id, 54, 0 FROM wp36_posts WHERE post_type = 'Product' and  post_title like '123-%'
B5A7
  • 863
  • 12
  • 20