Insert from XML into mysql

I have an xml datafeed.

I've managed to display selected entries from the xml file with php code using...

$xml = simplexml_load_file("../shop/datafeed_79329.xml");

foreach($xml->merchant->prod as $product){ 
$product->text->name;
$product->uri->awThumb;
$product->price->buynow;

PHP:

Now I'd like to insert the selected entries into a mysql database.

Can anyone help with the mysql insert, what I have cobbled together isn't working

mysql_query("INSERT INTO shop (name, awThumb, buynow)
VALUES ('$product->text->name;', '$product->uri->awThumb;', '$product->price->buynow;')");

PHP:

 

Hello,

try this,

foreach($xml->merchant->prod as $product){
mysql_query("INSERT INTO shop (name, awThumb, buynow)
VALUES ('$product->text->name', '$product->uri->awThumb', '$product->price->buynow')");
}

 

That inserts

->name ->awThumb and 0


into the table fields.

 

Hello,

Have a try on this

foreach($xml->merchant->prod as $product){


$name = $product->text->name;
$awThumb = $product->uri->awThumb;
$buynow = $product->price->buynow;


mysql_query("INSERT INTO shop (name, awThumb, buynow)
VALUES ('$name', '$awThumb', '$buynow')");
}

also while using the above code please check the values of $name,$awThumb,$buynow by printing them this way you can get a better idea of wats going on if this doesnt work plz provide the xml file.

 

Hello

Thanks for getting back to me

The code has inserted 1,207 rows into the table, but each row is exactly the same.

The last item from the feed is what has been repeated.

It seems to work but is repeating the last product 1207 times, rather than 1207 separate products.

Here is a sample of the .xml feed i'm using

<merchantProductFeed>
<merchant id="####" name="####">
<prod id="245238091" pre_order="no" web_offer="no" in_stock="no" stock_quantity="0">
<pId>10490</pId>
<text>
<name>You're Busted Police Woman Costume</name>
<desc>
He’ll be doing hard time when he sees you in this cute and sexy police uniform! This classic police woman uniform features a fitted dress with matching tie and hat. We’ve even included the handcuffs for your little trouble maker!
</desc>
</text>
<uri>
<awTrack>
http://www.awin1.com/pclick.php?p=245238091&a=#####&m=2268
</awTrack>
<awThumb>
http://images.productserve.com/thumb/2268/245238091.jpg
</awThumb>
<awImage>
http://images.productserve.com/preview/2268/245238091.jpg
</awImage>
<mLink>
http://www.passion8.co.uk/pd/Youre-Busted-Police-Woman-Costume_82403.html
</mLink>
<mImage>http://www.passion8.co.uk/p/l/82403.jpg</mImage>
</uri>
<price curr="GBP">
<buynow>24.99</buynow>
<store>0.00</store>
<rrp>0.00</rrp>
<delivery>3.95</delivery>
</price>
<cat>
<mCat>Role Play</mCat>
</cat>
<brand/>
<valFrom>0000-00-00</valFrom>
<valTo>0000-00-00</valTo>
</prod>
</merchant>
</merchantProductFeed>

HTML:

And here's the php I'm using

<?php

include('../shop/dbconn.php');

$con = mysql_connect(localhost,$username,$password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("#######", $con);

$xml = simplexml_load_file("../shop/datafeed_79329.xml");

foreach($xml->merchant->prod as $product){ 
$name    = $product->text->name;
$awThumb = $product->uri->awThumb;
$buynow  = $product->price->buynow;
}  

foreach($xml->merchant->prod as $product){ 
   mysql_query("INSERT INTO shop (name, awThumb, buynow)
   VALUES ('$name', '$awThumb', '$buynow')");
}


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

PHP:

 

Hello,

It seems that the problem is with your foreach loop.
Try using below code.

<?php


include('../shop/dbconn.php');


$con = mysql_connect(localhost,$username,$password);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}


mysql_select_db("#######", $con);


$xml = simplexml_load_file("../shop/datafeed_79329.xml");


foreach($xml->merchant->prod as $product){
$name = $product->text->name;
$awThumb = $product->uri->awThumb;
$buynow = $product->price->buynow;


mysql_query("INSERT INTO shop (name, awThumb, buynow)
VALUES ('$name', '$awThumb', '$buynow')");
}




if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";


mysql_close($con)
?>

 

That's great, it's working.

I do get a

Error: Query was empty

when submitting the data, and again when reading it but I'm just pleased to get this far.

Thank you very much for all your help, much appreciated.

Phil

 

I've got a problem with this which is driving me crazy.

I've add name, price, thumbnail, catergory and link to the database.

But as soon as I try to add description, suddenly it stops working.


The last 'descip' stops everything from working. Help

Here's the code....

foreach($xml->merchant->prod as $product){ 
$name = $product->text->name;
$awThumb = $product->uri->awThumb;
$buynow = $product->price->buynow;
$mcat = $product->cat->mCat;
$awTrack = $product->uri->awTrack;
$descrip = $product->text->desc;

mysql_query("INSERT INTO shop2 (name, awThumb, buynow, mcat, awTrack, desc)
VALUES ('$name', '$awThumb', '$buynow', '$mcat', '$awTrack', '$descrip')");
}

PHP:

 

Problem solved,

Seemed to be name of the field 'desc' in the table, changed it 'descrip' and all is working.