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

ikkigaya commented Apr 17, 2018

Thanks you! ❤

@apradipta10

This comment has been minimized.

Copy link

apradipta10 commented Apr 23, 2018

how bout the stock ? can you add stock list ?

@vallerydelexy

This comment has been minimized.

Copy link

vallerydelexy 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

@bobans29

This comment has been minimized.

Copy link

bobans29 commented May 16, 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

Yes you can. Also you can apply additional filters for specific categories, in order to narrow down the resulset.

@tecdisol

This comment has been minimized.

Copy link

tecdisol commented Sep 7, 2019

it works. thank you

@tecdisol

This comment has been minimized.

Copy link

tecdisol commented Sep 7, 2019

i added 2 more colums

regular_price,
sale_price,
price,

`

SELECT
wpaf_posts.post_title AS Product,
wpaf_postmeta1.meta_value AS SKU,
wpaf_postmeta2.meta_value AS regular_price,
wpaf_postmeta3.meta_value AS sale_price,
wpaf_postmeta4.meta_value AS price,
GROUP_CONCAT( wpaf_terms.name ORDER BY wpaf_terms.name SEPARATOR ', ' ) AS ProductCategories
FROM wpaf_posts
LEFT JOIN wpaf_postmeta wpaf_postmeta1
ON wpaf_postmeta1.post_id = wpaf_posts.ID
AND wpaf_postmeta1.meta_key = '_sku'

LEFT JOIN wpaf_postmeta wpaf_postmeta2
ON wpaf_postmeta2.post_id = wpaf_posts.ID
AND wpaf_postmeta2.meta_key = '_regular_price'

LEFT JOIN wpaf_postmeta wpaf_postmeta3
ON wpaf_postmeta3.post_id = wpaf_posts.ID
AND wpaf_postmeta3.meta_key = '_sale_price'

LEFT JOIN wpaf_postmeta wpaf_postmeta4
ON wpaf_postmeta4.post_id = wpaf_posts.ID
AND wpaf_postmeta4.meta_key = '_price'

LEFT JOIN wpaf_term_relationships
ON wpaf_term_relationships.object_id = wpaf_posts.ID
LEFT JOIN wpaf_term_taxonomy
ON wpaf_term_relationships.term_taxonomy_id = wpaf_term_taxonomy.term_taxonomy_id
AND wpaf_term_taxonomy.taxonomy = 'product_cat'
LEFT JOIN wpaf_terms
ON wpaf_term_taxonomy.term_id = wpaf_terms.term_id

WHERE wpaf_posts.post_type = 'product'
AND wpaf_posts.post_status = 'publish'
GROUP BY wpaf_posts.ID
ORDER BY wpaf_posts.post_title ASC

`

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.