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

    (@bcworkz)

    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

    (@jcc5018)

    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

    (@bcworkz)

    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

    (@jcc5018)

    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 = 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 = select ‘post_id’ from ‘wp_hyhg_postmeta’ where ‘meta_value’ = ‘product’

    Moderator bcworkz

    (@bcworkz)

    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