Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

3 Answers 3

up vote 2 down vote accepted
+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?

share|improve this answer
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 '12 at 22:53
    
Better append it as EDIT: to the original question. –  jot Jan 5 '12 at 18:14
    
edited as per your suggestion. –  deadlyhifi Jan 6 '12 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. –  Mike Hudson Jan 8 '12 at 11:49
    
Glad you got it sorted - thanks for the points. –  deadlyhifi Jan 9 '12 at 13:16

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

share|improve this answer
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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