You could do it with a script.
IMPORTANT - Backup your database before using this script
create a file in your favourite text editor with the following code and save it as whatever.php - Upload it to your store root and run it in a web browser
IMPORTANT - Backup your database before using this script
create a file in your favourite text editor with the following code and save it as whatever.php - Upload it to your store root and run it in a web browser
Code: Select all
<?php
set_time_limit(0);
require_once('config.php');
require_once(DIR_SYSTEM . 'startup.php');
// Registry
$registry = new Registry();
// Loader
$loader = new Loader($registry);
$registry->set('load', $loader);
// Config
$config = new Config();
$registry->set('config', $config);
// Database
$db = new DB(DB_DRIVER, DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
$registry->set('db', $db);
// Create array of all customer IDs and array of customers to be deleted
$customers = array();
$customers_delete = array();
$query = $db->query("SELECT `customer_id` FROM `" . DB_PREFIX . "customer`");
foreach($query->rows as $c){
$customers[] = $c['customer_id'];
}
// Create array of customers who have ordered in last 5 years
$customers_5years = array();
$query = $db->query("SELECT DISTINCT `customer_id` FROM `" . DB_PREFIX . "order` WHERE date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)");
foreach($query->rows as $c){
$customers_5years[] = $c['customer_id'];
}
// Work out which customers to delete
$customers_delete = array_diff($customers, $customers_5years);
foreach($customers_delete as $cid){
$query = $db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_activity` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_online` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_search` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = " . $cid);
$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_wishlist` WHERE `customer_id` = " . $cid);
$query = $db->query("UPDATE `" . DB_PREFIX . "order` SET `customer_id` = '0' WHERE `customer_id` = " . $cid);
}
You don't need to request customers and then use array_diff(). One single query should be enough:uksitebuilder wrote: ↑Sun Sep 15, 2019 5:47 pmCode: Select all
foreach($customers_delete as $cid){ $query = $db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_activity` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_online` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_search` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = " . $cid); $query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_wishlist` WHERE `customer_id` = " . $cid); $query = $db->query("UPDATE `" . DB_PREFIX . "order` SET `customer_id` = '0' WHERE `customer_id` = " . $cid); }
$query = $db->query("SELECT c.customer_id FROM `" . DB_PREFIX . "customer` AS c LEFT JOIN `" . DB_PREFIX . "order` AS o ON (c.customer_id = o.order_id AND o.date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)) WHERE o.order_id IS NULL");
You also don't need foreach() clause, because you can do everything in a single run, if this:
= " . $cid);
Is replaced with this:
IN (" . implode(',', array_colum($query->rows, 'customer_id').")");
Here's even a single liner for one of the queries:
$db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` IN (SELECT GROUP_CONCAT(c.customer_id) FROM `" . DB_PREFIX . "customer` AS c LEFT JOIN `" . DB_PREFIX . "order` AS o ON (c.customer_id = o.order_id AND o.date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)) WHERE o.order_id IS NULL)");
Might require to run this before executing the main queries:
$this->db->query("SET SESSION group_concat_max_len = 1000000");
Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com
uksitebuilder code also does not follow the request! It is deleting ALL customers in the past 5 years with no check if the customer had any orders or not.
EDIT: No sorry it is, just such a unnecessarily heavy/messy script I could not fully grasp it, I would definitely go with thekrotek code, much cleaner and easy to understand with less logic so less likely to go wrong.
EDIT: No sorry it is, just such a unnecessarily heavy/messy script I could not fully grasp it, I would definitely go with thekrotek code, much cleaner and easy to understand with less logic so less likely to go wrong.
Regards, WebDesires.
We are a team of developers in the UK - professional and friendly, message us or give us a call anytime and we will be happy to help.
Phone: +44 (0) 121 318 6336 - Web: webdesires.co.uk - Skype: WebDesires
OpenCart Support - OpenCart Web Development - Our OpenCart Plugins
Active Member
How exactly will c.customer_id = o.order_id return anything ?thekrotek wrote: ↑Sun Sep 15, 2019 6:37 pm
You don't need to request customers and then use array_diff(). One single query should be enough:
$query = $db->query("SELECT c.customer_id FROM `" . DB_PREFIX . "customer` AS c LEFT JOIN `" . DB_PREFIX . "order` AS o ON (c.customer_id = o.order_id AND o.date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)) WHERE o.order_id IS NULL");
$db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` IN (SELECT GROUP_CONCAT(c.customer_id) FROM `" . DB_PREFIX . "customer` AS c LEFT JOIN `" . DB_PREFIX . "order` AS o ON (c.customer_id = o.order_id AND o.date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)) WHERE o.order_id IS NULL)");
Apparently, it's a typo and o.customer_id should be used.uksitebuilder wrote: ↑Thu Sep 19, 2019 2:04 pmHow exactly will c.customer_id = o.order_id return anything ?
Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com
Who is online
Users browsing this forum: No registered users and 228 guests