用php和mysql插入xml

Pretend you have an xml.file with 80 products in a shop.(it will be a feed but i don't want to confuse the question)

The 80 products have many elements like

    <product> 
    <price> millions </price>
    <colour> red </colour>
    <pictures> <pic> picture1_url </pic> <pic> picture2_url </pic> 
 <pic>picture3_url </pic>
 </pictures>
    </product >

Your client wants to use the .xml on a webpage/app where his customers can search the data by price and colour with a search form and view the 'many' pictures that are nested into the main element 'product'.

My question is: Should i save the data in relational table with columns for each element( relational because there are many pictures which i presume will need to be foreign keyed to the id of product.

I have been able to use dom/ simple_xml to echo the products on a page without the need for a database but i have a nagging feeling i should put the data into a db so i query it easier with mysql select statements.

I Even spoke to a dev and saw on wordpress meta tables that url's for the pictures were kept comma seperated in one table. I thought this was very bad practice in db land?

Trouble is it seems very difficult to attach a foreign key to 1 id when i have many other pictures to associate with that id.. ( get last id seems to get confused when foreach / looping the products.

Here is my code but i suppose its useless if i decide to go dom route and dont need to put the data in a dbase.

 $xml = simplexml_load_file('products.xml') or die("can not find it");

        foreach($xml->product as $row){ 


         $price  =   $row->price
        $colour  =$row->colour
        $pictures =  $row->pictures 

        $sql= insert into products table 

}

Here're you go

CREATE TABLE products (id INT NOT NULL PRIMARY KEY auto_increment, colour VARCHAR(25), price DECIMAL)

Note that price is decimal - it's common practice to keep pricing as decimals

CREATE TABLE pictures (id INT NOT NULL PRIMARY KEY auto_increment, picture_url VARCHAR(100))

CREATE TABLE relations (product_id INT, picture_id INT)

Inserting values...

INSERT INTO products VALUES (NULL, 'red', 100500.00)
INSERT INTO pictures VALUES (NULL, 'picture_url1');
INSERT INTO pictures VALUES (NULL, 'picture_url2');
INSERT INTO pictures VALUES (NULL, 'picture_url3');

Adding relation

INSERT INTO relations VALUES (1, 1);
INSERT INTO relations VALUES (1, 2);
INSERT INTO relations VALUES (1, 3);

And finally - getting result

SELECT price, colour, group_concat(pictures.picture_url) as 'pictures' FROM products, relations, pictures WHERE products.id = 1 AND
relations.product_id=products.id AND relations.picture_id=pictures.id

------------------------------------------------------
|price |colour|pictures                              |
------------------------------------------------------
|100500|red   |picture_url1,picture_url2,picture_url3|
------------------------------------------------------

Update. Use SimpleXML for insert all of the pictures

$new_xml = new SimpleXMLElement($xml_file);

foreach ($new_xml->product->pictures->pic as $pic) {
//this is your picture url
   $your_picture_url = $pic;
}