Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

SQL query for customer & order info for orders with a given coupon code? 
 
EricO
Member
 
Total Posts:  63
Joined:  2010-04-02
 

Does anyone know of an SQL query that could be used for looking up customer and order information for an order that contains a given coupon code?  Magento’s database model seems fairly complicated, with its EAV model and data spread out across multiple tables.  What we want to retrieve is the date and time an order was placed, customer name, customer ID, and customer email address for orders placed with a given coupon code.

The sales_flat_quote table contains information that I need, but I think the flat tables need to be updated manually in Magento?  I’m not sure if this would contain live data.

The table sales_order_varchar seems to contain entries with coupon codes (with entity_type_id 11, attribute_id 122, and varying value_id and entity_id values).  I imagine there must be a way to link this in an SQL query to get the data we want.  Does anyone know of such a query we could do or a resource that explains how to go about creating such a query for Magento?

 
Magento Community Magento Community
Magento Community
Magento Community
 
tzyganu
Mentor
 
Avatar
Total Posts:  2205
Joined:  2009-11-18
Bucharest, Romania
 

$couponCode 'COUPON_CODE';
$orders Mage::getModel('sales/order')->getCollection()->addAttributeToSelect("*")->addAttributeToFilter(array(array('attribute'=>'coupon_code''eq'=>$couponCode)));
foreach (
$orders as $order){
echo $order->getCreatedAt();//when the order was placed
echo "<br />";
echo 
$order->getCustomerFirstname(). " ".$order->getCustomerLastname()."<br />";
echo 
$order->getCustomerId()."<br />";
echo 
$order->getCustomerEmail()."<br />";
}

Use this instead of a query. It’s not a good idea to work directly on the database if you don’t know what you are doing.

 
Magento Community Magento Community
Magento Community
Magento Community
 
EricO
Member
 
Total Posts:  63
Joined:  2010-04-02
 

Thanks tzyganu - That works well.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top