Useful WordPress Code

uwpc.md

Useful SQL queries

Show how much autoload data is being used

SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
  UNION SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
  UNION (SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10);

Delete all posts from a certain post type including meta and term relationships (change CUSTOM_POST_TYPE)

DELETE p,tr,pm FROM wp_posts p
  LEFT JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
  LEFT JOIN wp_postmeta pm ON (p.ID = pm.post_id)
WHERE p.post_type = 'CUSTOM_POST_TYPE';

Get an overview of WooCommerce orders

SELECT 
  p.ID as order_id, 
  p.post_date as order_date, 
  p.post_status as status,
  m4.meta_value AS 'order_total',
  m1.meta_value AS 'firstname', 
  m2.meta_value AS 'lastname',
  m3.meta_value AS 'email',
  m5.meta_value AS 'wordpress_user_id'
FROM wp_posts p
  LEFT JOIN wp_postmeta m1 ON p.ID = m1.post_id AND m1.meta_key = '_billing_first_name'
  LEFT JOIN wp_postmeta m2 ON p.ID = m2.post_id AND m2.meta_key = '_billing_last_name'
  LEFT JOIN wp_postmeta m3 ON p.ID = m3.post_id AND m3.meta_key = '_billing_email'
  LEFT JOIN wp_postmeta m4 ON p.ID = m4.post_id AND m4.meta_key = '_order_total'
  LEFT JOIN wp_postmeta m5 ON p.ID = m5.post_id AND m5.meta_key = '_customer_user'
WHERE p.post_type = 'shop_order'
ORDER BY p.post_date DESC;

Delete non-admin users (backing up to dev from staging|live) - change to SELECT to test first

DELETE wu FROM `wp_users` wu 
  INNER JOIN wp_usermeta ON wu.ID = wp_usermeta.user_id 
  WHERE meta_key = 'wp_capabilities' 
  AND meta_value NOT LIKE '%administrator%';

Good howto posts

Modify admin posts

https://www.smashingmagazine.com/2013/12/modifying-admin-post-lists-in-wordpress/