PHP Query to Bulk edit Number_Format

PHP number format.  A college of mine installed a wholesale plugin on a WooCommerce Site.  This brilliant Plugin over wrote all sales prices in the DB.  If the sale price had no decimal point it got rid of the decimal point.  If the decimal point had two decimal points, it added 2 more zeros onto the sales price.  WooCommerce wants all prices to have two decimal points so show properly.

I am writing a piece of code for my functions.php file to update all sales prices in the DB and make sure they have 2 decimal points.

I have 2 questions;
1. This will out put a number with 2 decimal points, even if there are no decimal points on the number, correct?
$meta_value = number_format( $result->meta_value, 2 );
2. Can you see anything wron with the following code?
</php
global $wpdb;
$postmeta = $wpdb->prefix . "postmeta";
$posts = $wpdb->prefix . "posts";

// 1. First query: Get all prices
$results = $wpdb->get_results( "
    SELECT $postmeta.*
    FROM $postmeta
    INNER JOIN $posts ON $postmeta.post_id = $posts.ID
    WHERE $posts.post_type ='product'
    AND $postmeta.meta_key ='_sale_price'
    AND $postmeta.meta_value != ''
    ORDER BY $postmeta.meta_id ASC
" );

// iterating through each price and update it
foreach($results as $result){
    $meta_id    = $result->meta_id;
    $post_id    = $result->post_id;
    $meta_key   = $result->meta_key;
    $meta_value = number_format( $result->meta_value, 2 );

    // 2. Udating prices query
    $wpdb->query( $wpdb->prepare( "
        UPDATE $postmeta
        SET meta_id = $meta_id, post_id = $post_id, meta_key = '$meta_key', meta_value = '$meta_value'
        WHERE $postmeta.meta_id = $meta_id " ) );

     // 3. Clear all related product transient cached data (refresh prices)
     wc_delete_product_transients($post_id);
}

Open in new window

I found and modified the code above and it looks like what I need.  However this part (   $wpdb->query( $wpdb->prepare( ") confuses me.  I want to run the query, not prepare it.  Will that statement run the update and is the above code  correct?

Also, as a bonus, WooCommerce does not show updated prices until you manually re-save a product.  Do you know of a programmatic way to update all of the products so I don't have to manually re-save them?
LVL 7
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
preparing a statement just validates and binds variables to a query before it is executed. It is usually good practice to do this instead of putting variables directly into a query string. In this case however it is not being used correctly - the query is constructed as a normal string based query would rendering the prepare part useless because the variables are embedded in the string. To use it properly you would use an sprintf type syntax with string placeholders and variables provided as parameters. Refer to the docs here https://developer.wordpress.org/reference/classes/wpdb/prepare/

I am not following the bit about where WC wants 2 decimal 'points' (assuming you mean places here - two decimal points is not a valid number) how is the number being stored in the database - is it a string? That would not make much sense - if not a string then you are limited by the type that is specified for the field - you format the output after retrieving from the DB - but the number is still stored as a number.
0
rgranlundAuthor Commented:
So, is this correct? Especially line 13.
I'm not sure I really understand the principal.  I found this code and it seems to do what I need to do, But I'm not sure I understand.
Each of the queries should be formatted like the follow?
$wpdb->prepare( "SELECT * FROM table WHERE column = %s AND field = %d OR other_field LIKE %s", array( ‘foo’, 1337, ‘%bar’ ) );
Should I update the   // 2. Udating prices query to match this format?

<?php
global $wpdb;
$postmeta = $wpdb->prefix . "postmeta";
$posts = $wpdb->prefix . "posts";

// 1. First query: Get all prices
$results = $wpdb->get_results( "
    SELECT $postmeta.*
    FROM $postmeta
    INNER JOIN $posts ON $postmeta.post_id = $posts.ID
    WHERE $posts.post_type LIKE '%product%'
    AND $postmeta.meta_key LIKE '%_sale_price%'
    AND ($postmeta.meta_value != '' OR $postmeta.meta_value != 'NULL')
    ORDER BY $postmeta.meta_id ASC
" );

// iterating through each price and update it
foreach($results as $result){
    $meta_id    = $result->meta_id;
    $post_id    = $result->post_id;
    $meta_key   = $result->meta_key;
    $meta_value = number_format( $result->meta_value, 2 );

    // 2. Udating prices query
    $wpdb->query( $wpdb->prepare( "
        UPDATE $postmeta
        SET meta_id = $meta_id, post_id = $post_id, meta_key = '$meta_key', meta_value = '$meta_value'
        WHERE $postmeta.meta_id = %d
    ", $meta_id ) );

     // 3. Clear all related product transient cached data (refresh prices)
     wc_delete_product_transients($post_id);

Open in new window

WC stores Sale Price in the Postmeta table.  I'm assuming as a string.  However, if you add a sales price to a product in WC, say $150 and the sale price is $100.  if you put in 100 it won't show the correct sale price.  It repeats $150 as the price and as the sale price.  The only way it will show the sale price is if you insert, 100. 0r 100.0 or 100.00.  It breaks in the same way if you enter 100.000
0
Julian HansenCommented:
No, what you are doing on line 13 is checking for $postmeta.meta_value not equal to the string 'NULL' to check if it is not null then you must do this
AND ($postmeta.meta_value != '' OR $postmeta.meta_value IS NOT NULL)

Open in new window

Note the 'IS NOT' and there are no quotes around NULL

Should I update the   // 2. Udating prices query to match this format?
It will work as you have it but if you do it that way you might as well put the query directly into the $wpdb->query i.e. remove the $wpdb->prepare
Prepared statements are better - so if you want to use them then convert to the correct format.

It breaks in the same way if you enter 100.000
I see your price is being stored in the PostMeta table which stores key / value pairs as strings. It is not the database that is enforcing the rule it is WC.

What you can do is use number_format() on the number to get the correct value

<?php
$number = ['100', 101.1, '1001.67','123.456'];
foreach($number as $n) {
  // $n - the number we are converting
  // 2 - the number of decimals
  // '.' - the decimal separator (default '.')
  // '' - the thousands separator (default ',')
  echo number_format($n, 2,'.','') . "<br>";
}

Open in new window

Output
100.00
101.10
1001.67
123.46

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.