Title: adding post meta with sql query
Last modified: August 22, 2016

---

# adding post meta with sql query

 *  [jcc5018](https://wordpress.org/support/users/jcc5018/)
 * (@jcc5018)
 * [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/)
 * I have been trying to arrange my site so that my URL structure is set to domain.
   com/collection/%collection%/%product-group%
 * Where %collection% is a taxonomy term, collection is the index, and %product-
   group% is the product in question (its a group cause each group will list many
   product variations)
 * Anyway, wordpress seems unable to handle this simple task, so I am switching 
   methods in order to get my permalink structure correct.
 * I am simply going to create the collection, product, and variant pages all under
   the same custom post type with no taxonomies to deal with. Then each will display
   a page template depending on the value of a custom field (collection, product,
   or variant)
 * Well, since I already have the products and variants already uploaded to their
   respective custom post types, I don’t want to go through all the data again to
   set featured images and content. (If it werent for that, I would just re import
   all the data.)
 * So I created a function to switch post types in the data base for me. It works
   except for one part.
 * here is the code
 *     ```
       function change_post_type($from,$to,$value)
       {     global $wpdb;
   
       //change metadata
       $post_id = $wpdb->get_results ('SELECT ID FROM wp_hyhg_posts WHERE post_type=pods_product_grps' , object); 
   
                foreach ($post_id as $id){
       $table='wp_hyhg_postmeta';
       $data= array(
           'post_id' => $id,
           'meta_key' =>'input_type',
           'meta_value' => $value
       );
   
       $wpdb->insert( $table, $data );
   
             }
   
        //UPDATE
             $data2= array(
                 'post_type' => $to,
                  );
              $where =array('post_type' => $from );
   
         $wpdb->update('wp_hyhg_posts', $data2, $where);
   
             }
   
             change_post_type ('pods_product_grps', 'uc-product', 'product');
       ```
   
 * So, I was able to get the post type to switch from ‘pods_product_grps to uc-product
   easily enough, but I also need to add a meta value ‘input_type’ to every product
   I just switched.
 * Since it is already changed, I can simply add input_type=>product to all products
   currently labeled as uc-product
 * But I also need to run this code where the function is:
 * change_post_type (‘product_variant’, ‘uc-product’, ‘variant’);
 * So I need to get the meta data figured out for the ones I just converted, before
   I add the new set of data to the mix.
 * The product and Variants input type both have additional sets of meta data that
   are displayed conditionally based on the value selected.
 * Products will have a field called group_id and variants will have a field (product
   sku) which contains the group_id plus a number
 * So I need another query that finds all post marked ‘product’, gets the post_id,
   then compares the group_id to product_sku and updates the parent_id field to 
   the corresponding parent relationship.
 * That is probably confusing. In other words, a product variant is a child of a
   product if group_id is in product_sku So I’d like to make a query that populates
   the parent field of the post table based on that condition.
 * Hope someone can help with this one.

Viewing 5 replies - 1 through 5 (of 5 total)

 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/#post-5258561)
 * If I understand correctly, this is a one time script to bring existing data up
   to date with your new organization scheme.
 * I don’t think it’s feasible to incorporate your parent logic into a query, best
   to use PHP to determine the correct parent ID. It’s then simple enough to use`
   $wpdb->update()` to insert the new ID, but you can also use `wp_update_post()`
   for that matter. Being a one time script, efficiency doesn’t matter too much,
   do what ever is easiest to code for you.
 * A general outline for your code would look like this:
    Query for all posts that
   need to be brought up to date. Use `foreach` to loop through every post returned.
   In the loop, get what ever meta data necessary and determine the parent ID In
   the loop, update that post’s parent’s ID record. Once the loops complete, everything
   will be up to date.
 *  Thread Starter [jcc5018](https://wordpress.org/support/users/jcc5018/)
 * (@jcc5018)
 * [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/#post-5258588)
 * ok, So I added the post meta by running the import plugin again to update the
   fields.
 * Now I’ve got another similar problem that the import plugin didnt fix for me.
   I previously had the product post uploaded, and I just now added the variations
   to that. So each variation needs its parent post defined. I have over 600 entries
   so its another thing, I dont really want to do manually. But I am having trouble
   figuring out how to word the function to make this work.
 * The Tables: columns in question are wp_hyhg_postmeta(pm) :post_id, meta_key, 
   meta_value wp_hyhg_post (p): ID, post_content, post_parent, post_type
 * So I need to create a query that updates p.post_parent with pm.post_id If meta_key
   =’group_id’ And meta_value is in post_content.
 * post_content for the relevant post types contains a product sku in the form of“
   SKU: $product_sku” (SKU: AB_1000)
 * So for example if the postmeta row is post_id=1, meta_key= group_id, and meta_value
   = AB
 * and the post data has post_content = “SKU: AB_1000” then post_parent will be 
   updated to 1 because AB appears in the string.
 * For an extra check, the corresponding post meta for each post with a sku code
   should also have meta_value =product
 * If comparing to the post_content field could cause problems, I also have the 
   product_sku listed in the post meta table as a meta_key/ value
 * There may be an easier way to do what I need to do, but right now, Im seeing 
   that I have to pass the post id back and forth to do several different comparisons.
   I’m assuming some sort of join statement is what I’m going to need, but I not
   sure how to write it.
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/#post-5258635)
 * Once again, I think the logic you need is too convoluted for a straight query.
   I’m not saying SQL cannot do this, but the required query is beyond my skills
   as well. I find the procedural style of PHP much easier to work with.
 * 600 records will not take that long for PHP to work through, though it may take
   longer than the default time out perid of 30 secs. You can easilly extend this
   period by placing something like this in your wp-config.php file:
    `set_time_limit(
   120); // time in secs`
 * Don’t forget to restore the default or remove the line when you’re finished.
 * In case you have more questions, you may want to know I’ll have limited Internet
   connectivity for a while. I’m not ignoring you, I simply can’t see your post.
   Good luck.
 *  Thread Starter [jcc5018](https://wordpress.org/support/users/jcc5018/)
 * (@jcc5018)
 * [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/#post-5258663)
 * This is what I’m working with so far. I’d be happy to do it within php and wordpress,
   but I’m not entirely sure how. If i can just set variables, run foreach statements,
   ect, then I may be able to figure it out. But If I have to use the $wpdb methods
   to accomplish this, I may need some assistance. This is not a complete list, 
   but should be a good start.
 * update ‘wp_hyhg_post’ p set ‘post_parent’= @id1
 * set [@id](https://wordpress.org/support/users/id/) = select ‘ID’ from ‘wp_hyhg_post’
   where ‘post_type’=’uc-collections’
    select ‘meta_value’ from ‘wp_hyhg_postmeta’
   where ‘meta_key’ = ‘group_id’ AND ‘post_id’ =@id1 set @id1 = select ‘post_id’
   from ‘wp_hyhg_postmeta’ where ‘meta_value’ = ‘variant’
 * set [@product](https://wordpress.org/support/users/product/) = select ‘post_id’
   from ‘wp_hyhg_postmeta’ where ‘meta_value’ = ‘product’
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/#post-5258713)
 * Yes, a PHP approach is basically running a `foreach` loop on a query result, 
   manipulating the data assigned to variables, then updating the result back to
   the DB. The use of variables and the multitude of ways you can manipulate them
   based on application of logic is why I prefer PHP over SQL. I imagine it can 
   all be done with SQL, but how to structure complex logic in SQL is beyond my 
   abilities. In PHP, I can structure the code to reflect my own mental model of
   the problem.
 * I’m not saying one way is better than another, just that I’m not going to be 
   of any help with a SQL approach. I’m more than happy to help you through a PHP
   solution if you have the patience to communicate this way. I’ll have complete
   Internet access restored next week, but even then we will be likely be limited
   to one or two messages each per day at best.
 * All `$wpdb` methods do is save you the trouble of setting up communication with
   the WP database and in some cases it does some basic query formatting for you,
   not a big advantage if you are not familiar with PHP. In particular, `$wpdb->
   query()` simply applies any SQL query you care to write to the DB, you can actually
   use just this one method and ignore the rest if you want, though there are advantages
   to other methods. For example, certain methods apply some minimal sanitizing 
   or at least escaping of data for you. The problem is you need to know which do
   and which do not. It’s all documented in the Codex at least.
 * You first query for all records you want to work on, by which ever method you
   choose, the records are assigned to a $wpdb property or a PHP variable, depending
   on the method used. The data will be in the form of an object or array, depending
   again on the method and/or supplied parameters. Whatever the form, you can step
   through each record with `foreach` and apply changes based on whatever conditions
   are found for each record in turn. Once the data for that record are altered 
   as needed, call `$wpdb->update()` to commit the changes to the DB.
 * I’m sure you can manage this overall structure, it’s the data manipulation in
   each loop that can get tricky, depending on what your needs are. All I can suggest
   is you struggle through it, using the Codex and php.net to find the functions
   you need to manipulate the data. However, it appears you’ll be mainly applying
   basic if/elseif/else logic more than needing specific functions. While PHP requires
   a certain syntax, if/else logic is the same in any logical language, so I’m certain
   you can work through this.
 * I encourage you to just give it your best effort, if you get stuck on any particular
   part, I’ll be happy to help you through it. Good luck!

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘adding post meta with sql query’ is closed to new replies.

## Tags

 * [query](https://wordpress.org/support/topic-tag/query/)
 * [sql](https://wordpress.org/support/topic-tag/sql/)

 * In: [Hacks](https://wordpress.org/support/forum/plugins-and-hacks/hacks/)
 * 5 replies
 * 2 participants
 * Last reply from: [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * Last activity: [11 years, 9 months ago](https://wordpress.org/support/topic/adding-post-meta-with-sql-query/#post-5258713)
 * Status: not resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics