CODEWITHSUNDEEP
phpsqlwordpresswoocommercecustom-taxonomy
Mr. Jo
Mr. Jo

Reputation: 5281

Get the catalog visibility for a product in a SQL query on Woocommerce

I know, as simple as it sounds but I don't get it running: How can I select the product visibility for a product by his id via SQL?

This is what I have:

global $wpdb;
$result = $wpdb->get_col( "SELECT catalog_visibility FROM XXX WHERE product_id = 3" );

Upvotes: 1

Views: 1946

Answers (1)

LoicTheAztec
LoicTheAztec

Reputation: 254362

The following SQL query will display for a given product ID the catalog visibility as follow:

$product_id = 37;

$result = $wpdb->get_var( "
    SELECT COUNT(tr.object_id)
    FROM {$wpdb->prefix}terms as t
    JOIN {$wpdb->prefix}term_taxonomy AS tt ON t.term_id = tt.term_id
    JOIN {$wpdb->prefix}term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
    WHERE t.name = 'exclude-from-catalog'
    AND tr.object_id = $product_id
" );

echo $result; // 0 for visible | 1 for not visible

Tested and works.

It will give:

  • 1 for hidden from catalog (shop and archives pages)
  • 0 for visible in catalog (shop and archives pages)

Upvotes: 2

Related Questions