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

user_8622891 0
Viewed 265

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>';
woocommerce product wordpress sql php
1 Answers

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.

user_37307540
Related