如何将XML文件导入mysql db？

时间：2021-04-02 02:44:59

Can anyone help with converting data from an XML document into an associative array? I'm running into issues given that the XML structure. the code make loop, but repeat the same node (first node in xml file). this code insert into database four rows (all them the same row). How make the code goto to the other node in the xml.

任何人都可以帮助将XML文档中的数据转换为关联数组吗?考虑到XML结构,我遇到了问题。代码make循环,但重复相同的节点(xml文件中的第一个节点)。此代码将四行插入数据库(所有这些行都是同一行)。如何使代码转到xml中的其他节点。

/////////////////////////////XML FILE//////////////////////////////////////////////////

<?xml version="1.0" encoding="utf-8"?>
<root>
   <property>   
        <id>2</id>
        <type>2</type>
        <rent_type>1</rent_type>
        <cid>1</cid>
        <locid>2</locid>
        <stid>2</stid>
        <cnid>2</cnid>
        <locality>2</locality>
        <state>costa blanca</state>
        <country>spain</country>
        <office_id>1</office_id>
        <mls_id>2d2</mls_id>
        <mls_agent>2</mls_agent>
        <viewad>1</viewad>
        <viewbooking>1</viewbooking>
        <unit_num>mmmmm</unit_num>
        <street_num>nnnn</street_num>
        <address2>mmmbbbbbbbbbbbbbbb b bbm</address2>
        <postcode></postcode>
        <county>dd</county>
        <price>222222222.00</price>
        <showprice>1</showprice>
        <freq>0</freq>
        <bond>0.00</bond>
        <closeprice>2222222222.00</closeprice>
        <priceview>3</priceview>
        <age>5</age>
        <landtype>1</landtype>
        <frontage></frontage>
        <depth></depth>
    </property>
    <property>
        <id>3</id>
        <type>2</type>
        <rent_type>0</rent_type>
        <cid>1</cid>
        <locid>0</locid>
        <stid>0</stid>
        <cnid>0</cnid>
        <locality></locality>
        <state></state>
        <country></country>
        <office_id></office_id>
        <mls_id>3d3</mls_id>
        <mls_agent></mls_agent>
        <viewad>0</viewad>
        <viewbooking>0</viewbooking>
        <unit_num>mmmmm</unit_num>
        <street_num>nnnn</street_num>
        <address2>mmmm</address2>
        <postcode></postcode>
        <county>dd</county>
        <price>3333333333.00</price>
        <showprice>1</showprice>
        <freq>0</freq>
        <bond>0.00</bond>
        <closeprice>333333.00</closeprice>
        <priceview></priceview>
        <age></age>
        <landtype></landtype>
        <frontage></frontage>
        <depth></depth>
     </property>
    <property>
        <id>4</id>
        <type>0</type>
        <rent_type>0</rent_type>
        <cid>1</cid>
        <locid>0</locid>
        <stid>0</stid>
        <cnid>0</cnid>
        <locality></locality>
        <state></state>
        <country></country>
        <office_id></office_id>
        <mls_id>4d4</mls_id>
        <mls_agent></mls_agent>
        <viewad>0</viewad>
        <viewbooking>0</viewbooking>
        <unit_num>mmmmm</unit_num>
        <street_num>nnnn</street_num>
        <address2>mmmm</address2>
        <postcode></postcode>
        <county>dd</county>
        <price>444444444444444.00</price>
        <showprice>1</showprice>
        <freq>0</freq>
        <bond>0.00</bond>
        <closeprice>4444444444444444444.00</closeprice>
        <priceview></priceview>
        <age></age>
        <landtype></landtype>
        <frontage></frontage>
        <depth></depth>
    </property>
   <property>
        <id>5</id>
        <type>0</type>
        <rent_type>0</rent_type>
        <cid>1</cid>
        <locid>0</locid>
        <stid>0</stid>
        <cnid>0</cnid>
        <locality></locality>
        <state></state>
        <country></country>
        <office_id></office_id>
        <mls_id>5d5</mls_id>
        <mls_agent></mls_agent>
        <viewad>0</viewad>
        <viewbooking>0</viewbooking>
        <unit_num>mmmmm</unit_num>
        <street_num>nnnn</street_num>
        <address2>mmmm</address2>
        <postcode></postcode>
        <county>dd</county>
        <price>55555555555555.00</price>
        <showprice>1</showprice>
        <freq>0</freq>
        <bond>0.00</bond>
        <closeprice>55555555555.00</closeprice>
        <priceview></priceview>
        <age></age>
        <landtype></landtype>
        <frontage></frontage>
        <depth></depth>
    </property>
</root>

//////////////////////////////PHP CODE///////////////////////////////////////////////////// 

