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 ); }