collapsing subarray for responsexml output
Posted by:
josh good
Date: June 11, 2009 12:27PM
Trying to output this:
orderID | date | customerName | bookTitle
------------------------------------------------------------
2201678 | 6-5-09 | Jack Ryan | Economist, Harlow
6558037 | 6-5-09 | John Smith | New Yorker, PCWeekly
7363840 | 6-5-09 | Alice Brown | MaximumPC
from "6-5-09" date query (bookTitle array collapsed into a coma-separated items). But instead, I've been getting this:
orderID | date | customerName | bookTitle
------------------------------------------------------------
2201678 | 6-5-09 | Jack Ryan | Economist
2201678 | 6-5-09 | Jack Ryan | Harlow
6558037 | 6-5-09 | John Smith | New Yorker
6558037 | 6-5-09 | John Smith | PCWeekly
7363840 | 6-5-09 | Alice Brown | MaximumPC
I think the problem is the composite entity the 4 tables:
1. customer (customerID*, customerName, customerPhones)
2. order (orderID*, customerID, date), the customerID is foreign key
3. orderLineItem (orderID*, bookID*), which is a composite entity to mitigate M2M relationship between orderID and bookID
4. book (bookID*, bookTitle)
Should I create temporary table, or nested subquery, or what option do I have? Here's snippet of my code:
1. $order_sql = "SELECT order.orderID, order.date, customer.customerName, book.bookTitle
2. FROM customer, order, orderLineItem, book
3. WHERE customer.customerID = order.customerID
4. AND order.orderID = orderLineItem.orderID
5. AND orderLineItem.bookID = act.bookID
6. AND order.date = '6-5-09'";
7. $order_result = mysql_query ($order_sql, $conn) or die(mysql_error());
8. echo '<?xml version="1.0" encoding="ISO-8859-1"?> <TodaysOrder>';
10. while($row = mysql_fetch_array($order_result)) {
11. echo "<order_id>" . $row['order_id'] . "</order_id>";
12. echo "<date>" . $row['date'] . "</date>";
13. echo "<customerName>" . $row['customerName'] . "</customerName>";
14. echo "<bookTitle>" . $row['bookTitle'] . "</bookTitle>";}
15. echo '</TodaysOrder>';