ini_set('display_errors','On');

echo "starting<br />";

//mysql connection
...
echo "connected to DB<br /><br />";

$url = "s_property1.xml";
    $xmlfgc = file_get_contents($url);
    $xmlitem = new SimpleXMLElement($xmlfgc);
    echo "xml loaded<br /><br />";

foreach ($xmlitem->property as $xml) {
//for ($i=0;$i ;){
    $id          = mysql_real_escape_string($xmlitem->property->id);
    $type        = mysql_real_escape_string($xmlitem->property->type);
    $rent_type   = mysql_real_escape_string($xmlitem->property->rent_type);
    $cid         = mysql_real_escape_string($xmlitem->property->cid);
    $locid       = mysql_real_escape_string($xmlitem->property->locid);
    $stid        = mysql_real_escape_string($xmlitem->property->stid);
    $cnid        = mysql_real_escape_string($xmlitem->property->cnid);
    $locality    = mysql_real_escape_string($xmlitem->property->locality);
    $state       = mysql_real_escape_string($xmlitem->property->state);
    $country     = mysql_real_escape_string($xmlitem->property->country);
    $office_id   = mysql_real_escape_string($xmlitem->property->office_id);
    $mls_id      = mysql_real_escape_string($xmlitem->property->mls_id);
    $mls_agent   = mysql_real_escape_string($xmlitem->property->mls_agent);
    $viewad      = mysql_real_escape_string($xmlitem->property->viewad);
    $viewbooking = mysql_real_escape_string($xmlitem->property->viewbooking);
    $unit_num    = mysql_real_escape_string($xmlitem->property->unit_num);
    $street_num  = mysql_real_escape_string($xmlitem->property->street_num);
    $address2    = mysql_real_escape_string($xmlitem->property->address2);
    $postcode    = mysql_real_escape_string($xmlitem->property->postcode);    
    $county      = mysql_real_escape_string($xmlitem->property->county);
    $price       = mysql_real_escape_string($xmlitem->property->price);
    $showprice   = mysql_real_escape_string($xmlitem->property->showprice);
    $freq        = mysql_real_escape_string($xmlitem->property->freq);
    $bond        = mysql_real_escape_string($xmlitem->property->bond);
    $closeprice  = mysql_real_escape_string($xmlitem->property->closeprice);    
    $priceview   = mysql_real_escape_string($xmlitem->property->priceview);
    $age         = mysql_real_escape_string($xmlitem->property->age);
    $landtype    = mysql_real_escape_string($xmlitem->property->landtype);
    $frontage    = mysql_real_escape_string($xmlitem->property->frontage);
    $depth       = mysql_real_escape_string($xmlitem->property->depth);

echo "xml parsed<br /><br />";

$xmldata = array();

$xmldata[] = '("' . $id. '", "' . $type . '", "' . $rent_type. '", "' . $cid . '", "' . $locid . '", "' . $stid . '", "' . $cnid . '", 
       "' . $locality . '", "' . $state . '", "' . $country . '", "' . $office_id . '", "' . $mls_id . '", "' . $mls_agent . '",
       "' . $viewad . '", "' . $viewbooking . '", "' . $unit_num . '", "' . $street_num . '", "' . $address2 . '", "' . $postcode . '",
       "' . $county . '", "' . $price . '", "' . $showprice . '", "' . $freq . '", "' . $bond . '", "' . $closeprice . '",
       "' . $priceview . '" ,"' . $age . '", "' . $landtype . '", "' . $frontage . '", "' . $depth . '")';

//insert into databse                     
$query = 'INSERT INTO gitfd_ezrealty ( id, type, rent_type, cid, locid, stid, cnid, locality, state, country, office_id, mls_id, mls_agent,
               viewad, viewbooking, unit_num, street_num, address2, postcode, county, price, showprice, freq, bond, closeprice, priceview,
               age, landtype, frontage, depth)
    VALUES' . implode(',', $xmldata);

    mysql_query($query) or die(mysql_error());

    echo "inserted into mysql<br /><br />";

    print_r($xmldata);

}

?>

but the id is auto_increment.

但id是auto_increment。

