drdyszuy488152 2015-08-20 08:13
浏览 14
已采纳

问答:Ex-和Import数据库值包含多个XML表

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.

  • 写回答

1条回答 默认 最新

  • douyuan4697 2015-08-20 08:13
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥30 求一下解题思路,完全不懂。
  • ¥30 关于#硬件工程#的问题:求一下解题思路
  • ¥15 运筹学对偶单纯行法构造扩充问题
  • ¥20 XP系统的老电脑一开机就提示找不到rundll.exe,付费求解
  • ¥15 milvus查询出来的score怎么转换成0-1之间的相似性
  • ¥15 多ip服务器站群如何搭建l2tp服务器
  • ¥15 lvgl V9移植到linux开发板
  • ¥15 VB.net中在窗体中创建一个button控件来关闭窗体,但是提示错误,我该怎么办
  • ¥15 网上下载好的程序但是arduinoIDE编程报错,运行不了,哪里出错了,能具体给改一下吗
  • ¥15 Sharepoint JS开发 付费技术指导