I want create search field which can get order number from product name or product description in magento 1.9 ,

I've confuse since order number is placed in Mage::getModel('sales/order') and product name in Mage::getModel('catalog/product')

I don't know how to join them.

1 Answer 1


To fetch the order number from the Database by providing product name, you will need to use the following tables. sales_flat_order - You need the field "increment_id" which represents the order number sales_flat_order_item - You need the field "name" which contains the product name.

SELECT sfo.increment_id 
FROM sales_flat_order AS sfo 
INNER JOIN sales_flat_order_item AS sfoi ON sfo.entity_id = sfoi.order_id 
WHERE sfoi.name = 'Your product name';

This will return all of the order numbers that contain this product.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.