Get data from custom table and update relative post_meta based on meta_key

Question

long story short: I need to retrieve data from WordPress-created custom Table and add/update metadata to the related post (post_type=product). I can match the posts (products) and custom table fields by post metadata (_sku) which is the key of my query.

To give you more context here follows my query:

SELECT * FROM wpfx_custom_table a 
left join wpfx_postmeta b 
on a.custom_key_id=b.meta_value
WHERE b.meta_key = '_sku'
ORDER BY a.custom_date DESC
LIMIT 1

I have tried this but it does not work:

function update_custom_post_meta_data() {
    global $wpdb;
    global $post;
    
    $args = array(
    'posts_per_page' => -1,
    'post_type' => 'products',
    'suppress_filters' => true,
    'meta_key' => '_sku'
     );

    $posts_array = get_posts( $args );

    foreach($posts_array as $post_array) {
        $post_array->the_post();
        $post_id = $post_array->ID;
        $post_sku = get_post_meta($post_id,'_sku',true);
        
        $CustomTable = $wpdb->prefix.'custom_table';

        $results = $wpdb->get_results ( "SELECT * FROM CustomTable  WHERE `custom_key_id` = $meme_sku ORDER BY `custom_date` DESC LIMIT 1");

        foreach($results as $result){
            $custom_meta_value = $result->custom_column1;
            add_post_meta($post_id, 'custom_meta_key', $custom_meta_value);
        }

    update_post_meta($post_id, 'custom_meta_key', $custom_meta_value);
}
}

Thank you in advance

Answer 1

There are several issues in your function. Do not use backticks in the sql. Don't put quotes around field names. You use get_results with LIMIT 1 when you only need the value from a single column in a single row. And you use $meme_sku instead of... $post_sku ? Just guessing with this example, it might work...

function update_custom_post_meta_data() {
    global $wpdb;
    
    $args = array(
        'posts_per_page' => -1,
        'post_type' => 'product',
        'suppress_filters' => true,
        'meta_key' => '_sku'
    );

    $posts_array = get_posts( $args );
    $customTable = $wpdb->prefix.'custom_table';

    foreach($posts_array as $post) {
       
        $post_id = $post->ID;
        $post_sku = get_post_meta($post_id,'_sku',true);
            
        $result = $wpdb->get_var( "SELECT custom_column1 FROM $customTable  WHERE custom_key_id = $post_sku");
        
        if ( $result != NULL ) {
              update_post_meta($post_id, 'custom_meta_key', $result);
        }

    }
}