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.
I want to select ‘name’, ‘SKU’ and ‘price’ from 2 WordPress tables, namely 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.
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'"
2
Answers
It sounds like you could do with a join so you’re relating the meta information to the right posts.
Example results:
This assumes that the id column in
wp_posts
ispost_id
.It’s important to note that this will return up to two rows for each post, depending on whether it has a meta row for
_sku
and_price
). If you need the data all on the same row (as you might for your export) you might need something like this instead:Example results:
I hope this helps.
This will works for me