SQL nested query in stored in an array?

Question

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.

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.

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.

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!

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

Answer 1

One of the basic tenets of relational databases is that columns don't hold compound data, which means no arrays. It's also called "first normal form" (1NF). If you don't want repeated order info within the product query, you can run two queries rather than one, one that gets the orders and the second that gets the products.

Since the result of the query is being processed anyway, you can also take care of aggregation within PHP once you get the result. You can aggregate first:

$orders=array();
while ($row = $result->fetch()) {
    if (! isset($orders[$row['order_id']])) {
        $orders[$row['order_id']] = new Order($row);
    } else {
        $orders[$row['order_id']]->addProducts($row);
    }
}

(assuming a suitable Order class), or aggregate in-line:

class OrderList {
    ...
    function printXML($result) {
       $currOrderID = null;
       echo '<orders>'
       while ($row = $result->fetch()) {
           if ($currOrderID != $row['order_id']) {
               $this->closeOrder();
               $this->openOrder($row);
           }
           $this->printProduct($row);
       }
       $this->closeOrder();
       echo '</orders>';
    }

    function openOrder($row) {
        echo "<order id='$row[order_id]'><total>$row[order_total]</total>";
        $this->printCustomer($row);
        echo '<products>';
    }

    function printProduct($row) {
        echo "<product><title>$row[title]</title><price>$row[price]</price></product>\n";
    }

    function closeOrder() {
        echo '</products></order>';
    }

    function printCustomer($row) {
        echo <<EOS;
          <customer>
            <email>$row[primary_email]</email>
            <first_name>$row[delivery_first_name]</first_name>
            ...
EOS;
        $this->printAddress($row);
        echo '</customer>';
    }

    function printAddress($row) {
        echo <<EOS;
          <address>
            <street line="1">$row[delivery_street1]</street>
            <street line="2">$row[delivery_street2]</street>
            ...
          </address>
EOS;
    }
}

Of course, the specifics of the above is not a good design for production code. The print* methods (and even order printing, via the openOrder and closeOrder) probably deserve to be OrderWriter, CustomerWriter, AddressWriter and ProductWriter classes. You might also want to use XMLWriter rather than echoing.

Answer 2

You could create a User Defined Function that would accept order id and return a list of ordered products in a string. Then, instead of joining with your uc_order_products table, you simply call the UDF. Hope this helps.

Answer 3

This is a procedural task and would require handling the result set using PHP or some other language(may be a db procedure that returns array for you).