wp admin - $wpdb update query in plugin only updating one column

I am doing an update in from woocommerce admin, edit order details. I have two problems:

1. Only column 'download-count' is updated.
2. I am not able to catch any sql errors.

However, when using the same query in a standalone php file, it works fine. What is wrong, and how can I get hold of the sql errors, or at least the query sent?

$table = $wpdb->prefix . 'woocommerce_downloadable_product_permissions';
$expiry_date = date("Y-m-d", strtotime("+ 30 days"));
$expiry_time = $expiry_date . ' 00:00:10';

$wpdb->query("UPDATE `$table` SET 
    `downloads_remaining` = '1',
    `access_expires` = '$expiry_time',
    `download_count` = (`download_count` + 1)
    WHERE `order_id` = '$post_id'");

---

Here is "standalone php version, which works:

$link = mysqli_connect($servername, $username, $password, $db);
$post_id = '219';

if (!$link) { exit; }

$expiry_date = date("Y-m-d", strtotime("+ 30 days"));
$expiry_time = $expiry_date . ' 00:00:00';
$table = 'hkmw_woocommerce_downloadable_product_permissions';
$query = "UPDATE `$table` SET 
    `downloads_remaining` = '1',
    `access_expires` = '$expiry_time',
    `download_count` = (`download_count` + 1)
    WHERE `order_id` = '$post_id';";

if (!mysqli_query($link,$query)) {
    printf("Errormessage: %s\n", mysqli_error($link));
}
mysqli_close($link);

I am doing an update in from woocommerce admin, edit order details. I have two problems:

1. Only column 'download-count' is updated.
2. I am not able to catch any sql errors.

However, when using the same query in a standalone php file, it works fine. What is wrong, and how can I get hold of the sql errors, or at least the query sent?

$table = $wpdb->prefix . 'woocommerce_downloadable_product_permissions';
$expiry_date = date("Y-m-d", strtotime("+ 30 days"));
$expiry_time = $expiry_date . ' 00:00:10';

$wpdb->query("UPDATE `$table` SET 
    `downloads_remaining` = '1',
    `access_expires` = '$expiry_time',
    `download_count` = (`download_count` + 1)
    WHERE `order_id` = '$post_id'");

---

Here is "standalone php version, which works:

$link = mysqli_connect($servername, $username, $password, $db);
$post_id = '219';

if (!$link) { exit; }

$expiry_date = date("Y-m-d", strtotime("+ 30 days"));
$expiry_time = $expiry_date . ' 00:00:00';
$table = 'hkmw_woocommerce_downloadable_product_permissions';
$query = "UPDATE `$table` SET 
    `downloads_remaining` = '1',
    `access_expires` = '$expiry_time',
    `download_count` = (`download_count` + 1)
    WHERE `order_id` = '$post_id';";

if (!mysqli_query($link,$query)) {
    printf("Errormessage: %s\n", mysqli_error($link));
}
mysqli_close($link);

• plugins
• wp-admin
• wpdb
• sql

Share Improve this question edited Jan 30, 2017 at 19:49 hal asked Jan 30, 2017 at 14:25 halhal 3511 gold badge11 silver badge99 bronze badges 5

• I don't see how this query can work in any situation. this seems like an invalid php code. – Mark Kaplun Commented Jan 30, 2017 at 15:39
• @MarkKaplun, Really? It works in a standalone php file? Why wouldn't it work? – hal Commented Jan 30, 2017 at 17:13
• hmmm, maybe it is the way you formatted the code.... but what is this "standalone" you are talking about, there shoiuld be zero difference, unless the code is different. My guess is that with loging on you will spot the problem in no time – Mark Kaplun Commented Jan 30, 2017 at 18:08
• I inserted the php-file in the main post above. Same query, but without any $wpdb->query wrapper. – hal Commented Jan 30, 2017 at 19:52
• therefor it is not the same code.... you should probably install the query monitor plugin and see what query is actually being sent. In any case $wpdb has an update method you might want to use to improve the readability – Mark Kaplun Commented Jan 31, 2017 at 4:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

I had to use two separate queries. The $wpdb->update syntax cant deal with calculating based on the existing value. But for the other two fields it works. So like this:

$table = $wpdb->prefix . 'woocommerce_downloadable_product_permissions';

$expiry_date = date("Y-m-d", strtotime("+ 30 days"));
$expiry_time = $expiry_date . ' 00:00:00';

$wpdb->query("UPDATE $table SET 
    download_count = (download_count + 1)
    WHERE order_id = '$post_id'");

$wpdb->update( $table, array( 'downloads_remaining' => '1', 'access_expires' => $expiry_time), array( 'order_id' => $post_id ), array( '%s', '%s' ), array( '%d' ) );