Getting a Billing Address from the Magento Orders TableSep 13, 2012 · 1 minute read
I recently had to generate a report listing the details of all orders from a magento store that matched certain parameters.
There was a requirement that the billing address should be included in this, and I wanted to do this all in SQL if possible.
The biggest problem was formatting the address as there is a value in the address table called suffix, which contains the County information, but is not a required value.
As I was adding in line breaks to the select, this meant for all of the orders that did not have the value, then there would be a blank line using a standard CONCAT statement.
What I found was that it is possible to add an IF statement to the CONCAT query, which gives the required result.
This is done like this
SELECT -- Other Values that are needed -- CONCAT( billing.firstname, " ", billing.lastname, "\n", billing.street, "\n", billing.city, if(billing.suffix IS NOT NULL, CONCAT( "\n", billing.suffix), '' ), "\n", billing.postcode) AS "Billing Address" FROM sales_flat_order AS sfo JOIN sales_flat_order_address AS billing ON billing.parent_id = sfo.entity_id AND billing.address_type = 'billing' -- add your WHERE conditions --