PHP / MySQL处理XML数组，其属性每行更改

Question

I am working with the eBay Trading API and part of one of the API calls comes back with an ItemSpecifics array of XML elements, a simple example is below:

<ItemSpecifics>
<NameValueList>
    <Name>Brand</Name>
    <Value>My1stWish</Value>
    <Source>ItemSpecific</Source>
</NameValueList>
</ItemSpecifics>

So we have the attributes Name and Value being returned.

Not every item comes back with the same set of Name attributes, some eBay listings may have none, some may have 20 and those 20 completely differ from the next listing with have 15 different ones.

So my question is: How would you suggest handling these attribute values, so that they can be stored into a MySQL database table and then be able to be pulled back out from that table, with the values that have been set with the right Name as the header and corresponding Value?

Should I just dump these as serialised data into the table for a column called 'ItemSpecifics'? But then I'm not sure how I get them back out again so that I can create a flat file with the all options as headings in say a CSV file.

Two examples below:

Listing 1 returns:

<ItemSpecifics>
<NameValueList>
    <Name>Brand</Name>
    <Value>My1stWish</Value>
    <Source>ItemSpecific</Source>
</NameValueList>
<NameValueList>
    <Name>Exact Colour</Name>
    <Value>Black</Value>
    <Source>ItemSpecific</Source>
</NameValueList>
</ItemSpecifics>

Listing 2 returns:

<ItemSpecifics>
<NameValueList>
    <Name>Exact Heel Height</Name>
    <Value>4.1"</Value>
    <Source>ItemSpecific</Source>
</NameValueList>
<NameValueList>
    <Name>Heel Type</Name>
    <Value>Stiletto</Value>
    <Source>ItemSpecific</Source>
</NameValueList>
</ItemSpecifics>

Answer 1

There's a couple of way to do it that I can think of. First, as you mentioned, there's serialization. That's where you would dump your data into an array, run it through serialize and stick it in the same row as your other data.

The second way is probably better, however. That is, you make a table that is relational to your items. Let's say you have a table called items. In it, you make an automatic ID (auto-increment) field called item_id. So you pull your item data out and put it into this table. We'll say that it gets an ID of 20. Now, you have a second table called item_options. Inside this table, you would store your options, row by row. You would also put in the ID of the parent row. So let's say you have 5 sets of options. You would have five rows, each with that item_id of 20. The advantage here is that your data is now fully expanded so you could, for instance, query your options table to find all products that offer the color red. This is the "relational database" part of RDBMS.