Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Woocommerce – Get permalink from SKU

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

Tags:

2

Answers


  1. 0

    You are correct. The product post itself is stored in wp_posts, however wp_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 the wp_postmeta table under the meta_key of _sku. Here’s an updated query that gets you Product ID and SKU:

    SELECT wp_postmeta.meta_value as `sku`,
       wp_posts.ID as `product_id`
FROM wp_posts
INNER JOIN wp_postmeta 
       ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type LIKE 'product%'
AND wp_postmeta.meta_key = '_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).

    Login or Signup to reply.
  2. 0

    You could use the built-in WooCommerce function for this – wc_get_product_id_by_sku.

    Example:

    <?php
echo get_permalink( wc_get_product_id_by_sku( 'SKU' ) );
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search