how to delete completed orders in woocommerce using a my sql query
Woocommerce orders are stored in the post
table, postmeta
table, woocommerce_order_items
, and woocommerce_order_itemmeta
tables. Various parts of an order are stored in different tables. addition to that the order status is managed through the taxonomies
which stores the order status list.
Anyone who is/will be writing this query have to write at least 5-6 queries minimum or
single large join query.
So, my suggestion to you should uninstall WooCoommerce, if you don't have much larger product setup. To do this you can follow this link which will delete the data
How do I delete orders from a cloned WooCommerce site?
You can use this plugin in order to delete in bulk
https://wordpress.org/plugins/woocommerce-store-toolkit
Woocommerce delete download permission to refunded orders, using a query trigger in wc core function
After searching some posts i have modded the query to run in a function, its working and prevent download permission after digital product order become refunded:
function mysite_refunded($order_id) {
$sql = "DELETE
FROM wp_woocommerce_downloadable_product_permissions
WHERE EXISTS (SELECT 1 FROM wp_posts
WHERE wp_posts.ID = wp_woocommerce_downloadable_product_permissions.order_id AND wp_posts.post_status = 'wc-refunded');";
global $wpdb;
$wpdb->get_results($sql);
}
add_action( 'woocommerce_order_status_refunded', 'mysite_refunded', 10, 1);
sql query to bulk remove woocommerce products by publish date
Woocommerce products are located mainly in 2 tables, the wp_posts and wp_postmeta. If you want to delete them, you will have to use a JOIN clause to combine the product rows from these tables.
Suppose you want to delete every product added before the 1st of September of 2018. You can use this query below.
DELETE wp_posts, wp_postmeta FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'product' and wp_posts.post_date < '2018-09-01'
Delete processing orders after 10 days
This function will handle it:
function expire_after_x_days(){
global $wpdb;
// Get current time
$today = date("mdy");
// set time to expire
$time_to_expire = "-10 days";
$expiration_date = date("mdy", strtotime( $today . $time_to_expire));
// Get orders with processing status
$result = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE post_type = 'shop_order' AND post_status = 'wc-processing'");
if( !empty($result)) foreach ($result as $order){
// Get order's time
$order_time = get_the_time('mdy', $order->ID );
// Compare order's time with current time -10 days
if ( $order_time < $expiration_date ){
// Update order status
$orders = array();
$orders['ID'] = $order->ID;
$orders['post_status'] = 'wc-cancelled';
wp_update_post( $orders );
}
}
}
// Use the best HOOK for your case
add_action( 'admin_footer', 'expire_after_x_days' );
// OR simply call the function if you are pasting this in your functions.php
Related Topics
How to Get Multiple Value from Post Variable Using Same Name
Passing PHP Variable in Onclick Function
Passing an Array to a Query Using a Where Clause
How to Remove a Directory That Is Not Empty
Laravel Array to String Conversion
Laravel: Property [Name] Does Not Exist on This Collection Instance
Increase a Value Like = +1 in MySQL and PHP
Convert Image to Base 64 String With Laravel
Laravel Drop Down Multiple Values from Db in Drop Down
Remove/Hide Woocommerce Added to Cart Message But Keep/Display Coupon Applied Message
Getting Date Format M-D-Y H:I:S.U from Milliseconds
Keep Values Selected After Form Submission
How to Send an Email Using PHP
Get Image Type from Base64 Encoded Src String
Reading Numbers as Text Format With Phpexcel
How to Find Out If CSV File Fields Are Tab Delimited or Comma Delimited