I am relatively new to PHP, WordPress, and WooCommerce. But I have 5 years experience in Oracle (SQL and PL/SQL) and also did a lot of JavaScript and some TypeScript.
I am working for a client of mine. He has a WooCommerce-based webshop. I need to update stock_quantity, backorders, and stock_status for products and product variations according to data coming from another HTTP server. Currently, I have a working solution in TS/JS, which runs on a Linode server and is basically retrieving and combining/preparing data to update WooCommerce. But because it utilizes the WooCommerce HTTP API which has batch update limits (100 in a second, all variations have to be updated per parent id also 1 per second), and I am updating about 2500 products and product variations, it takes very long for the process to complete (~10 min).
The process in steps:
- Get stock from 3rd party API, this is a list of SKUs and their stock amount.
- Get all WC products and WC product variations
- Match stock list on WC product and product variation lists by SKU.
- Update products in batches of 100 per second (/products/batch)
- Update product variations per parent product per second (/products/${id}/variations/batch)
I think there is a better approach, I was thinking of:
- PHP procedure, closer to DB, maybe no limit on batch updates? But I am unable to find any WooCommerce PHP API other than https://github.com/woocommerce/wc-api-php, which is a wrapper for the HTTP API, so in the end, would return the same result.
- MySQL Database procedure, in DB, should run way quicker. Difficult to dissect what tables need to be updated exactly with what values. Is the MySQL DB able to do HTTP requests?
What approach would be best concerning performance, security, and keeping updates of PHP, WordPress, and WooCommerce in mind?
2
Answers
Using the WC API to do this is by far the most "reliable" way to accomplish your goal. The API is very stable and doesn’t (usually) break when internals are changed. It is, however, the slowest method, as you’ve noticed.
Your next best option is to run an update using the Woocommerce CRUD methods but, again, not super speedy but faster than using the API.
Updating the DB directly is by far the fastest way but error prone because, for example, while stock status is in wp_postmeta, it’s ALSO a taxonomy and both have to match or you’ll have unintended consequences.
Based on additional comments by @HotFix and answer provided by @VegasGeek, I created a PHP file that can be run on the WordPress server from the command line or a cron job.
The idea:
Let’s assume that 3rd party external API returns a JSON response like this
There a 2 products with SKU and quantity info.
The PHP script requires
wp-load.php
so replace my path with yours. For simplicity, I created this file in the website root so I can run it in the browser too but you can put it somewhere else and change the path. It’s only important that the path towp-load.php
is correct. This line of code will load the WordPress and all plugins and make it available to the script.I am simulating API response with a simple static JSON file, you replace the endpoint with the URL of the real API. Also, you may need to add some authentication to access the external API and your data will probably be different to my example, so you’ll have to change the code a bit. We are using curl to get the data from the API so it should be easy to modify the call.
Then, we iterate over the products, find the product in WooCommerce by SKU, check if the quantity has changed and update if needed.
I tested it with 2 products, only one of them needed an update. The result (log):