MySQL query to set wp_postmeta using term_taxonomy_id value

Question

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:

• 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?

Answer 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?

Answer 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.

Answer 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.