0

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

1 Answer 1

0

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

    }
}
5
  • Thank you @shanebp Unfortunately, it does not work. Regarding $meme_sku becoming post_sku is right (it is a typo). LIMIT 1 I have used it because I need to retrieve only the first row of the query. Anyway, I have used your exact code for the test but it gives me this error: fatal error: Uncaught Error: Call to undefined method WP_Post::the_post(). Also, I think in my there is a further error in my code: 'post_type' => 'products' should be 'post_type' => 'product'. Jul 3, 2021 at 17:54
  • Edited to remove the undefined method error. You do not need LIMIT 1 when using get_var
    – shanebp
    Jul 3, 2021 at 18:04
  • Many thanks! I had to change custom_key_id = $post_sku into custom_key_id = '$post_sku' and now it works! Just and advice, if I have to retrieve vars form more than column, Do I need to duplicate $result = $wpdb->get_var( "SELECT custom_column1 FROM $customTable WHERE custom_key_id = $post_sku"); or there is a more concise way? Jul 3, 2021 at 18:17
  • For more than one column from a single row, use get_row. See developer.wordpress.org/reference/classes/wpdb/#select-a-row
    – shanebp
    Jul 3, 2021 at 20:37
  • Since you found my answer useful, please upvote or accept it. wordpress.stackexchange.com/tour
    – shanebp
    Jul 3, 2021 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.