How to parse following type of XML and store it into database using PHP

Question

I have to parse this type of XML and store into the database. Please help.

Here is the XML..

<?xml version='1.0' encoding='ASCII'?>
<root>
    <mac>mac_n</mac>
    <ip_addr>ip_addr_n</ip_addr>
    <name1>Adobe AIR</name1>
    <version1>15.0.0.356</version1>
    <name2>Adobe Flash Player 16 PPAPI</name2>
    <version2>16.0.0.235</version2>
</root>

I want to parse this XML in such a manner that mac-address & ip gets parsed once and it can be stored into the database for every software-name and version.

I have tried the following PHP code.. for the case when the tags have just the similar <name> but not like <name1>, <name2>. I want to tweak the code to for this format of XML stated above.

$url = "audit.xml";

$xml = simplexml_load_file($url);

$db = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $db->prepare("INSERT INTO software(name, version) VALUES(?, ?)");

foreach ($xml as $software => $row) {
    $name    = $row->name;
    $version = $row->version;

    $stmt->execute(array($name, $version));
}

Answer 1

The issue you have is less with the database but first of all with traversing the XML document.

From what you outline is that you basically want to skip the first two entries and the you want to have each two entries - name and version.

This can be managed quite straight forward by making use of Iterator - both concrete ones that ship with PHP and the interface itself which allows you to go forward even while foreach already traverses the XML document.

So first of all skip the first two entries:

$limit = new LimitIterator(new IteratorIterator($xml), 2);

foreach ($limit as $row) {

Here the $xml you have is first wrapped into an IteratorItertor (necessary for a SimpleXMLElement as it's a Traversable but LimitIterator needs a true Iterator) and the 2 as second parameter just tells the offset. Which is equal to: Skip the first two entries.

Next is to fetch two entries at once. The foreach is fetching only one entry once, so the second entry needs to be "fetched" manually:

foreach ($limit as $row) {

    $name = (string) $row;
    $limit->next();
    $version = (string) $limit->current();

    ...

The $limit->next() call just moves forward to the next element and $limit->current() fetches that next element.

And that's it already. That done, you have $name and $version inside the foreach for each entry (pair). You then just do the insert operation. Here is an example with PDO:

$buffer = <<<XML
<?xml version='1.0' encoding='ASCII'?>
<root>
    <mac>mac_n</mac>
    <ip_addr>ip_addr_n</ip_addr>
    <name1>Adobe AIR</name1>
    <version1>15.0.0.356</version1>
    <name2>Adobe Flash Player 16 PPAPI</name2>
    <version2>16.0.0.235</version2>
</root>
XML;

$xml = simplexml_load_string($buffer);

$db = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'testuser', 'test');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $db->prepare("INSERT INTO software (`name`, `version`) VALUES(?, ?)");

$limit = new LimitIterator(new IteratorIterator($xml), 2);

foreach ($limit as $row) {

    $name = (string)$row;
    $limit->next();
    $version = (string)$limit->current();

    $stmt->execute(array($name, $version));
}

This script inserts quickly and flawlessly into the configured database:

CREATE TABLE test.software (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255),
  `version` VARCHAR(127)
);