Skip to content
Advertisement

SQL SELECT query to get stock qty does not return value in function ( but does from phpMyAdmin)

I am running this code in a function in WordPress/Woocommerce to update stock values to the wp_stock_log table when I manually edit stock levels. I am attempting to get the most recent ‘qty’ value for the product and then set it as var $old_qty. I then want to insert it into the table in a new row as ‘old_qty’ so I can see how much the stock level has increased by.

When I run the SELECT query in phpMyAdmin I am successfully getting the last row’s ‘qty’ but when I run the function in wordpress I always get a value of 1.

I have tested to see if the value being returned from the sql query is an integer and it isn’t – but not sure why as the ‘qty’ column is type integer? Even when I convert it to an integer I always get $old_qty value as ‘1’.

Why am I always getting a value of 1?

    public function save_stock( $product ) {
        $current_user = wp_get_current_user();
        global $wpdb;
        
        //get the most recent qty for the product from wp_stock_log table
        $old_qty = $wpdb->get_results("SELECT qty FROM wp_stock_log WHERE product_id = '".$product->get_id()."' ORDER BY id DESC LIMIT 1 ");
        
        $old_qty = intval($old_qty);
        $qty = $product->get_stock_quantity();

        $data = array();

        $data['date_created'] = date( 'Y-m-d H:i:s', time() );
        $data['old_qty'] = $old_qty;
        $data['qty'] = $product->get_stock_quantity();
        $data['product_id'] = $product->get_id();
        $data['user'] = $current_user->display_name;
        $data['reason'] = 'Stock Level Adjusted';
        $wpdb->insert( $wpdb->prefix.'stock_log', $data ); 
    
    }

EDIT The wp_stock_logs table structure is as follows:

|--------|--------------|------------|-----|---------|---------|----------|
|   ID   | date_created | product_id | qty | old_qty | user    | reason   |
|--------|--------------|------------|-----|---------|---------|----------|
| bigint | datetime     |  bigint    | int | int     | varchar | varchar  |
|--------|--------------|------------|-----|---------|---------|----------|
|   1    | 14-08-2020   | 123        | 12  | 10      | user1   | added 2  |
|--------|--------------|------------|-----|---------|---------|----------|
|   2    | 15-08-2020   | 123        | 15  | 12      | user1   | added 3  |
|--------|--------------|------------|-----|---------|---------|----------|
|   3    | 15-08-2020   | 123        | 14  | 15      | user1   | deduct 1 |
|--------|--------------|------------|-----|---------|---------|----------|

Advertisement

Answer

As I am only wanting to retrieve a single value from the database I should use get_var and not get_results (which returns an array).

   public function save_stock( $product ) {
        $current_user = wp_get_current_user();
        global $wpdb;
        
        //get the most recent qty for the product from wp_stock_log table
        //I am retrieving a single value so use "get_var"
        $old_qty = $wpdb->get_var("SELECT qty FROM wp_stock_log WHERE product_id = '".$product->get_id()."' ORDER BY id DESC LIMIT 1 ");
        
        $old_qty = intval($old_qty);
        $qty = $product->get_stock_quantity();

        $data = array();

        $data['date_created'] = date( 'Y-m-d H:i:s', time() );
        $data['old_qty'] = $old_qty;
        $data['qty'] = $product->get_stock_quantity();
        $data['product_id'] = $product->get_id();
        $data['user'] = $current_user->display_name;
        $data['reason'] = 'Stock Level Adjusted';
        $wpdb->insert( $wpdb->prefix.'stock_log', $data ); 
    
    }
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement