xml insert with php and mysql

Question

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 

}

My prev comment that I've deleted accidentally: "Classic solution: 1 table for products, 1 table for images, 1 table for product_id -> image_id relations. Avoid XML and comma separated data in relational databases." You should give your products ids before you insert products and images. I guess, you must have any unique value for it inside your real list of products. If you don't have any, you can generate it manually for each product and give this value to your pictures. — user1597430, Oct 17 '18 at 16:20
I have ids for the products but i was thinking of just incrementing each product to give a fresh id. Then should i insert the products and in another insert statement insert the pictures to that id... this is the bit i get confused. i.e. what would a insert statement like this look like and how would i foreach each picture to the correct id of product? — DonQuery, Oct 17 '18 at 16:26
Auto increment value is possible too, please refer to https://stackoverflow.com/a/5178713/1597430 — user1597430, Oct 17 '18 at 16:27
thx for the link.when you say 'is possible' i dont want to make it harder then you advise. I understand multi queries to a point.. i dont understand the link between the many pictures and it knowing which product id to latch on to — DonQuery, Oct 17 '18 at 16:32
LAST_INSERT_ID() knows which id was last, so right after the product insert it will return you right product_id which you can use instead of "raw" numeric value. — user1597430, Oct 17 '18 at 16:53
Any chance of an example insert statement in php so i can see how products and pictures are linked together in 3 tables ? — DonQuery, Oct 17 '18 at 16:58

Anton · Answer 1 · 2018-10-18T13:42:03.917

0

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

edited Oct 18 '18 at 13:42

answered Oct 18 '18 at 10:18

Anton

919
7
22

thanks so much for your time on this... i was hoping for php version of insert. Manually inserted is the easy part i find.... dynamic is another game. ive managed to get everything into tables with php ..the trouble is i cant get the code to loop through each picture node to insert many pictures to each id. i used last_insert_id() to link the pictures to the products id.. anyone know how to do this? At the moment i can get the first picture of every product but not the other pictures (the many) – DonQuery Oct 18 '18 at 11:58
Hmm, seems vague but added use of SimpleXML to iterate through pictures – Anton Oct 18 '18 at 13:42
so where are you using the id of product in a product table to attach to the many pictures in pictures table with same id as product? i might ask this part of the question as fresh question. seem to be losing the thread. " If you don't have any, you can generate it manually for each product and give this value to your pictures" ( this is what i need to know how to do ..but not just for 1 picture i need to loop through all pictures and have same product id as foreign key – DonQuery Oct 18 '18 at 13:48
If it's in XML, then grab it from file. If id is not specified it can be assigned in DB (`auto_increment` option) and then be populated using `last_insert_id()` – Anton Oct 18 '18 at 13:50
exactly anton,,now read from my post ... i have many pictures to be asscociated with 1 product id.. so i need to loop through the deep nested picture elements and link to the id of product.... which i have done for the first picture but not for the remaining 2 pictures of each product - so my question is , how do i loop the pictures to same id – DonQuery Oct 18 '18 at 13:53

xml insert with php and mysql

1 Answers1