Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Select Product name, SKU, price and category from WordPress / WooCommerce with MySQL query
SELECT
wp_posts.post_title AS Product,
wp_postmeta1.meta_value AS SKU,
wp_postmeta2.meta_value AS Price,
GROUP_CONCAT( wp_terms.name ORDER BY wp_terms.name SEPARATOR ', ' ) AS ProductCategories
FROM wp_posts
LEFT JOIN wp_postmeta wp_postmeta1
ON wp_postmeta1.post_id = wp_posts.ID
AND wp_postmeta1.meta_key = '_sku'
LEFT JOIN wp_postmeta wp_postmeta2
ON wp_postmeta2.post_id = wp_posts.ID
AND wp_postmeta2.meta_key = '_regular_price'
LEFT JOIN wp_term_relationships
ON wp_term_relationships.object_id = wp_posts.ID
LEFT JOIN wp_term_taxonomy
ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'product_cat'
LEFT JOIN wp_terms
ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_posts.post_type = 'product'
AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC
@ikkigaya

This comment has been minimized.

Copy link

commented Apr 17, 2018

Thanks you! ❤

@apradipta10

This comment has been minimized.

Copy link

commented Apr 23, 2018

how bout the stock ? can you add stock list ?

@vallerydelexy

This comment has been minimized.

Copy link

commented Mar 15, 2019

noob question here, i run the query in phpmyadmin, it shows my 1000+ product.
can i edit the price directly from there?
it such a pain to edit from woocomrce

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.