php - Inserting data from an XML file into table -

i trying data xml file on web datbase can use it.

i have produced following code, been long time since have done coding, imlost error message getting.

the error "unknown column '10074' in 'field list'".

10074 produce id of first item in xml file.

any pointers useful doing head in!

the code have follows:

    <?php    $products = simplexml_load_file('http://atsdistribution.co.uk/feeds/xml_all_products.aspx');   $con = mysql_connect(details); if (!$con)   {   die('could not connect: ' . mysql_error());   } mysql_select_db("catflaps_products", $con);   foreach($products->product $product) { $productid = $product->productid; $name = $product->name; $dropshipprice = $product->dropshipprice; $srp = $product->srp; $brand = $product->brand; $xline = $product->xline; $instock = $product->instock; $stock = $product->stock; $barcode = $product->barcode; $weight = $product->weight; $categoryid = $product->categoryid; $category = $product->category; $smallimage = $product->smallimage; $largeimage = $product->largeimage; $description = $product->description;  mysql_query("insert test(productid, name, dropshipprice, srp, brand, xline, instock, stock, barcode, weight, categoryid, category, smallimage, largeimage, description) values(`$productid`, `$name` , `$dropshipprice`, `$srp`, `$brand`, `$xline`, `$instock`, `$stock`, `$barcode`, `$weight`, `$categoryid`, `$category`, `$smallimage`, `$largeimage`, `$description`)")         or die(mysql_error());  }  mysql_close($con);   ?> 

1. you should not use backquotes inside values part. it's quote mysql identifiers (like table, column names). think if remove problem solved
2. you should use quotes (regular ones either ' or ") when quote string value in values part (but see below, there better approach)
3. if choose #2, need escape values xml using mysql_real_escape_string in case. in fact, breach in security (see sql injections) if don't this. if temporary script one-time usage etc, you'll end error when there single- or double-quote in xml data
4. the best approach using pdo prepare statements, don't bother quoting datatypes quotes or not doing - bind param datatype. , remember mysql_* functions deprecated today.

so code works charm:

<?php $products = simplexml_load_file('xml_all_products.xml'); $config = array('db' => array(     'dbname' => 'test',     'host'   => 'localhost:4040',     'username' => 'xx',     'password' => 'xxx' )); $db = new pdo('mysql:dbname='.$config['db']['dbname'].';host='.$config['db']['host'],$config['db']['username'],$config['db']['password']);  foreach($products->product $product) {     $productid = $product->productid;     $name = $product->name;     $dropshipprice = $product->dropshipprice;     $srp = $product->srp;     $brand = $product->brand;     $xline = $product->xline;     $instock = $product->instock;     $stock = $product->stock;     $barcode = $product->barcode;     $weight = $product->weight;     $categoryid = $product->categoryid;     $category = $product->category;     $smallimage = $product->smallimage;     $largeimage = $product->largeimage;     $description = $product->description;      $productsrs = $db->prepare("insert test(productid, name, dropshipprice, srp, brand, xline, instock, stock, barcode, weight, categoryid, category, smallimage, largeimage, description)                         values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");      $productsrs->execute(array($productid, $name, $dropshipprice, $srp, $brand, $xline, $instock, $stock, $barcode, $weight, $categoryid, $category, $smallimage, $largeimage, $description)); }