WP Run function to update metadata

Sometimes we need to update some taxonomie or values into a serialized value within a meta_value.

The problem with serialized data is that can’t be updated with mySQL queries.

Sometimes we need to update some taxonomie or values into a serialized value within a meta_value.

The problem with serialized data is that can’t be updated with mySQL queries.

In this specific sample we have a table in access with a post_id field and 2 other fields with the geolocation of 1000 posts. Because I don’t feel like go post by post in the admin to update the posts.

Other problem is that the the plugin that save the values in the wp database use serialized data also i need to assign to all these posts a new category to use them in a template.

Thats how we solve it as always can be other ways to do it that it the one that come to our minds.

1 Upload the access table with the data to the mySQL server

I like to to this with a fantastic tool that connects to the server and allow you to upload the tables you want.

http://www.bullzip.com/products/a2m/doc/info.php

2 Create the new category in the admin in our case is not a wp category but a custom taxonomy category call “ait-dir-item-category”

There is a nice query to run in the mySQL server to see how many times an specific taxonomy is used in the posts:

SELECT t.term_id, count(tr.object_id) AS times_used, t.name, t.slug, tt.term_taxonomy_id, tt.taxonomy, tr.object_id
FROM oe476_terms t
LEFT JOIN oe476_term_taxonomy tt USING (term_id)
LEFT JOIN oe476_term_relationships tr USING (term_taxonomy_id)
GROUP BY t.term_id
HAVING tt.taxonomy = "ait-dir-item-category"
ORDER BY times_used

ok so I know that the term_id that I want to use to update the posts is 400

3 We need a place to run our code I use a dirty simple way to do this.

I first add my function where ever your template have the functions normaly function.php and add a new shortcode:

add_shortcode( 'myFunction', 'update_geolocation' ); 
function update_geolocation() {
    //here will come the code
    return "my output";    
}

4 Create a page with the shortcode in the content and access the page in the browser:

[myFunction]

If i see that the output is “my output” i can change the function with the correct code.

5 Update the function:

add_shortcode('myFunction', update_geolocation');
function update_geolocation() {
    $sql = "select Post_id, Lat, Lon from  tblGeodatawithposts";
    global $wpdb;
    $rows = $wpdb->get_results($sql, ARRAY_N);
    $counter = 0;
    foreach ($rows as $row) {
        $counter ++;
        $data = get_post_meta($row[0], '_ait-dir-item');
        $data[0]['gpsLatitude'] = $row[1];
        $data[0]['gpsLongitude'] = $row[2];
        update_post_meta($row[0], '_ait-dir-item', $data[0]);
        wp_set_object_terms( $row[0], 400, 'ait-dir-item-category', true );
    }
    return $counter . " records updated...";
}

the code it is very simple:

first get the table with the data and then use the wp

update_post_meta with update the value base on the post id and wp_set_object_terms to change the custom categories with true at the end to append the category without delete the existing.

Then delete the function and the page….

Good luck and nice coding…