Parsing XML into MySQL

I’ve got an xml feed that I need to retrieve from a URL. Once I’ve got this I then need to extract the results and insert them into a database but have no idea how to do this and would be grateful if anybody can give me any help/pointers please.

Some of the xml fields will go into the same field in the database, so for example i list of song tracks would need to be in the same table even though they’re all separate in the xml file.

Thanks in advance

you can parse XML using SimpleXML or DOMDocument.

that makes one insertion per field.

Sorry to be really dumb, but how would I go about that? I’ve never done anything like this before and am completely confussed

the XML parsing or the DB insertion?

Sorry the XML parsing

have a look at the examples.

The Symfony DomCrawler coupled with the CSS Selector makes it super simple to parse XML using CSS selectors. Not to mention there is some really thorough documentation.

I’ve had a look at SimpleXML and because I’m using a url feed rather than an actual file I think I need to create a file on the server. I tried to do that but although it does write the file it’s all gibberish. I’ve attached a screenshot of what I get.

This is the code I’m using to write the file:

<?php
set_time_limit(0);
$fp = fopen ('a.xml', 'w+');
$ch = curl_init('http://datafeed.api.productserve.com/datafeed/download/apikey/099640af374a1c61137ad9b60f202215/fid/3712/columns/aw_product_id,merchant_product_id,merchant_category,aw_deep_link,merchant_image_url,search_price,description,product_name,merchant_deep_link,aw_image_url,merchant_name,merchant_id,category_name,category_id,delivery_cost,currency,store_price,display_price,data_feed_id,rrp_price,specifications,condition,promotional_text,warranty,merchant_thumb_url,aw_thumb_url,brand_name,brand_id,delivery_time,valid_from,valid_to,web_offer,pre_order,in_stock,stock_quantity,is_for_sale,product_type,commission_group,upc,ean,mpn,isbn,model_number,parent_product_id,language,last_updated,dimensions,colour,keywords,custom_1,custom_2,custom_3,custom_4,custom_5,saving,delivery_weight,delivery_restrictions,reviews,average_rating,number_stars,number_available,rating,alternate_image,large_image,basket_link/format/xml/dtd/1.4/compression/gzip/adultcontent/1/');// or any url you can pass which gives you the xml file
curl_setopt($ch, CURLOPT_TIMEOUT, 50);
curl_setopt($ch, CURLOPT_FILE, $fp);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_exec($ch);
curl_close($ch);
fclose($fp);
?>

All the solutions mentioned support loading content from a string or a file on the file system. Seeing what you are doing I would also highly recommend using Goutte for handling http requests.

I’m not using Symphony or any framework can I still do this?

Do what?

Can I still use SimpleXML without any framework?

Also I’ve discovered I need to uncompress the file before I can save it to the server. The problem is when I try to do that it doesn’t work. This is what I’m using to save the uncompress and save the file:

<?php
set_time_limit(0);
$fp = fopen ('a.xml', 'w+');
$ch = curl_init('http://datafeed.api.productserve.com/datafeed/download/apikey/099640af374a1c61137ad9b60f202215/fid/3712/columns/aw_product_id,merchant_product_id,merchant_category,aw_deep_link,merchant_image_url,search_price,description,product_name,merchant_deep_link,aw_image_url,merchant_name,merchant_id,category_name,category_id,delivery_cost,currency,store_price,display_price,data_feed_id,rrp_price,specifications,condition,promotional_text,warranty,merchant_thumb_url,aw_thumb_url,brand_name,brand_id,delivery_time,valid_from,valid_to,web_offer,pre_order,in_stock,stock_quantity,is_for_sale,product_type,commission_group,upc,ean,mpn,isbn,model_number,parent_product_id,language,last_updated,dimensions,colour,keywords,custom_1,custom_2,custom_3,custom_4,custom_5,saving,delivery_weight,delivery_restrictions,reviews,average_rating,number_stars,number_available,rating,alternate_image,large_image,basket_link/format/xml/dtd/1.4/compression/gzip/adultcontent/1/');// or any url you can pass which gives you the xml file 
curl_setopt($ch, CURLOPT_ENCODING, 1); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 50);
curl_setopt($ch, CURLOPT_FILE, $fp);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_exec($ch);
curl_close($ch);
fclose($fp);
?>

as I say though it saves the file as gibberish :frowning:

Try to get in the habit of checking documentation, it will save you much grief

http://php.net/manual/en/function.curl-setopt.php

CURLOPT_ENCODING The contents of the "Accept-Encoding: " header. This enables decoding of the response. Supported encodings are “identity”, “deflate”, and “gzip”. If an empty string, “”, is set, a header containing all supported encoding types is sent.

vs.

curl_setopt($ch, CURLOPT_ENCODING, 1);

yes

I never mentioned anything about Simple XML though so don’t know what you’re referring to there.

Sorry that was Dormilich that mentioned that, but you mentioned DomCrawler and I didn’t know if I could use that without a framework?

I did try using it with empty speech marks but that had the same result, sorry I should have said what I’d tried.

I’ve tried:

curl_setopt($ch, CURLOPT_ENCODING, "");
curl_setopt($ch, CURLOPT_ENCODING, 1);
curl_setopt($ch, CURLOPT_ENCODING, "gzip");
curl_setopt($ch, CURLOPT_ENCODING, "gzip,deflate");

and none of them work :frowning:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.