Databases
StackOverflow2013
Postsdbo
POSQL nested query in stored in an array?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL nested query in stored in an array?
    text
    copied!<p>I have an sql query pulling a few results from different tables, one of the tables - "orders" - contains customer details with an order_id as a PK, another table - order_products - contains each product ordered, with reference to the order_id and another column as PK.</p> <p>Basically, I need to know how to query the database in such a way that each order_id is only displayed once, with data from the order_products table added within the same row, even if customers have ordered more than one product in that particular order. </p> <p>Currently customer details are being repeated within the results if they have ordered multiple different products - I know why this is happening (see query) but don't know how to get the results output in the required format. My best idea would be a nested query, in which the results from the order_products are loaded into an array or something but I don't even know if that would be possible.</p> <p>I am using MySQL and PHP with the results to then be output in an XML document. If you need any more info please ask!</p> <pre><code>SELECT uc_orders.order_id, uc_orders.order_total, uc_orders.primary_email, uc_orders.delivery_first_name, uc_orders.delivery_last_name, uc_orders.delivery_street1, uc_orders.delivery_street2, uc_orders.delivery_city, uc_orders.delivery_zone, uc_orders.delivery_postal_code, uc_zones.zone_name, uc_order_products.title, uc_order_products.price FROM uc_orders LEFT JOIN uc_zones ON uc_orders.delivery_zone = uc_zones.zone_id LEFT JOIN uc_order_products ON uc_orders.order_id = uc_order_products.order_id ORDER BY order_id </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload