Hi everyone,
Don’t know if this is of any help to anyone - or if there something similar out there. I couldn’t find this anywhere.
Just a bit of background - we run a e-com website on wordpress. About 2k products overall, 50k “posts” including meta. We also use Advanced Custom Fields to manage special fields against products - including files.
We use Airtable to actually gather all the data with multiple stakeholders involved in the collection.
The problem:
One of the most annoying things has been updating the technical images of the products to a better, newer version that might be uploaded to AT.
The idea:
Upon a trigger event, update the website’s MySQL db using Zapier.
The (beta) execution:
A. Get you Wordpress MySQL db hooked up to zapier … this took a bit of time to figure out but after couple of quick exchanges with our hosting company all was good.
B. Setting up the Zap - 5min
-
SLACK - Test Trigger - Post “SKU” as message on a private slack channel (instant trigger)
-
AIRTABLE - Find Record in Airtable - Looks up the SKU in right table
-
WORDPRESS - Upload Media to Media Library
-
MYSQL - Custom Query – SELECT * FROM [wp_database].[wp_postmeta] WHERE meta_key = “_sku” AND meta_value = “{SKU}”;
-
MYSQL - SELECT * FROM [wp_database].[wp_postmeta] WHERE post_id = “{post id from step 4}” AND meta_key = “technical_images”;
6a. MYSQL - Update Row --> meta id from STEP 5 // meta value from STEP 3 // Row --> Add search step and refer to step 6b // ID Column --> meta_id
6b .MYSQL = Find Row - Lookup value --> Meta ID from Step 5
The result
The zap takes about 18-20sec to execute. The website’s database is updated :slightly_smiling_face:
Notes
It’s only been tested a few times - but it works like a charm … i’ll work a bit more on the triggering side of things… I’m only learning a bit of SQL currently - so I’m pretty sure you can get them two queries in one where you get data from two tables.
The only negative is that you gotta have a subscription to zapier
