Saturday, May 21st, 2022

TablePlus for SQL Query in WordPress

Having a nice GUI to do direct database queries can be really helpful when working with a complex WordPress site. Tableplus is the best I’ve found for Mac.

Using TablePlus with WP Engine

It should be pretty easy to connect to a local database and most remote databases, but there are a few extra steps if the database is hosted with WP Engine.

WP Engine has remote database access instructions here. Make sure your IP address is whitelisted, include the certificate, and get the ports properly. Here is a screenshot of what the settings should look like.

useful questions

I work with WooCommerce a lot these days. Here are the types of questions I run into regularly.

Receive ActionScheduler progress during the subscription renewal period:


select status,
	count(*) from wp_actionscheduler_actions where 
	status in ('failed','complete','pending')
	and scheduled_date_gmt > '2021-01-01 00:00:00' 
	and scheduled_date_gmt 

Update order statuses in bulk:


UPDATE wp_posts
SET post_status="wc-pending"
WHERE ID IN (101,102,103);

Get some order/post meta for a specific set of IDs:


SELECT
	orders.ID,
	orders.post_date as order_date,
	orders.post_status as status,
	shipping.meta_value as shipping_date
FROM
	wp_posts orders
JOIN
	wp_postmeta shipping ON orders.ID=shipping.post_id
WHERE
	orders.post_status="wc-processing" AND
	shipping.meta_key = 'shipping_date' AND
	orders.ID IN ( 100, 101, 102 )
);

Even for small things, the way to fetch a piece of data can be from using TablePlus to quickly view the order entry in WordPress.

Source link