php - Design pattern for creating an XML document from a database query that has a 1:many relationship -

All,

Sorry in advance for the obscure title.

Here's what I'm trying to do ...

I'm working on an application for which to get a list of items, ask a database . That list of items will be formatted in an XML document that will return to the querying front-end.

To make it simple, assume that the items in the books are in the books, each book has the following characteristics: ISBN, title, author, value is also between reviews of 0 and n in each book

< P> Then, the data is stored in 2 tables: "book_info" and "book_reviews" have a line in each book's "Book_Info", and between "0 bookmarks" and "between bookmarks"

I want XML that comes back to look like this:

   & Lt; / Books & gt;  

As you can see, there is a review of the first book, the other has no third to the third and third.

My question is: What is the most efficient way to create a structure like this?

For example, I can get a list of books like this:

  book, book with information, IESBN, title, value of the author, where title like "% To learn%"  

Again, through the result set, I can build an XML document but how do I get reviews?

A clear solution is this: With each interaction, get ISBN for the book in that row, then ask the database again:

  Select Review from Book_review While book_review.isbn = [current ISBN]  

While setting this "sub" -set set, I can add child "review" nodes to the current book node.

With this problem, if there are 100 books in the initial result set, then I would have run 100 SQL commands to see all the reviews.

Then, one second

Select all the books that match the search with a question

Create XML documents

" Execute a query against book_reviews "Recover all reviews for all books in step 1 (i.e., select" Review from book review where in IBN ([list of isbn]] ")

Repeat through that result set; For each review, the XML node for the book should also be met, and adding a child to it for the node

This means there are only two SQL queries, but searching from the XML document.

Finally, both of them seem a little uncomfortable. Since it seems like this will be a common problem, I hope that there is a good, elegant solution for this.

What is this for, I am using SQL Server and PHP and for executing and collecting queries

Cheers, Matt

If you are using Microsoft SQL Server, you can generate XML for XML through the XML Clause. If you are trying to get the exact formatting, then it can be a bit tricky, but it is quite versatile.

If you really need to make it yourself, then use only one selection Book Reviews. I'm a little surprised if someone has not written any XML generator with the functionality set out as a result of PHP.

An example query:

  SELECT BI ISBN, BITTAL, BI. Author, BIPIC, BR From Book_Info BE - This is generally considered to be bad form for the name of the table containing "Info" or "Data". Simply join the "books" better next to Book_Reviews BR BR.isbn = BR.isbn = BR.isbn BR Where the BI.tisbn, like BI.title, "learn% to" order by BI.isbn