0

I want to select 'name', 'SKU' and 'price' from 2 WP tables: wp_posts and wp_postmeta. I don't know how to get data from 'meta_value' column twice for 'meta_key'='_price' and 'meta_key'='_sku' , ex. enter image description here

My current query:

                    "SELECT a.post_title, m1.meta_value, m2.meta_value FROM wp_posts a, wp_postmeta m1, wp_postmeta m2
                WHERE a.post_type='product' AND m1.post_id = a.ID
                AND m1.meta_key='_sku'
                AND m2.meta_key='_price'"
3
  • 1
    Is there a particular reason you're getting these values this way? The proper way to get a WooCommece product is wc_get_product(). Feb 19, 2020 at 0:08
  • I need to use some library to export product names, SKU and prices to one CSV file. This library connects using PDO and needs an SQL query. Feb 19, 2020 at 7:22
  • 1
    In that case, you'd probably be better off asking your question over at Stackoverflow and looking at the MySQL UNION clause.
    – TomC
    Feb 19, 2020 at 7:39

1 Answer 1

0

Not tested 100% but this might work:

SELECT a.post_title, m1.meta_value AS SKU, m2.meta_value AS PRICE
FROM wp_posts a
LEFT JOIN wp_postmeta m1
    ON a.ID = m1.post_id
LEFT JOIN wp_postmeta m2
    ON a.ID = m2.post_id
WHERE a.post_type='product' 
AND m1.meta_key='_sku'
AND m2.meta_key='_price'

Note that _price field corresponds to the "sale price" (if existing) so you may grab also the "list price":

SELECT a.post_title, m1.meta_value AS SKU, m2.meta_value AS SALE_PRICE, m3.meta_value AS LIST_PRICE
FROM wp_posts a
LEFT JOIN wp_postmeta m1
    ON a.ID = m1.post_id
LEFT JOIN wp_postmeta m2
    ON a.ID = m2.post_id
LEFT JOIN wp_postmeta m3
    ON a.ID = m3.post_id
WHERE a.post_type='product' 
AND m1.meta_key='_sku'
AND m2.meta_key='_price'
AND m3.meta_key='_regular_price'

query output

2
  • Thank you for query :-) Feb 21, 2020 at 18:17
  • If it fits your needs can you accept it? Feb 21, 2020 at 23:36

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.