Vote up!
Vote down!

How do I create a MySQL query to pull node id using the sku?

I have an image hosting site outside Drupal and I'm wanting to put a link next to each image that points to the correct node, so I'm trying to figure out the mysql query that I need to do this. I know the SKU for each image. My understanding is that I need the commerce_product and node tables in a join query. Are these the only two tables I need?

known values: SKU
needed value: node id

A plus would be to pull the alias instead of the node, but having the node would be enough. I can then create the html needed to make the link: <a href="http://example.com/products/$nodeid">$sku</a>

I also posted this question here: http://www.drupalcommerce.org/questions/3176/how-get-node-id-product-dis...

Asked by: ndavidg
on December 12, 2012

1 Answer

Vote up!
Vote down!

Products and product displays (nodes) are connected via the Product reference field on the displays content type. The table for that field is called 'field_data_field_products' and contains the node id (called entity_id in the table) and product_id.

If you don't have access to the product_id then do a query first to get that:
$product_id = SELECT product_id
FROM commerce_product
WHERE sku=$your_sku;

Then query for the node id:
SELECT entity_id
FROM field_data_field_products
WHERE field_products_product_id = $product_id

Depending on your situation if you need to do a join you can do an inner join:
SELECT field_data_field_products.entity_id
FROM field_data_field_products
INNER JOIN commerce_product
ON field_data_field_products.field_products_product_id = commerce_product.product_id
WHERE sku=$your_sku;

Answer by: michfuer
Posted: May 13, 2013