Generate seperate XML file for each mysql table row using PHP

12
January 12, 2019, at 05:30 AM

I have this function in php script to generate xml file from mysql table.

    function createXMLfile($stackarray){
    $filePath = '/root/stack.xml';
    $dom = new DOMDocument('1.0', 'utf-8');
    $root = $dom->createElement('REQUEST');
    for($i=0; $i<count($stackarray); $i++){
        $stackName    =  $stackarray[$i]['stack_name']; 
        $stackSERIAL      =  $stackarray[$i]['stack_serialnumber']; 
        $content = $dom->createElement('CONTENT');
        $device = $dom->createElement('DEVICE');
        $info = $dom->createElement('INFO');
        $name   = $dom->createElement('NAME', $stackName); 
        $info->appendChild($name);  
        $serial     = $dom->createElement('SERIAL', $stackSERIAL); 
        $info->appendChild($serial);
        $type     = $dom->createElement('TYPE', "NETWORKING"); 
        $info->appendChild($type);
        $device->appendChild($info);
        $root->appendChild($content);
        $module     = $dom->createElement('MODULEVERSION', "3.1");
        $content->appendChild($module);
        $process     = $dom->createElement('PROCESSNUMBER', "1");
        $content->appendChild($process);
        $content->appendChild($device);        
        $deviceid = $dom->createElement('DEVICEID', "foo");
        $root->appendChild($deviceid);
        $query     = $dom->createElement('QUERY', "SNMPQUERY");
        $root->appendChild($query);
    }
    $dom->appendChild($root); 
    $dom->save($filePath);
}

What it does currently is generating xml file for whole data in mysql table based on field mapping above. Eg;

<?xml version="1.0" encoding="utf-8"?>
<REQUEST>
  <CONTENT>
    <MODULEVERSION>3.1</MODULEVERSION>
    <PROCESSNUMBER>1</PROCESSNUMBER>
    <DEVICE>
      <INFO>
        <NAME>Nexus5020 Chassis</NAME>
        <SERIAL>SS613390FZT</SERIAL>
        <TYPE>NETWORKING</TYPE>
      </INFO>
    </DEVICE>
  </CONTENT>
  <DEVICEID>foo</DEVICEID>
  <QUERY>SNMPQUERY</QUERY>
  </REQUEST>

Mysql table structure looks like this

+----+------------------------+--------------------+
| id | stack_name             | stack_serialnumber |
+----+------------------------+--------------------+
|  1 | Nexus5020 Chassis      | SS613390FZT        |
|  2 | 40x10GE/Supervisor     | JA91344BHNK        |
|  3 | 6x10GE Ethernet Module | JA71228018M        |
|  4 | 8x1/2/4G FC Module     | JAB1531020C        |
|  5 | Nexus5020 Chassis      | SSI13390FZT        |

I want to generate specific XML file for each row based on the mysql data above using existing element mapping in the php code. Eg above table contains 5 rows so it should have 5 XML files generated.

How do I accomplish this? Someone pointed out to use array column feature [array_column ( array $array , mixed $column_key [, mixed $index_key = null ] )] in the script. Is there another way to do this?

Answer 1

It should work to just put the creation of the document inside the loop. By adding the loops number $i to the filepath, you prevent overwriting the file on each iteration.

function createXMLfile($stackarray){
    for($i=0; $i<count($stackarray); $i++){
        $filePath = "/root/stack" . $i . ".xml";
        $dom = new DOMDocument('1.0', 'utf-8');
        $root = $dom->createElement('REQUEST');
        $stackName    =  $stackarray[$i]['stack_name']; 
        $stackSERIAL      =  $stackarray[$i]['stack_serialnumber']; 
        $content = $dom->createElement('CONTENT');
        $device = $dom->createElement('DEVICE');
        $info = $dom->createElement('INFO');
        $name   = $dom->createElement('NAME', $stackName); 
        $info->appendChild($name);  
        $serial     = $dom->createElement('SERIAL', $stackSERIAL); 
        $info->appendChild($serial);
        $type     = $dom->createElement('TYPE', "NETWORKING"); 
        $info->appendChild($type);
        $device->appendChild($info);
        $root->appendChild($content);
        $module     = $dom->createElement('MODULEVERSION', "3.1");
        $content->appendChild($module);
        $process     = $dom->createElement('PROCESSNUMBER', "1");
        $content->appendChild($process);
        $content->appendChild($device);        
        $deviceid = $dom->createElement('DEVICEID', "foo");
        $root->appendChild($deviceid);
        $query     = $dom->createElement('QUERY', "SNMPQUERY");
        $root->appendChild($query);
        $dom->appendChild($root); 
        $dom->save($filePath);
    }
}
READ ALSO
How do I sync a mysql table to a hive table ? (sqoop --incremental lastmodified hive imports is not supported)

How do I sync a mysql table to a hive table ? (sqoop --incremental lastmodified hive imports is not supported)

I want to sync a mysql table into hive tableBecause records in orders table usually changed in nearly future

51
How to execute PHP code with MySQL through command line?

How to execute PHP code with MySQL through command line?

I am trying to execute my PHP script through command line using below command:

22
I want to connect between MYSQL 5.7 with Crystal Rport using Windows Application framework 4

I want to connect between MYSQL 5.7 with Crystal Rport using Windows Application framework 4

Need a help for what are the drivers and version to install and connect MYSQL server 57 with Crystal Report for Windows Application Framework 4

10
SELECT random rows with flexible search query

SELECT random rows with flexible search query

I am trying to create a Flexible Search that retrieves 10,000 random rows out of 3MTried with different syntax like LIMIT, but i cannot make it work, I need to add this query to a Groovy Script

26