the output of this code = 4 rows into db + {starting connected to DB

此代码的输出= 4行到db + {开始连接到DB

xml loaded

xml parsed

inserted into mysql

插入mysql

Array ( [0] => ("", "2", "1", "1", "2", "2", "2", "2", "costa blanca", "spain", "1", "2d2", "2", "1", "1", "mmmmm", "nnnn", "mmmbbbbbbbbbbbbbbb b bbm", "", "dd", "222222222.00", "1", "0", "0.00", "2222222222.00", "3" ,"5", "1", "", "") ) xml parsed

数组([0] =>(“”,“2”,“1”,“1”,“2”,“2”,“2”,“2”,“costa blanca”,“西班牙”,“1 “,”2d2“,”2“,”1“,”1“,”mmmmm“,”nnnn“,”mmmbbbbbbbbbbbbbbbb bbm“,”“,”dd“,”222222222.00“,”1“,”0“ ,“0.00”,“2222222222.00”,“3”,“5”,“1”,“”,“”))xml解析

inserted into mysql

插入mysql

Array ( [0] => ("", "2", "1", "1", "2", "2", "2", "2", "costa blanca", "spain", "1", "2d2", "2", "1", "1", "mmmmm", "nnnn", "mmmbbbbbbbbbbbbbbb b bbm", "", "dd", "222222222.00", "1", "0", "0.00", "2222222222.00", "3" ,"5", "1", "", "") ) xml parsed

数组([0] =>(“”,“2”,“1”,“1”,“2”,“2”,“2”,“2”,“costa blanca”,“西班牙”,“1 “,”2d2“,”2“,”1“,”1“,”mmmmm“,”nnnn“,”mmmbbbbbbbbbbbbbbbb bbm“,”“,”dd“,”222222222.00“,”1“,”0“ ,“0.00”,“2222222222.00”,“3”,“5”,“1”,“”,“”))xml解析

inserted into mysql

插入mysql

Array ( [0] => ("", "2", "1", "1", "2", "2", "2", "2", "costa blanca", "spain", "1", "2d2", "2", "1", "1", "mmmmm", "nnnn", "mmmbbbbbbbbbbbbbbb b bbm", "", "dd", "222222222.00", "1", "0", "0.00", "2222222222.00", "3" ,"5", "1", "", "") ) xml parsed

数组([0] =>(“”,“2”,“1”,“1”,“2”,“2”,“2”,“2”,“costa blanca”,“西班牙”,“1 “,”2d2“,”2“,”1“,”1“,”mmmmm“,”nnnn“,”mmmbbbbbbbbbbbbbbbb bbm“,”“,”dd“,”222222222.00“,”1“,”0“ ,“0.00”,“2222222222.00”,“3”,“5”,“1”,“”,“”))xml解析

inserted into mysql

插入mysql

Array ( [0] => ("", "2", "1", "1", "2", "2", "2", "2", "costa blanca", "spain", "1", "2d2", "2", "1", "1", "mmmmm", "nnnn", "mmmbbbbbbbbbbbbbbb b bbm", "", "dd", "222222222.00", "1", "0", "0.00", "2222222222.00", "3" ,"5", "1", "", "") )

数组([0] =>(“”,“2”,“1”,“1”,“2”,“2”,“2”,“2”,“costa blanca”,“西班牙”,“1 “,”2d2“,”2“,”1“,”1“,”mmmmm“,”nnnn“,”mmmbbbbbbbbbbbbbbbb bbm“,”“,”dd“,”222222222.00“,”1“,”0“ ,“0.00”,“2222222222.00”,“3”,“5”,“1”,“”,“”))

Records inserted: 1 }

插入的记录:1}

3 个解决方案

#1

0  

$id          = mysql_real_escape_string($xmlitem->property->id);

Use 

$id          = mysql_real_escape_string($xml->id);

instead

#2

0  

You can find a class XML2Array in the link which makes it easy to convert xml to array and vice versa.Xml2Array

你可以在链接中找到一个XML2Array类,它可以很容易地将xml转换为数组,反之亦然.Xml2Array

Usage:

$array = XML2Array::createArray(file_get_contents('path/to/xml-file.xml'));

#3

0  

Use http://php.net/manual/en/function.simplexml-load-file.php

$xml = simplexml_load_file('test.xml');

You should be able to loop through the object.

你应该能够遍历对象。

Alternatively review MySQLs solution to loading in XML files: http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

或者,查看MySQLs解决方案以加载XML文件:http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

#1

0  

$id          = mysql_real_escape_string($xmlitem->property->id);

Use 

$id          = mysql_real_escape_string($xml->id);

instead

#2

0  

You can find a class XML2Array in the link which makes it easy to convert xml to array and vice versa.Xml2Array

你可以在链接中找到一个XML2Array类,它可以很容易地将xml转换为数组,反之亦然.Xml2Array

Usage:

$array = XML2Array::createArray(file_get_contents('path/to/xml-file.xml'));

#3

0  

Use http://php.net/manual/en/function.simplexml-load-file.php

$xml = simplexml_load_file('test.xml');

You should be able to loop through the object.

你应该能够遍历对象。

Alternatively review MySQLs solution to loading in XML files: http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

或者,查看MySQLs解决方案以加载XML文件:http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

标签：

相关文章