PHP Query to Bulk edit Number_Format

Robert Granlund
Robert Granlund used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial