I am trying to do a reverse lookup to get the Permalink to a product from its SKU. The output of which will be used to redirect old URLS to new ones.
I am having trouble understanding the relationships between the tables used in woocommerce. The products seem to be stored in wp_posts, and the SKU in wp_wc_product_meta_lookup however when creating a join in the database tables this does not bring up the expected data :
SELECT pml.sku,pml.product_id,wpp.guid FROM wp_wc_product_meta_lookup pml LEFT JOIN wp_posts wpp ON wpp.ID=pml.product_id WHERE pml.stock_status='instock'
In addition to this the guid in wp_posts is not the same as the actual product URL – the categories are different.
Thanks,
Rick
2
Answers
You are correct. The product post itself is stored in
wp_posts
, howeverwp_wc_product_meta_lookup
is actually a reference table that is used to speed up certain queries on the site. The actual SKU is stored in thewp_postmeta
table under themeta_key
of_sku
. Here’s an updated query that gets you Product ID and SKU:Furthermore, you really should not us the GUID column for URLs. The GUID that WordPress uses is a fake unique identifier that just shows the URL of the page on the environment it was created on. You are much better off creating a script that will generate the permalink for you via
get_permalink($post_id)
.You could use the built-in WooCommerce function for this – wc_get_product_id_by_sku.
Example: