WooCommerce reviews + tutorials

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;
}

Published by Justin Stern

Justin is one of our co-founders, and is our resident overengineer. He likes to write developer tutorials and make black magic happen in our plugins. He thinks that writing code is a lot easier than writing words.

34 Comments

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

    • 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?

      • 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

      • 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

        • 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

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

    • 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.

  3. 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

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

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

        • 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”

          • 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 :-/

          • 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

          • 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!

  4. 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?

  5. 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)

  6. 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!

    • 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 🙂

      • 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!

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

    • 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

  8. 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!

    • 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;
      }

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

  10. WooCommerce 2.3 finally adds support for this in core.

    If you are using this version, you can call wc_get_product_id_by_sku( $sku ) to get the required information.

  11. Is there a WooCommerce 2.3? Last version I am aware of is 2.2.10.

    Is there a way to call search for a product by SKU in the admin Orders list page?

    • Hey Katie, WooCommerce 2.3 is in beta testing now. As for your second question, I don’t believe you can do this with WooCommerce core but I’ll add it to our idea board for a future tutorial.

  12. Hello Beka, thank you very much for your reply! 🙂

  13. Why is this still not supported out of the box in the core by now?

  14. Thanx man.
    This is really helpfull!

Comments are closed.

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match