Q&A: Ex- and Import Database values with more than 1 Table in XML

Question

Hello fellow StackO's.

In the last day I worked on a solution to Export specific values from different Tables into one XML file. The major probelm : I had three levels of nested tables. As I had problems writing these functions, I'd like to share my knowledge with you.

I used XMLWriter and XMLReader for this

Here is the layout of the XML file that should be the final output:

<Table 1 Col1=".." Col2="..">
    <Table 2 Col1="...">
        <Table3 Col1=".." Col2="" Col3=".." Col4="..." />
        <Table3 Col1=".." Col2="" Col3=".." Col4="..." />
        <Table3 Col1=".." Col2="" Col3=".." Col4="..." />       
     </Table2>
    <Table1>
        <Table1>....</Table>
    </Table>
</Table2>
<Table1 Col1="xxx" Col2="xxx">
 ...

The are refering to the tablenames: -> "tablename". I will continue using this pattern for this example.

For the solution; look at the Export part in the anwser.

After I managed it to export this values and created the file, I wanted to do this backwards. Therefore I had to Import this file. This case was a bit tricky. For this, read the Import part.

I hope this will help someone.

PS: Special thanks to Craig Ringer who helped me a lot with this function.

Answer 1

Export

As I mentioned, I will use XMLWriter for this case.

At first you have to write the standard part on the top of the function:

        $xml = new XMLWriter();
        $xml->openURI($file);
        $xml->setIndent(true);
        $xml->startDocument('1.0','UTF-8');

Then you start a new Query and select the first table (table1). Legend: Primary Key

    $query = new data_DataBaseQuery();

        $sql = '
                SELECT
                *(table1).table1pk*  AS "*table1pk*", (table1).col1 AS col1, (table1)."col1" AS col1
                FROM
                table1
                ORDER BY "*table1pk*"
            ';

After the query succeeded, start fetching the rows, t1 = table1 shortcut

        if ($query->doQuery($sql) && $query->num_rows()) {

            $rows_t1 = $query->fetch_all();
                foreach ($rows_t1 as $row_t1)  {

                    $*table1pk* = $row_k['*table1pk*'];
                    $col1 = $row_t1['col1'];
                    $col2 = $row_t1['col1'];

Start writing the values in the XML file

                    $xml->startElement("Table1");
                    $xml->writeAttribute('Col1', $col1); 
                    $xml->writeAttribute('Col1', $col1);

Okay, now whe have this output:

 <Table 1 Col1=".." Col2=".."/>

Now, we can re-use our function for the next iteration. We simply change the tablenames, variables and Output names.

The output will look like this:

<Table 1 Col1=".." Col2="..">
    <Table 2 Col1="..."/>   
</Table1>

For the next we start a new iteration and so on. The final output should look like in the original Post. Do not forget to close the loops and end the Elements correctly. Otherwise it can look stange. In this case your end should look like this:

                        $xml->endElement(); // End Table3
                        }
                    }   
                    $xml->endElement(); // End Table2
                }
            }
            $xml->endElement(); // End Table1
        }
    }

$xml->endElement();
$xml->endDocument();
$xml->flush();

---

Import

For the import we reverse the logic of the Export. For this, we use XMLReader, which is a cursor like reader for XML files. In our case a good solution, as it can distinguish between Elements and Attributes Attribute="...". At first we start a new Reader

reader = new XMLReader();

if ($reader->open("filename.xml")) {
    while($reader->read()) {
        if ($reader->nodeType == XMLReader::ELEMENT &&reader->name == 'Table 1') {

It selects all Entries with the elementname "Table 1"

            $col1 = $reader->getAttribute('Col1');
            $col1 = $reader->getAttribute('Col2');

The reader searches for the Attributes and put them into the values. Then, you have to select the tables and columns where you want to insert the entires and let the SQL do the rest.

            $SQL = "";
            $SQL .= "SELECT
                        (table1).col1 AS col1, (table1).col2 AS col1
                    FROM
                        table1
                        ";
            $SQL .= "INSERT INTO table1 (";
            $SQL .= "col1, col1";
            $SQL .= ") VALUES (";
            $SQL .= "'".$col1."', '".$col1."'";
            $SQL .= ");".PHP_EOL;
            echo $SQL;
    }

You can use the echo to test it on your server and remove it on your live system. Now we have inserted all selected values, so the echo should print your query without the PHP syntax.

Now you can continue Importing the Next Element, which is Table 2 with the same function and Table 3 and so on..

               if ($reader->nodeType == XMLReader::ELEMENT 
                    &&reader->name == 'Table 2') { ......}

                       if ($reader->nodeType == XMLReader::ELEMENT
                            &&reader->name == 'Table 3') { ......}
  }
    $reader->close();
}   

Don't forget to close the reader at the end of the function :)

So, this is my solution so far. If I find any options to optimize this code I will and hope this could hel some StackO users.

Please feel free to comment and improve this code.