MySQL query to set wp_postmeta using term_taxonomy_id value

1 Views

I have a need to set the wp_postmeta value (in table: wp_postmeta) on every post that has a specific term_taxonomy_id (in table: wp_term_relationships):

Specifically:

Read More
  • run a query against every post_ID that has term_taxonomy_id value of 18
  • if the wp_postmeta for that post_ID doesn’t contain the key of _category_permalink_ then add it with the value set to 18, otherwise ignore it

My MySQL foo is poor, so apologies if this makes no sense or I have got tables, names around the wrong way.

UPDATE:
In relation to answers/comments below by @deadlyhifi and @Jot I have the following: 

function cleanup_permalink() {
    static $fnCount = 0; //to run only once
    if ($fnCount) return;
    $fnCount++;
    global $wpdb;
    $results = $wpdb->get_results("SELECT `object_id` FROM $wpdb->term_relationships WHERE `term_taxonomy_id` = 18");
    foreach ($results as $result) {
        add_post_meta( $result, '_category_permalink_', '18', true);
    }
}
add_action('init','cleanup_permalink'); //i'm assuming init is the best place?
Post Views: 1

Related posts

Leave a Reply

3 comments

  1. I think I know what you mean, and if that is the case it’s actually a straightforward operation.

    Firstly get all the object_ids from the term_relationship table.

    $results = $wpdb->get_results("SELECT `object_id` FROM $wpdb->term_relationships WHERE `term_taxonomy_id` = 18");

    And then run through each of those values and update_post_meta to 18.

    foreach ( $results as $result )
    update_post_meta($result, '_category_permalink_', '18', true);

    It couldn’t be that simple could it?

  2. @deadlyhifi’s answer looks good. I would suggest trying out WP_Query though for a more human readable “select” statement, suppose your term taxonomy 18 is the foo category, here’s the equivalent with WP_Query:

    $query = new WP_Query( array(
    'posts_per_page' => -1,
    'category_name' => 'foo', // slug, not name!
) );

while ( $query->have_posts() ) {
    $query->the_post();
    update_post_meta( get_the_ID(), '_category_permalink_', 18, true );
}

    You can also use 'cat' => 18 if you need to stick to the IDs. It’s definitely not faster than the direct SQL method mentioned by @deadlyhifi, but it’s friendlier to the eye.

  3. global $wpdb;
$query = 'SELECT `object_id` FROM '.$wpdb->term_relationships.' WHERE  `term_taxonomy_id`=18';
$obj_ids = $wpdb->get_col( $query );
foreach( $obj_ids as $post_id ) {
    update_post_meta( $post_id, '_category_permalink_', '18' );
}

    Should about do it for you, assuming @kovshenin’s method does not work, since it’s the better method for sure if it does (though I might try a foreach instead of the while personally, either is valid).

    I have not directly tested this, but I’ve tested the query as well as checked the output on $obj_ids, so if it’s not perfect, it’s close.