Website Cleanup – Delete Drupal Users who have no ubercart orders 

 June 24, 2014

By  NickLitten

Migrating Cousin Rob’s www.DRUMSTHEWORD.com website from his 6 year old Drupal6 website to a new (hopefully sexier) WordPress4 website has meant a lot of data cleansing before golive. Before we get any comments about Drupal vs. WordPress, or a CMS flamewar, the new website was chosen to be on WordPress because of WOOCOMMERCE and a neat WordPress Theme that had already been selected.

So, I’m going to leave a little blog about any particular problems I found, or cool tricks I found, during data migration.

The old drupal website had over 48,000 users defined… many of them spam… many of them dead… many redundant or no longer needed.

So how do we cleanup users?

Using the PHP panel I used this SQL script to select the users that have been registered but who have never ordered anything using Drupal6 Ubercart:

select count(*) from users where mail not in 
(select primary_email from uc_orders) and
 uid <> '0' and
 mail NOT LIKE "%drumstheword.com%" and
 mail NOT LIKE "%projex%"

note: do not touch uid=0 because this is the ANONYMOUS user and do not touch any *@drumstheword.com or *@projex.com users

This will give you a count value showing how many users are selected and if your ready to delete then just use:

delete from users where mail not in
(select primary_email from uc_orders) and
 uid <> '0' and
 mail NOT LIKE "%drumstheword.com%" and
 mail NOT LIKE "%projex%"



IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

I’m always looking for awesome input, feedback and critique!