0

trying to fetch products from wooocommerce to script with sql, but have no luck in it. tried that:

$query = "SELECT
    p.id, p.post_title as title, pm.meta_value as price
FROM wp_posts p
LEFT JOIN
    wp_term_relationships tr
ON
    p.ID = tr.object_id
LEFT JOIN
    wp_term_taxonomy tt
ON
    tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN
    wp_terms t
ON
    t.term_id = tt.term_id
    and tt.term_id = t.term_id
LEFT JOIN wp_postmeta pm
ON
    p.ID = pm.post_id
    and pm.meta_key = 'price'
WHERE
1
    and tt.taxonomy = 'product_type'
    and t.name = %s
    AND post_type = 'product'
    AND post_status = 'publish'
GROUP BY p.id
"

but had no product. what should i do to fetch products with sql in woocomerce?

Prains
  • 17
  • 5
  • `SELECT ..... LEFT JOIN. wp_term_taxonomy tt ON ........ WHERE tt.taxonomy = 'product_type'`. => Why not write an INNER JOIN ? – Luuk Jan 06 '23 at 13:30

1 Answers1

1

First of all, don't use SQL queries, it's a bad practice. You should use Wordpress built in WP_Query

Second, this question has already been answered over here : Woocommerce get products I'll suggest to have a look there :)

And for more infos about Wordpress Queries, over here : https://developer.wordpress.org/reference/classes/wp_query/

Mc Manuel
  • 105
  • 8
  • Flaging a question as duplicate can be done on better ways then creating an answer. Naming something bad practice always requires a good explanation why. So where is yours? – Jonas Metzler Jan 06 '23 at 13:32
  • Wordpress got a built-in wp query that comes with a bunch of security features when it comes to making request, so using wp queries is always a better option – Mc Manuel Jan 06 '23 at 13:35