0

I don't know how many products are like this, but I know there are many.

in the db;

The _regular_price value of the post(wc product) is higher than the _price value. I don't know how or when this happened, but the front end is displaying _price - the lower of the two. ( this is bad, we are losing money )

I am trying to run a query that will find all of these post_ids so that I can update them. I would like to update _price with the value of _regular_price

at first I tried this;

SELECT post_id 
FROM wp_postmeta 
WHERE meta_key='_regular_price' > meta_key='_price' 

this returns a very long list of post_id many duplicates, and not always a product.

I then tried to adapt a different answer from stackexchange I came up with this

SELECT post_id, 
       post_title
FROM wp_posts 
   INNER JOIN wp_postmeta 
       ON post_id = post_id
WHERE post_type = 'product'
   AND meta_key='_regular_price' > meta_key='_price'

however I am not exactly understanding inner join and this query does not return the desired result either.

I believe i need to join wp_posts & wp_postmeta here becuase post_type='product' would be pulled from wp_posts & meta_key='_regular_price' > meta_key='_price' would be pulled from wp_postmeta

could it be that meta_key='_regular_price' is not returning an integer?

my next thought was to compare these values with a function, and then update if needed ie;

//functions.php
add_action( 'after_theme_setup', 'benz_update_wc_meta' );
function benz_update_wc_meta() {
  global $woocommerce, $post;
  $reg_price = get_post_meta( get_the_ID(), '_regular_price', true );
  $basic_price = get_post_meta( get_the_ID(), '_price', true );
  if ( $reg_price > $basic_price ) {
    update_post_meta( get_the_ID(), '_price', $reg_price );
  }
}

or something like that -- but no luck there either.

Let me know if you have any advice. thanks.

I have also tried echoing out

$reg_price = get_post_meta( get_the_ID(), '_regular_price', true );

in the theme template woocommerce files -- and this displays the correct price, but when you add product to cart, the wrong price is still used.

  • I would also be fine if we could update all _price with _regular_price value, regardless if is greater -- that would catch everything – RobBenz Jan 23 '17 at 21:43
2

The reason your query doesn't return anything fruitful is because you're not comparing the meta_value of both of the meta_keys, you're comparing non-numeric outcomes of meta_key = 'x' and meta_key = 'y'.

SELECT post_id, 
       post_title
FROM wp_posts 
   INNER JOIN wp_postmeta 
       ON post_id = post_id
WHERE post_type = 'product'
   AND meta_key='_regular_price' > meta_key='_price'

What you want to be doing is comparing the meta_value for these two keys.

SELECT
  price.post_id,
  r_price,
  p_price
FROM
  (
    SELECT
      post_id,
      CAST(meta_value as SIGNED INTEGER) r_price
    FROM
      wp_postmeta
    WHERE
      meta_key = '_regular_price'
  ) regular_price,
  (
    SELECT
      post_id,
      CAST(meta_value as SIGNED INTEGER) p_price
    FROM
      wp_postmeta
    WHERE
      meta_key = '_price'
  ) price
WHERE
  price.post_id = regular_price.post_id
  and r_price > p_price

This will now give you the IDs you're looking for. You're best then looping through the result set and updating the post meta.

  • Thank you -- this returned the necessary post ids. now I just need to learn about loops in SQL, and update the id's respectively. If you have some time today / this week I would appreciate a little breakdown your posted query. Thanks again. – RobBenz Jan 24 '17 at 16:04
  • also is there any way to limit this to post_type='product'? thanks. – RobBenz Jan 24 '17 at 17:12
  • Do your loop in PHP or learn about MySQL stored procedures. It's a topic far to broad to answer in this question - more a learning adventure for you. You should be able to write a quick stored procedure (mysql function) with a simple FOR loop on the results of the above query, then run a simple update on the records. – Trent Jan 24 '17 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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