XML Parsed to > PHP > MySql

[Sawell]

Basically I have a XML data source (to be speicifc it's product listings).

 

I need something to read that XML data and place everything in tags into MySQL tables.

 

Example:

 

<product>Jar of sweeties</product> is is in an XML feed, PHP reads that XML and takes the contents of <product> and places it in the product_listing table under "Description".

 

Php seems the best option, then I can just set it as a daily cron job.

 

Is there a template script I can get for this? Any help massively appreciated.

[Psycho]

There is no one-size-fits-all script that I know of. Mostly because each situation is different. Please provide some more details of the data that will exist in the XML file and how you want it parsed and inserted intot he database and we can provide more assistance.

[Sawell]

I see, apologies for the conciseness.

 

The exact XML output from my distributors will look like this:

 

  <?xml version="1.0" ?> 
- <!--  Target Components Ltd Product Information 
 --> 
- <product>
 <result>OK</result> 
 <responsedatetime>30/04/2008 14:21:39</responsedatetime> 
 <stockcode>CATAR-56031</stockcode> 
 <description>S-809 4BAY 255W MAX BLACK/SILVER USB/AUD</description> 
 <extendeddescription><b>Key Features:</b> <li>Chassis Form Factor: ATX Mid Tower</li> <li>Power Supply Form Factor: ATX</li> <li>Bezel Colors: Black with Silver Frame</li> <li>Bezel ID Available status: Mass Production Now</li> <li>Slide Door: None</li> <li>Air Duct without Filter: Air Duct without Filter</li> <li>5.25: 4</li> <li>3.5: 2</li> <li>Internal 3.5: 4</li> <li>USB + Audio + MIC: USB 2.0 x 2, Audio + Mic</li> <b>Specifications:</b> <li>Slots: 7</li> <li>Reset-Button Front Panel: Yes</li> <li>Fan: 80mm / Sleeve</li> <li>Case Dimension mm: 440*185*420</li> <li>Carton Dimension mm: 495*230*460</li> <li>Power Supply Model: PSN-255PP 355PP </li> <li>20+4 PIN: Yes</li> <li>115/230 SWITCH: Yes</li> <li>Safety / CE Certification: No</li></extendeddescription> 
 <overview><ul><li>Form Factor: ATX</li><li>Colour: Black & Silver</li><li>Front I/O: USB,Audio</li><li>PSU: 255W</li><li>Power Cord: No</li></ul></overview> 
 <imageurl>http://80.249.110.100/images/CATAR-56031.jpg</imageurl> 
 <weight>4.3</weight> 
 <stock>1197</stock> 
 <delivery>24</delivery> 
 <price>999.99</price> 
 <price5off>999.99</price5off> 
 <price20off>999.99</price20off> 
 </product>

 

So ideally I would parse that code, and whilst doing so host the product image locally on my own servers. As for the rest of it, just populate those fields in my database?

 

Admittedly I'm a bit out of my depth here, regardless, I need to get this done! I'm using cubecart software sadly, not a custom codded jobby (that's definitely out of my depth!). But aside from the table names I can't see how that would make much difference? I have had a look around for existing solutions but have been unsucsessful, so thought I'd give it a bash on my own, or hopefully with a little help.

[discomatt]

There's TONS out there for parsing XML files. There is even a PHP module designed for it.

 

http://php.net/manual/en/xml.examples.php

[dfowler]

This is what I normally use when I want to work with xml and php.

http://us.php.net/simplexml

[Psycho]

Here is some mock code based upon a script I have and the XML schema you provided. It is definitely not a complete script but hopefully it is enough for you to get started:

 

<?php

//Load the XML file
$xml_data = simplexml_load_file('xmldata\data.xml');

$data_output = array();

//Iterrate through each product
foreach ($xml_data->product as $product_data)
{

  $this_record = array();

  //Read and VALIDATE each field (add your own validation)
  $this_record['result'] = (string)$product_data->result;
  $this_record['responsedatetime'] = (string)$product_data->responsedatetime;
  $this_record['stockcode'] = (string)$product_data->stockcode;
  $this_record['description'] = (string)$product_data->description;

  //Etc for each needed field
  //Add any necessary validation for each
  //field so you don't INSERT a bad record

  //Assuming all validation passed for the current record
  $data_output[] = "('" . implode("', '", $this_record) . "')";

}

//Use the $data_output array to create a single query to insert all records
$values = implode(", ", $data_output);

$query = "INSERT INTO products
          (result, responsedatetime, stockcode, description)
          VALUES " . $values;

$result = mysql_query($query) or die (mysql_error());

?>