XML Feed into mysql

[apg1985]

Tweet

Ok,

So ive been looking into this for a few hours now and im about to stick a folk in my eye, I cant for the life of me get this to work.

In my hosting I have db_connect.inc with db connection details / I have a file containing the below script which is not working / I have a folder called xmlfeeds with an empty xml file called productfeed.xml

When I run the script the xml file does not update, it stays how it is and the following error is thrown:

Parse error: syntax error, unexpected '{' in myurl/script.php on line 12

<?php
require_once("db_connect.inc");
db_connect();

$xmlReader = new XMLReader();
$filename = "productfeed.xml";
$url =  "http://datafeed.api.productserve.com/datafeed/download/apikey/08d521ce1e75d09d7e68a4d65d60cb4f/cid/281,283,554,285,555,286,282,287,288/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,aw_thumb_url,brand_id,brand_name,commission_amount,commission_group,condition,currency,delivery_time,display_price,ean,in_stock,is_hotpick,isbn,is_for_sale,language,merchant_thumb_url,model_number,mpn,parent_product_id,pre_order,product_type,promotional_text,rrp_price,specifications,stock_quantity,store_price,upc,valid_from,valid_to,warranty,web_offer/format/xml/compression/zip/";

file_put_contents($filename, file_get_contents($url));

