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

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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);
        }

    }
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0