Get no order from product name or product description

Question

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.

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.