1

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?
4
  • 2
    Are you running WP version 3.0 or greater? Jan 3, 2012 at 11:04
  • did you figure this out yet?
    – dwenaus
    Jan 3, 2012 at 19:53
  • Using latest version of WP (always update). And no, not figured out yet. Jan 3, 2012 at 22:10
  • Run the code above - no luck. Function was included in theme functions.php file. Jan 7, 2012 at 10:39

3 Answers 3

2
+100

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?

4
  • 2
    Probably you should use add_post_meta( $result, '_category_permalink_', '18', true);. This ensures that only posts without the key _category_permalink_ will be changed. update_post_meta() will overwrite all existing keys, too.
    – jot
    Jan 4, 2012 at 22:53
  • Better append it as EDIT: to the original question.
    – jot
    Jan 5, 2012 at 18:14
  • edited as per your suggestion.
    – deadlyhifi
    Jan 6, 2012 at 9:29
  • Thanks very much - I had made a mistake in the original question by adding a trailing underscore to the value - once I found that and fixed in PHPMyAdmin, everything was groovy. Jan 8, 2012 at 11:49
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.

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

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.