CODEWITHSUNDEEP
phpsqlwordpresswoocommerceproduct
David S.
David S.

Reputation: 23

Use the product ID instead of product name in a custom SQL query on WooCommerce order items

How can I get order itemmeta for specific product ID and not for product name?

$prodname = $product->get_name();

$result = $wpdb->get_results ( "SELECT 
wp_woocommerce_order_items.order_item_name, 
wp_woocommerce_order_itemmeta.meta_key, 
wp_woocommerce_order_itemmeta.meta_value
    FROM wp_woocommerce_order_items
    RIGHT JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_
        order_items.order_item_id=wp_woocommerce_order_itemmeta.order_item_id
    WHERE `meta_key` LIKE 'ticketcode_fixed' AND `order_item_name` LIKE '$prodname' ");
            
foreach ( $result as $print ) {
    echo '<span class="codes-anzeigen blue">' . $print->meta_value . '</span>';

Upvotes: 2

Views: 485

Answers (1)

LoicTheAztec
LoicTheAztec

Reputation: 254038

To make that SQL query with a product Id as dynamic variable instead of the product name, you will use the following (works for simple product, variable product or product variation):

$results = $wpdb->get_results( $wpdb->prepare("
    SELECT a.order_item_name,  b.meta_key,  b.meta_value
    FROM {$wpdb->prefix}woocommerce_order_items a
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta b
        ON a.order_item_id = b.order_item_id
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta c
        ON a.order_item_id = c.order_item_id
    WHERE b.meta_key LIKE '%s'
        AND c.meta_key IN ('_product_id','_variation_id')
        AND c.meta_value = %d
", 'ticketcode_fixed', $product->get_id() ) );

foreach ( $results as $result ) {
    echo '<span class="codes-anzeigen blue">' . $result->meta_value . '</span>';
}

Tested and works.

Upvotes: 2

Related Questions