Cleanup WordPress database

Handy cleanup SQL for WordPress database.

	— Delete orphan postmeta
	DELETE pm
	FROM wp_postmeta pm
	LEFT JOIN wp_posts p ON pm.post_id = p.ID
	WHERE p.ID IS NULL
	;
	— Delete orphan attachment
	DELETE p1
	FROM wp_posts p1
	LEFT JOIN wp_posts p2 ON p1.post_parent = p2.ID
	WHERE p1.post_parent > 0 AND p1.post_type = 'attachment' AND p2.ID IS NULL
	;

view raw

delete-orphan.sql

hosted with ❤ by GitHub

SQL to get WooCommerce product categories (terms)

For an import script I needed to get WooCommerce product categories so I ended up looking the generated query in /var/log/mysql/query.log for get_the_terms($postID, ‘taxonomy_name’)

I ended up using the following SQL query to get product categories:

SELECT t.*, tt.*
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('product_cat') AND tr.object_id IN (11448)

Getting ‘doesn’t have a default value’ from MySQL (MariaDB)?

Solution for me from ideas at https://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values and https://www.farbeyondcode.com/Solution-for-MariaDB-Field–xxx–doesn-t-have-a-default-value-5-2720.html was to remove ‘STRICT_TRANS_TABLES’ from my.cnf.

So I ended up with the following in /usr/local/mysql/my.cnf:
[mysqlnd]
sql_mode=NO_ENGINE_SUBSTITUTION

REPLACE instead of SELECT and then INSERT or UPDATE

It’s nice that MySQL has the replace query so instead SELECTing to check if the data exists in the DB and than INSERTing or UPDATEing I just need to write one query.

So instead this:

$q = "SELECT id FROM table WHERE name = 'value' AND name2 = 'value2' LIMIT 1;";
$id = $database->get_one($q);
if ($id > 0) {
    $q = "UPDATE table SET name = 'value', name2 = 'value2' WHERE id = $id LIMIT 1;";
}
else {
    $q = "INSERT INTO table (name, name2) VALUES ('value', 'value2');";
}
$database->query($q);

I just query once

$q = "REPLACE INTO table (name, name2) VALUES ('value', 'value2');";
$database->query($q);

More info at
http://blogs.coldbuffer.com/inserting-or-updating-records-using-mysql-replace
https://dev.mysql.com/doc/refman/5.0/en/replace.html