$zip = new ZipArchive();
if ($zip->open($filename) == TRUE {
$zip->extractTo('xmlfeeds');
$zip->close();
}
else {
echo "Extraction failed";
}
$xmlReader->open('xmlfeeds/productfeed.xml');

while($xmlReader->read())
    {

    switch ($xmlReader->name)
        {
        
        case 'product':
            
            $dom = new DOMDocument();
            $domNode = $xmlReader->expand();
            $element = $dom->appendChild($domNode);
            $domString = utf8_encode($dom->saveXML($element));
            $product = new SimpleXMLElement($domString);
            
            $id = $prod->prod['id'];
            $name = $product->name;
            $tracking = $product->awTrack;
            
            
            if(strlen($prod) > 0)
            {
            
            $query = mysql_query ("REPLACE INTO ddtest (id, name, deeplink) VALUES ('$id', '$name', '$tracking')");
            echo $name." has been instered!";
            
            }
        break;
    }
} 
?>

[jonsp]

not a PHP guy but this line
if ($zip->open($filename) == TRUE {
doesn't look right - maybe
if ($zip->open($filename) == TRUE) {

[scriptmonkey]

What he said ^

[apg1985]

Ok so when I get a chance ill give it ago.

Another question - say if the feed on a monday has 10 offers and they go into the database from my script.

Now say on the tuesday the 10 offers from monday have expired and 10 NEW offers are in the feed.

When the script adds these to the database will it overwrite the old ones or put them in under the old ones?

Basically will I have 10 rows on the tuesday or 20.

[scriptmonkey]

Depends on how you set up your database, if you make the link url (or guid) the primary, no duplicates field, then you won't overwrite anything that already exists, and, you'll add the new stuff to the end of the table.

[jonsp]

Ok so when I get a chance ill give it ago.

Another question - say if the feed on a monday has 10 offers and they go into the database from my script.

Now say on the tuesday the 10 offers from monday have expired and 10 NEW offers are in the feed.

When the script adds these to the database will it overwrite the old ones or put them in under the old ones?

Basically will I have 10 rows on the tuesday or 20.

Whichever you want :-)

Why not just delete the existing offers then import the new ones?

[webboy]

Just a Tip here , i would do this in real time as the price value will be out of date every day if you miss the update. plus if your insert fails to update successfully . No need to dump all the info

[nick-harper]

Just a Tip here , i would do this in real time as the price value will be out of date every day if you miss the update. plus if your insert fails to update successfully . No need to dump all the info

That would slow down page load time though, and with Google counting this it isn't worth it.

It depends how many feeds you have and the size of it really. Just set it as a regular CRON for every few hours so you won't ever forget.

[Xerath]

agree with the above post...

your code at the moment just inserts because you haven't said WHERE to replace... You can either use "TRUNCATE TABLE mytable" to delete everything in there, then insert from the feed starting fresh

or you could REPLACE INTO mytable WHERE id = $id

which will do... well, what it says replace with the newer info where the id matches

[apg1985]

Yea was going to do a cron job.

Only thing which is happening with code above is it runs smoothly so no errors but the file name ive asked it to create puts it in the root and not the directory 'xmlfeeds' and the file which it does create on the root contains a load of rubbish where its trying to store the zip file itself in the xml file.

But it also stores the correct file from the zip inside the directory but as the file name actually in the xml feed so something like 687598_xmlfile.xml.

So basically when it runs I get a xml file in root called productfeed.xml with zipfile code in it and another in directory xmlfeeds which contains xml data from feed but not renamed.

p.s Xerath ill get back to you soon been busy outside of affiliate stuff.

[Xerath]

If you don't want it in the ROOT, the filename can actually contain the path as well.. eg/

$filename = "/user/mysite.com/www/xmlfeeds/productfeed.xml";




I'm not sure about the line $zip->extractTo('xmlfeeds');

Maybe you need a full-path in there too?

[apg1985]

Ok so I got the file going into the correct folder but now its not storing it in the db, goes through to end but gives no error??

This is what I got so far:

<?php
require_once("db_connect.inc"); 
db_connect();
$xmlReader = new XMLReader();
$filename = "xmlfeeds/datafeed_112359.xml";
$url =  "http://datafeed.api.productserve.com/datafeed/download/apikey/08d521ce1e75d09d7e68a4d65d60cb4f/cid/281,283,554,285,555,286,282,287,288/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,aw_thumb_url,brand_id,brand_name,commission_amount,commission_group,condition,currency,delivery_time,display_price,ean,in_stock,is_hotpick,isbn,is_for_sale,language,merchant_thumb_url,model_number,mpn,parent_product_id,pre_order,product_type,promotional_text,rrp_price,specifications,stock_quantity,store_price,upc,valid_from,valid_to,warranty,web_offer/format/xml/compression/zip/";

file_put_contents($filename, file_get_contents($url));

$zip = new ZipArchive();
if ($zip->open($filename) == TRUE) {
$zip->extractTo('xmlfeeds');
$zip->close();
}
else {
echo "Extraction failed";
}
$xmlReader->open('xmlfeeds/datafeed_112359.xml');

while($xmlReader->read())
    {

    switch ($xmlReader->name)
        {
        
        case 'prod':
            
            $dom = new DOMDocument();
            $domNode = $xmlReader->expand();
            $element = $dom->appendChild($domNode);
            $domString = utf8_encode($dom->saveXML($element));
            $product = new SimpleXMLElement($domString);
            
            $id = $prod->prod['id'];
            $name = $prod->name;
            $deeplink = $prod->awTrack;
            
            
            if(strlen($id) > 0)
            {
            
            $query = mysql_query ("REPLACE INTO test (id, name, deeplink) VALUES ('$id', '$name', '$deeplink')");
            echo $id." has been instered!";
            
            }
        break;
    }
} 
?>

[michaeldim]

Try this,

<?php
require_once("db_connect.inc"); 
db_connect();
$filename  = "xmlfeeds/datafeed_112359.xml";
$url       = "http://datafeed.api.productserve.com/datafeed/download/apikey/08d521ce1e75d09d7e68a4d65d60cb4f/cid/281,283,554,285,555,286,282,287,288/columns/merchant_id,merchant_name,aw_product_id,merchant_product_id,product_name,description,category_id,category_name,merchant_category,aw_deep_link,aw_image_url,search_price,delivery_cost,merchant_deep_link,merchant_image_url,aw_thumb_url,brand_id,brand_name,commission_amount,commission_group,condition,currency,delivery_time,display_price,ean,in_stock,is_hotpick,isbn,is_for_sale,language,merchant_thumb_url,model_number,mpn,parent_product_id,pre_order,product_type,promotional_text,rrp_price,specifications,stock_quantity,store_price,upc,valid_from,valid_to,warranty,web_offer/format/xml/compression/zip/";

file_put_contents($filename, file_get_contents($url));

$zip = new ZipArchive();
if ($zip->open($filename) == TRUE) {
    $zip->extractTo('xmlfeeds');
    $zip->close();
} else {
    echo "Extraction failed";
}

$xml = simplexml_load_file($filename);

foreach ($xml->merchant as $merchant) {
    foreach ($merchant->prod as $product) {
        $id   = $product->attributes()->id;
        $name = $product->text->name;
        $url  = $product->uri->awTrack;
        
        
    if(strlen($id) > 0)
            {
            
            $query = mysql_query("REPLACE INTO test (id, name, deeplink) VALUES ('$id', '$name', '$url')");
            echo $id." has been inserted!</br>";
            
            }
    }
}

[apg1985]

Sweet, that worked perfectly.

Thanks dude.

[apg1985]

Uh problem, I was testing it with 1 merchant in the xml feed, if there are two or more the structure of the xml changes

Check feed out with more then one merchant:

http://datafeed.api.productserve.com...mpression/zip/