MySQL Forums
Forum List  »  PHP

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>';

Options: ReplyQuote


Subject
Written By
Posted
collapsing subarray for responsexml output
June 11, 2009 12:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.