asked on

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?

Julian Hansen

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.

Robert Granlund

ASKER

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

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial