CE Phoenix Cart Forum

I have a page in admin and is making a detailed report.

So essentially i want to get all the order information relating to an order.
Using GROUP_CONCAT or even DISTINCT causes some other issues to get comma seperated values and removing values if it is same.

So i did try various ways to get the resuls but not all the result is shown in this sql.

Any SQL King willing to give some advice?

SELECT o.orders_id, o.customers_id, o.customers_name, o.customers_company, o.customers_street_address, o.customers_street_address_2, o.customers_suburb, o.customers_city, o.customers_postcode, o.customers_state, o.customers_country, o.customers_country_id, o.customers_telephone, o.customers_fax, o.customers_email_address, o.delivery_name, o.delivery_company, o.delivery_street_address, o.delivery_street_address_2, o.delivery_suburb, o.delivery_city, o.delivery_postcode, o.delivery_state, o.delivery_country, o.delivery_country_id, o.delivery_address_format_id, o.billing_name, o.billing_company, o.billing_street_address, o.billing_street_address_2, o.billing_suburb, o.billing_city, o.billing_postcode, o.billing_state, o.billing_country, o.billing_country_id, o.billing_address_format_id, o.payment_method, o.date_purchased, o.orders_status, o.orders_date_finished, o.currency, o.currency_value, o.discount_codes,
op.products_id, op.products_model, op.products_name, op.products_price, op.final_price, op.products_tax, op.products_quantity,
opa.orders_products_id, opa.products_options, opa.products_options_values,
osh.orders_status_history_id, osh.orders_status_id, osh.date_added, osh.adminuser,
ot.orders_total_id, ot.title, ot.text, ot.value, ot.class, ot.sort_order,
ab.entry_customers_vat, ab.entry_company, ab.entry_country_id, c.countries_iso_code_2

FROM orders o
LEFT JOIN orders_products op ON o.orders_id = op.orders_id
LEFT JOIN orders_products_attributes opa ON o.orders_id = opa.orders_id
LEFT JOIN orders_status_history osh ON o.orders_id = osh.orders_id
LEFT JOIN orders_total ot ON o.orders_id = ot.orders_id
LEFT JOIN address_book ab ON o.customers_id = ab.customers_id
LEFT JOIN countries c ON o.customers_country_id = c.countries_id
WHERE o.orders_id = 10

With this query a 144 rows are returned

at some stage i am calling
$orders = [];
foreach ($orders as $order) {

On a first glance you should simplify the query:
address_book can have more than one entry per customer. So it's ambiguous. You have all address data already in the order table. So remove the address_book part.
If you really need the entry_customers_vat from the address_book (which is not a core column) you would need to use the orders billing address data to ensure the correct address_book entry.
countries is also unneeded. You have the country data already in the orders table.