用php将XML数据插入mysql [英] insert XML data into mysql with php
问题描述
代表问题的xml文件部分(该xml文件有数百条客户记录)
Portion of xml file that represents the problem (the xml file has hundreds of customers record)
<?xml version="1.0" encoding="utf-8"?>
<test>
<customer>
<name>customer 1</name>
<address>address 1</address>
<city>city 1</city>
<state>state 1</state>
<zip>zip 1</zip>
<phone>phone 1</phone>
<buyerinfo>
<shippingaddress>
<name>ship to</name>
<address>Ship address1</address>
</shippingaddress>
</buyerinfo>
<shippingDetail>
<saletax>
<saletaxamount>2</saletaxamount>
</saletax>
</shippingDetail>
</customer>...
下面是我的代码
Below is my code
//Xml string is parsed and creates a DOM Document object
$responseDoc = new DomDocument();
$responseDoc->load('test.xml');
foreach ($responseDoc->getElementsByTagName('customer') as $customer){
$sSQL = sprintf(
"INSERT INTO customer (name, address, city, state, zip, phone, shipto, shipadderss, tax)
VALUES ('%s','%s', '%s', '%s','%s','%s', '%s','%s','%s')",
mysql_real_escape_string($customer->getElementsByTagName('name')->item(0)->nodeValue),
mysql_real_escape_string($customer->getElementsByTagName('address')->item(0)->nodeValue),
mysql_real_escape_string($customer->getElementsByTagName('city')->item(0)->nodeValue),
mysql_real_escape_string($customer->getElementsByTagName('state')->item(0)->nodeValue),
mysql_real_escape_string($customer->getElementsByTagName('zip')->item(0)->nodeValue),
mysql_real_escape_string($customer->getElementsByTagName('phone')->item(0)->nodeValue)
?
?
?
);
$rResult = mysql_query($sSQL);
if(mysql_errno() > 0)
{
printf(
'<h4 style="color: red;">Query Error:</h4>
<p>(%s) - %s</p>
<p>Query: %s</p>
<hr />',
mysql_errno(),
mysql_error(),
$sSQL
);
}
}
问题:
-
如何使用以下方法访问获取customer.buyerinfo.shippingaddress.name节点值 我的插入语句中的mysql_real_escape_string?用" ??? "
How do I get access to get customer.buyerinfo.shippingaddress.name node value using mysql_real_escape_string in my insert statement? indicated with "???"
我有两个具有相同节点名称名称" 的节点， 是 customer.name ，另一个是 customer.buyerinfo.shippingaddress.name 命名会引起问题 使用 getElementsByTagName 名称"标签获取值.
The fact that I have two nodes with the same node name "name", one is customer.name and another is customer.buyerinfo.shippingaddress.name to name make it problematic to use getElementsByTagName "name" tag to get the value.
与第一个相同，但是如何获取 saletaxamount 节点数据值?
the same as the first one but how do I get saletaxamount node data value?
请帮助.谢谢！
推荐答案
对于您拥有的XML数据，我希望使用SimpleXML扩展，它附带了您需要的所有内容，并且编写的代码并不多(这是DOMDocument的妹妹).
For the XML data you have I would prefer the SimpleXML extension, it ships with everything you need and it's not that much code to write (it is DOMDocument's little sister).
因此，对于输入数据中的每个客户，您都希望获取9个左右的值.您可以将这些值公式化为xpath:
So for each customer in the input data, you want to fetch your 9 or so values. You can formulate those values as an xpath:
$values = <<<XPATH
(
name
|address
|city
|state
|zip
|phone
|buyerinfo/shippingaddress/name
|buyerinfo/shippingaddress/address
|shippingDetail/saletax/saletaxamount
)
XPATH;
这与数据库查询类似.您创建了一个包含查询的字符串，用于Xpath语言中的XML.
This works similar to as with a database query. You create a string that contains the query, for XML in the Xpath language.
我们在SQL上也做同样的事情，因为两者应该并存，所以下面是相应的SQL模式:
And we do same for SQL as both should go hand-in-hand together, so here is is the according SQL pattern:
$pattern = <<<SQL
INSERT INTO customer
(
name, address, city, state, zip, phone, shipto, shipadderss, tax
)
VALUES
(
'%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'
)
SQL;
现在所有这些需要打开XML并指定要使用的客户元素:
All this needs now is to open the XML and specify the customer elements to work on:
$customers = simplexml_load_string($test)->customer; // "test.xml"
然后，您只需要遍历每个客户，获取值，对其进行转义，将其插入查询中并运行SQL查询(或创建一个包含多个记录的较大查询):
Then you only need to foreach over each customer, obtain the values, escape them, insert them into the query and run the SQL query (or create one larger query containing more than one record):
foreach ($customers as $customer)
{
$data = $customer->xpath($values);
$escaped = array_map('mysql_real_escape_string', $data);
$query = vsprintf($pattern, $escaped);
// you can now run the query now
// ...
}
是的，那已经是您的代码了.如您所见，利用数组，xpath和SQL，您可以在很大程度上简化此操作.
Yes, that is your code already. As you can see, Making use of arrays, xpath and SQL, you can simplify this to a great extend.
对于您的样本XML中的第一个客户，这将生成以下查询:
For the first customer in your sample-XML this then generates the following query:
INSERT INTO customer
(
name, address, city, state, zip, phone, shipto, shipadderss, tax
)
VALUES
(
'customer 1','address 1', 'city 1', 'state 1', 'zip 1', 'phone 1', 'ship to', 'Ship address1', '2'
)
整个代码示例:
$values = <<<XPATH
(
name
|address
|city
|state
|zip
|phone
|buyerinfo/shippingaddress/name
|buyerinfo/shippingaddress/address
|shippingDetail/saletax/saletaxamount
)
XPATH;
$pattern = <<<SQL
INSERT INTO customer
(
name, address, city, state, zip, phone, shipto, shipadderss, tax
)
VALUES
(
'%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'
)
SQL;
$customers = simplexml_load_string($test)->customer; // "test.xml"
foreach ($customers as $customer)
{
$data = $customer->xpath($values);
$escaped = array_map('mysql_real_escape_string', $data);
$query = vsprintf($pattern, $escaped);
// you can now run the query now
$result = mysql_query($query);
if(mysql_errno())
{
printf(
'<h4 style="color: red;">Query Error:</h4>
<p>(%s) - %s</p>
<p>Query:
<pre>%s</pre>
</p>
<hr />',
mysql_errno(),
htmlspecialchars(mysql_error()),
htmlspecialchars($query)
);
}
}
这篇关于用php将XML数据插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
