Jump to content

How to export order's full address in SQL manager?


Wilfredcy

Recommended Posts

I create a SQL query in the SQL manager for export order details, but I found out that the order address is not complete for some order. (see attachment 1).

Then I realize if the user inserts their address with a comma, the address is going to be not complete when exporting.

example:
"123 Taman Good Jalan Hill 12345 Tawau" will result in "123 Taman Good Jalan Hill 12345 Tawau" in the csv file.

But if "123 Taman, Good Jalan Hill, 12345, Tawau", it will result in "123 Taman" in the CSV file.

It is because of the comma.

How can I fix that?

SELECT 

d.id_order, 
o.reference,

o.total_discounts_tax_incl,
o.total_shipping_tax_incl,
os.name AS state, 
d.product_name, 
d.product_reference, 
d.product_price,
d.product_price - d.reduction_amount AS final_price,
d.product_quantity, 
o.payment, 
o.date_upd,
g.email,
g.birthday,
address.phone,
address.phone_mobile,
CONCAT_WS(' ', g.firstname, g.lastname) AS customer, 
CONCAT_WS(' ',address.`address1`,address.`address2`, address.`postcode`, address.`city`, statelocation.`name`) AS `address`,
carrier.name,
sp.price




    FROM ps_order_detail d
    LEFT JOIN ps_orders o ON (d.id_order= o.id_order)
    LEFT JOIN ps_customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN ps_group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'club%'
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)


LEFT JOIN `ps_address` address ON (o.id_address_delivery = address.id_address)
LEFT JOIN ps_carrier carrier ON (o.id_carrier = carrier.id_carrier  )
LEFT JOIN ps_state statelocation ON (address.id_state = statelocation.id_state)
LEFT JOIN ps_product oproduct ON (d.product_id= oproduct.id_product  )
LEFT JOIN ps_message me ON (o.id_order = me.id_order)
LEFT JOIN ps_product_shop sp ON (oproduct.id_product = sp.id_product)

    WHERE os.id_lang = 1
 ORDER BY d.`id_order` DESC 
                              

 

1.JPG

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...