Blog

How to Find Product by SKU in WooCommerce

Searching the Admin

You can search for a product by SKU in the admin Products list page by starting the search string with the special “SKU:” indicator. So for instance, if the product you are searching for has a SKU of ’4′, your search string would look like: “SKU:4″. Here’s an example:

Programmatically

Here’s a little snippet of code which you can use in your WooCommerce plugin development to get a particular product by its SKU. Enjoy:


function get_product_by_sku( $sku ) {

  global $wpdb;



  $product_id = $wpdb->get_var( $wpdb->prepare( "SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $sku ) );

  if ( $product_id ) return new WC_Product( $product_id );

  return null;

}

28 Comments

  • 1974anon 4 months ago Reply

    Can this be added to some woocommerce code (without a plugin)? If so which file?

    • justin 4 months ago Reply

      Hey, this is really meant to be used when developing a plugin where you need to look up a product by SKU. Are you looking for a way for customers to be able to use the site search box to find a product by SKU?

      • Vipul 3 months ago Reply

        I am looking for a way for customers to be able to use the site search box to find a product by SKU. Can you please help. Thanks in advance

      • JOsh 3 months ago Reply

        I am looking for a way for customers to be able to use the site search box to find a product by SKU. Can you please help. Thanks in advance

        • justin 3 months ago Reply

          Yeah, that would be a handy feature. Not sure how to do it, but it’s something I’m going to try and look into at some point

  • Opal 3 months ago Reply

    Hi, Thank you for your info. Which file should I add these code in? Please let me know. Thanks.

    • justin 3 months ago Reply

      Hey Opal, this code snippet doesn’t do anything on its own; it’s really just meant to be a little timesaver for WooCommerce developers.

  • Wes 3 months ago Reply

    I found a plugin that claims to allow customers to search products using SKU… I haven’t tried it yet.

    http://ignitewoo.com/woocommerce-extensions-plugins/woocommerce-live-search-title-description-excerpt-category-tag-keywork-sku/

    I am actually looking for a plugin that allows site manager to search by SKU

    • justin 3 months ago Reply

      What do you mean by allowing a site manager to search by SKU? Search where?

      • Wes 3 months ago Reply

        In the product backend… wordpress admin area. I have a client who wants to search for products by SKU.

        • justin 3 months ago Reply

          Yeah, it’s a little weird, but you actually use something like the following when searching in the admin: “SKU:406″ assuming here that the product SKU you want is, or contains “406″

          • Wes 3 months ago

            Justin, thank you! That is very helpful. The only issue is that it seems to be a little quirky. Some SKU’s that I know exist, aren’t pulling up with the search :-/

          • justin 3 months ago

            Hmm, weird, it seemed to work fine for me in my couple of test cases, but who knows, perhaps there’s some bug with it

          • TB 26 weeks ago

            This little tidbit of information is all I’ve been searching for for EVER!
            I needed to be able to pull up a product in the product admin area by sku! and this works. No plugin needed. Thank You!

            This needs to be made public, lol!

          • Beka Rice 25 weeks ago

            Happy to help!

  • John Keenan 3 months ago Reply

    Hi Justin,

    Nice information here, thanks! Is it possible to also search on other fields in the woocommerce product admin or does this only work for SKU? I would love to be able to search on more fields (like Price) with this method?

  • Matthew Lawson 2 months ago Reply

    Hi Justin,

    I just found this post, I have made a plugin that searches by Stock Keeping Unit in woocommerce that your readers might find helpful.

    http://www.mattyl.co.uk/2012/12/11/woocommerce-plugin-to-search-products-by-sku/

    The query I use is similar but does a fuzzier search, returns multiple results, handles variations and hidden products / variations. Feel free to check it out. (Its also free :P)

    • Justin Stern 2 months ago Reply

      Thanks for the tip!

  • Andy Schneider 2 months ago Reply

    Hi Justin,

    Just stumbled on this, it’s great. I’m trying to modify the function to create an ‘Add Sample to Cart’ button that will automatically add a separate product to the cart. The sample product shares the same SKU, with the added suffix ‘_sample’ (i.e. product123 vs. product123_sample). In order to do that, I’d like to automatically search by the product SKU (product123_sample) and return the product ID for that sample, so I can modify the add to cart URL using that information.

    I know this snippet is originally intended to be used as the basis for a search plugin, but would it be possible to run the function when the ‘Add Sample to Cart’ button is clicked that would search the SKUs and return the product ID for the matching sample product, then pass that product ID into add to cart URL?

    Thanks!

    • Justin Stern 2 months ago Reply

      Hey Andy, seems like you’d want to create your main product, create your sample products with the specified SKU, and then add them both to a grouped product. I think that might do the trick? Otherwise you’re talking a decent amount of custom code :)

      • Andy Schneider 2 months ago Reply

        Hi Justin, thanks for the tip. Just checked out the grouped products option — looks like that’s the way to go. I was reluctant to add a variable product because I have to ship the regular and sample products differently, but I didn’t realize there was even a grouped option to link separate products together. Thanks so much for the help!

        • Justin Stern 2 months ago Reply

          You got it!

  • sailor656 2 months ago Reply

    woocommer products page doeesnt shop up “sku” how can do fix. i don understand explain.
    sorry im newbie

    • Justin Stern 2 months ago Reply

      hey, sorry if I misunderstand, but you’re saying your product page isn’t showing the product sku? This is probably a theme issue as WC will show the sku by default

  • lance 19 weeks ago Reply

    I’m trying to query the database — and return a single product by it’s sku. Is that possible? Also, basically just want to add this to a single template page I’ve created in wordpress — not necessarily editing the template pages of woocommerce.

    Thanks!

    • Max Rice 17 weeks ago Reply

      Yep, here’s some sample code that should get you started :)


      /**
      * Returns the product object for a given SKU
      *
      * @since 1.0
      * @param string $sku
      * @return object WC_Product object
      */
      function wc_get_product_by_sku( $sku ) {
      global $wpdb;

      if ( ! $sku ) {
      return null;
      }

      // get the product ID for the given SKU
      $product_id = $wpdb->get_col( $wpdb->prepare("
      SELECT posts.id FROM {$wpdb->posts} as posts
      INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id

      WHERE posts.post_status = 'publish'
      AND ( posts.post_type = 'product' OR posts.post_type = 'product_variation' )
      AND postmeta.meta_key = '_sku'
      AND postmeta.meta_value = '%s'
      ", $sku ) );

      // return product ID which should be first array element
      return ( isset( $product_id[0] ) ) ? get_product( $product_id[0] ) : null;
      }

  • Santosh 13 weeks ago Reply

    I can’t find where is this function used.means in which file i put this code.

Submit a Comment