如何使用php将mysql数据导出到xml

发布时间：2020-12-13 16:42:29 所属栏目：PHP教程来源：网络整理

导读：以下代码用于将数据从 mysql表导出为xml文件.我尝试了几个代码,但没有得到结果.请检查并帮助我. 目前得到的结果是 8sarathsarathernakulam423432washington9rahulrahulernakulam21212121newyork10aaaa3london11bbbb1newyork12cccc2washington13dddd3london

以下代码用于将数据从 mysql表导出为xml文件.我尝试了几个代码,但没有得到结果.请检查并帮助我.

目前得到的结果是

8sarathsarathernakulam423432washington9rahulrahulernakulam21212121newyork10aaaa3london11bbbb1newyork12cccc2washington13dddd3london

码

<?php
require_once "classes/dbconnection-class.php";
if(isset($_POST['export'])){
    header('Content-type: text/xml');
    $xml          = "<?xml version="1.0" encoding="UTF-8"?>";
    $root_element = "addressbook"; //fruits
    $xml         .= "<$root_element>";
    $query        = "SELECT AB.id,AB.name,AB.firstname,AB.street,AB.zipcode,AB.city_id,CI.city FROM address_book AS AB INNER JOIN city AS CI ON AB.city_id = CI.id";
    $result      = $mysqli->query($query);
    if (!$result) {
        die('Invalid query: ' . $mysqli->error());
    }

    while($result_array = $result->fetch_assoc()){
        $xml .= "<address>";
        foreach($result_array as $key => $value)
        {
            //$key holds the table column name
            $xml .= "<$key>";

            //embed the SQL data in a CDATA element to avoid XML entity issues
            $xml .= "<![CDATA[$value]]>";

            //and close the element
            $xml .= "</$key>";
        }

        $xml.="</address>";
    }
    $xml .= "</$root_element>";
    header ("Content-Type:text/xml");
    //header('Content-Disposition: attachment; filename="downloaded.xml"');
    echo $xml;
}
?>

浏览器显示

<?xml version="1.0" encoding="UTF-8"?><addressbook><address><id><![CDATA[8]]></id><name><![CDATA[sarath]]></name><firstname><![CDATA[sarath]]></firstname><street><![CDATA[ernakulam]]></street><zipcode><![CDATA[42343]]></zipcode><city_id><![CDATA[2]]></city_id><city><![CDATA[washington]]></city></address><address><id><![CDATA[9]]></id><name><![CDATA[rahul]]></name><firstname><![CDATA[rahul]]></firstname><street><![CDATA[ernakulam]]></street><zipcode><![CDATA[2121212]]></zipcode><city_id><![CDATA[1]]></city_id><city><![CDATA[newyork]]></city></address><address><id><![CDATA[10]]></id><name><![CDATA[a]]></name><firstname><![CDATA[a]]></firstname><street><![CDATA[a]]></street><zipcode><![CDATA[a]]></zipcode><city_id><![CDATA[3]]></city_id><city><![CDATA[london]]></city></address><address><id><![CDATA[11]]></id><name><![CDATA[b]]></name><firstname><![CDATA[b]]></firstname><street><![CDATA[b]]></street><zipcode><![CDATA[b]]></zipcode><city_id><![CDATA[1]]></city_id><city><![CDATA[newyork]]></city></address><address><id><![CDATA[12]]></id><name><![CDATA[c]]></name><firstname><![CDATA[c]]></firstname><street><![CDATA[c]]></street><zipcode><![CDATA[c]]></zipcode><city_id><![CDATA[2]]></city_id><city><![CDATA[washington]]></city></address><address><id><![CDATA[13]]></id><name><![CDATA[d]]></name><firstname><![CDATA[d]]></firstname><street><![CDATA[d]]></street><zipcode><![CDATA[d]]></zipcode><city_id><![CDATA[3]]></city_id><city><![CDATA[london]]></city></address></addressbook>

当我们处理XML和HTML时,最好的方法是通过一个解析器.
在这种特殊情况下,使用解析器进行操作可保证有效的XML和干净的短代码.

定义mySQL查询后,我们初始化一个带有版本和编码的DOMDocument,然后我们将他的 – > formatOutput设置为True,以缩进的格式打印出XML：

$query = "SELECT AB.id,CI.city FROM address_book AS AB INNER JOIN city AS CI ON AB.city_id = CI.id";

$dom   = new DOMDocument( '1.0','utf-8' );
$dom   ->formatOutput = True;

然后,我们创建根节点,并将其附加到DOMDocument：

$root  = $dom->createElement( 'addressbook' );
$dom   ->appendChild( $root );

在这一点上,执行mySQL查询后,我们对每个结果行执行一个while循环;对于每一行,我们创建一个空节点< address>然后我们通过每一行的字段执行foreach循环.对于每个字段,我们创建一个带有标记为字段键的空子节点,然后我们附加到子节点字段值作为CDATA,同样的子节点为< address>节点;在每个while循环结束时,每个< address>节点附加到根节点：

$result     = $mysqli->query( $query );
while( $row = $result->fetch_assoc() )
{
    $node = $dom->createElement( 'address' );
    foreach( $row as $key => $val )
    {
        $child = $dom->createElement( $key );
        $child ->appendChild( $dom->createCDATASection( $val) );
        $node  ->appendChild( $child );
    }
    $root->appendChild( $node );
}

现在,您的XML已经准备好了.

如果要将其保存到文件中,可以通过以下操作来实现：

$dom->save( '/Your/File/Path.xml' );

否则,如果您希望将其作为XML发送,则必须使用此代码：

header( 'Content-type: text/xml' );
echo $dom->saveXML();
exit;

如果你想要在HTML页面中输出,你可以写这段代码：

echo '<pre>';
echo htmlentities( $dom->saveXML() );
echo '</pre>';

>查看更多约DOMDocument

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!