将XML文件导入MySQL使用一个简单的PHP脚本。

huangapple
go评论98阅读模式
英文:

Import XML file into MySQL using a simple PHP script

问题

以下是您要翻译的内容：

"我已经尝试了许多多个站点/论坛上的帮助页面，但仍然遇到困难。

我只想从在线URL/文件中简单地导入XML数据到MySQL数据库。

https://www.geeksforgeeks.org/how-to-load-xml-data-into-mysql-using-php/ 对样本数据有效，尽管我要处理的XML数据源结构不同。

这是我要处理的XML数据源...

  1. <STOREITEMS>
  2. 		<PRODUCT ITEM='sku-01' NAME='This Is Title 1'>
  3. 			<STOCK>
  4. 				In Stock
  5. 			</STOCK>
  6. 		</PRODUCT>
  7. 		
  8. 		
  9. 		
  10. 		<PRODUCT ITEM='sku-02' NAME='This Is Title 2'>
  11. 			<STOCK>
  12. 				In Stock
  13. 			</STOCK>
  14. 		</PRODUCT>
  15. 		
  16. 		
  17. 		
  18. 		<PRODUCT ITEM='sku-03' NAME='This Is Title 3'>
  19. 			<STOCK>
  20. 				No Stock.
  21. 			</STOCK>
  22. 		</PRODUCT>
  23. 	</STOREITEMS>

任何帮助将不胜感激。

我使用了geekforgeeks的以下代码...

  1. <?php
  2.  
  3. // 连接到数据库
  4. // 服务器 - localhost
  5. // 用户名 - root
  6. // 密码 - 空
  7. // 数据库名称 = xmldata
  8. $conn = mysqli_connect("localhost", "root", "empty", "xmldata");
  9.  
  10. $affectedRow = 0;
  11.  
  12. // 加载XML文件，否则检查连接
  13. $xml = simplexml_load_file("xmltest.xml")
  14.     or die("错误：无法创建对象");
  15.  
  16. // 分配值
  17. foreach ($xml->children() as $row) {
  19.     $product = $row->product;
  20.     $stock = $row->stock;
  21.      
  23.     $sql = "INSERT INTO xtrader(
  24.         SKU, stock) VALUES ('"
  25.         . $product . "',' " . $stock . "')";     
  26.     $result = mysqli_query($conn, $sql);
  27.      
  28.     if (! empty($result)) {
  29.         $affectedRow ++;
  30.     } else {
  31.         $error_message = mysqli_error($conn) . "\n";
  32.     }
  33. }
  34. ?>
  35.  
  36. <center><h2>GEEKS GOR GEEKS</h2></center>
  37. <center><h1>XML数据存储在数据库中</h1></center>
  38. <?php
  39. if ($affectedRow > 0) {
  40.     $message = $affectedRow . "条记录已插入";
  41. } else {
  42.     $message = "未插入记录";
  43. }
  44.  
  45. ?>
  46. <style>
  47.     body { 
  48.         max-width:550px;
  49.         font-family: Arial;
  50.     }
  51.     .affected-row {
  52.         background: #cae4ca;
  53.         padding: 10px;
  54.         margin-bottom: 20px;
  55.         border: #bdd6bd 1px solid;
  56.         border-radius: 2px;
  57.         color: #6e716e;
  58.     }
  59.     .error-message {
  60.         background: #eac0c0;
  61.         padding: 10px;
  62.         margin-bottom: 20px;
  63.         border: #dab2b2 1px solid;
  64.         border-radius: 2px;
  65.         color: #5d5b5b;
  66.     }
  67. </style>
  68.  
  69. <div class="affected-row">
  70.     <?php  echo $message; ?>
  71. </div>
  72.  
  73. <?php if (! empty($error_message)) { ?>
  74.  
  75. <div class="error-message">
  76.     <?php echo nl2br($error_message); ?>
  77. </div>
  78. <?php } ?>

没有导入任何内容。"

英文:

I have tried so many help pages on multpl sites/forums and am still struggling here.

I am looking to simply import XML data from an online URL/file into a MySQL database.

The https://www.geeksforgeeks.org/how-to-load-xml-data-into-mysql-using-php/ worked with the sample data though the XML data feed I have to work with is structured differently.

This is the XML data feed I have...
<?xml version="1.0" encoding="UTF-8"?>

  1. &lt;STOREITEMS&gt;
  2. 		&lt;PRODUCT ITEM=&#39;sku-01&#39; NAME=&#39;This Is Title 1&#39;&gt;
  3. 			&lt;STOCK&gt;
  4. 				In Stock
  5. 			&lt;/STOCK&gt;
  6. 		&lt;/PRODUCT&gt;
  7. 		
  8. 		
  9. 		
  10. 		&lt;PRODUCT ITEM=&#39;sku-02&#39; NAME=&#39;This Is Title 2&#39;&gt;
  11. 			&lt;STOCK&gt;
  12. 				In Stock
  13. 			&lt;/STOCK&gt;
  14. 		&lt;/PRODUCT&gt;
  15. 		
  16. 		
  17. 		
  18. 		&lt;PRODUCT ITEM=&#39;sku-03&#39; NAME=&#39;This Is Title 3&#39;&gt;
  19. 			&lt;STOCK&gt;
  20. 				No Stock.
  21. 			&lt;/STOCK&gt;
  22. 		&lt;/PRODUCT&gt;
  23. 	&lt;/STOREITEMS&gt;

Any and all help would be appreciated.

I have used the geekforgeeks code as below...

  1. &lt;?php
  2.  
  3. // Connect to database
  4. // Server - localhost
  5. // Username - root
  6. // Password - empty
  7. // Database name = xmldata
  8. $conn = mysqli_connect(&quot;localhost&quot;, &quot;root&quot;, &quot;empty&quot;, &quot;xmldata&quot;);
  9.  
  10. $affectedRow = 0;
  11.  
  12. // Load xml file else check connection
  13. $xml = simplexml_load_file(&quot;xmltest.xml&quot;)
  14.     or die(&quot;Error: Cannot create object&quot;);
  15.  
  16. // Assign values
  17. foreach ($xml-&gt;children() as $row) {
  19.     $product = $row-&gt;product;
  20.     $stock = $row-&gt;stock;
  21.      
  23. 	$sql = &quot;INSERT INTO xtrader(
  24.         SKU, stock) VALUES (&#39;&quot;
  25.         . $product . &quot;&#39;,&#39;&quot; . $stock . &quot;&#39;)&quot;;     
  26.     $result = mysqli_query($conn, $sql);
  27.      
  28.     if (! empty($result)) {
  29.         $affectedRow ++;
  30.     } else {
  31.         $error_message = mysqli_error($conn) . &quot;\n&quot;;
  32.     }
  33. }
  34. ?&gt;
  35.  
  36. &lt;center&gt;&lt;h2&gt;GEEKS GOR GEEKS&lt;/h2&gt;&lt;/center&gt;
  37. &lt;center&gt;&lt;h1&gt;XML Data storing in Database&lt;/h1&gt;&lt;/center&gt;
  38. &lt;?php
  39. if ($affectedRow &gt; 0) {
  40.     $message = $affectedRow . &quot; records inserted&quot;;
  41. } else {
  42.     $message = &quot;No records inserted&quot;;
  43. }
  44.  
  45. ?&gt;
  46. &lt;style&gt;
  47.     body { 
  48.         max-width:550px;
  49.         font-family: Arial;
  50.     }
  51.     .affected-row {
  52.         background: #cae4ca;
  53.         padding: 10px;
  54.         margin-bottom: 20px;
  55.         border: #bdd6bd 1px solid;
  56.         border-radius: 2px;
  57.         color: #6e716e;
  58.     }
  59.     .error-message {
  60.         background: #eac0c0;
  61.         padding: 10px;
  62.         margin-bottom: 20px;
  63.         border: #dab2b2 1px solid;
  64.         border-radius: 2px;
  65.         color: #5d5b5b;
  66.     }
  67. &lt;/style&gt;
  68.  
  69. &lt;div class=&quot;affected-row&quot;&gt;
  70.     &lt;?php  echo $message; ?&gt;
  71. &lt;/div&gt;
  72.  
  73. &lt;?php if (! empty($error_message)) { ?&gt;
  74.  
  75. &lt;div class=&quot;error-message&quot;&gt;
  76.     &lt;?php echo nl2br($error_message); ?&gt;
  77. &lt;/div&gt;
  78. &lt;?php } ?&gt;

Nothing is importing.

答案1

得分: 1

$product = $row->product;

因为&lt;PRODUCT&gt;元素本身就是一行，所以没有$row->product。您想要的是该元素中名为ITEM的属性：

  1. $attributes = $row->attributes();
  2. $sku = (string) $attributes['ITEM'];

或者：

  1. $sku = (string) $row->attributes()['ITEM'];

或者只需：

  1. $sku = (string) $row['ITEM'];

还要注意，$row->STOCK将获取该属性中的所有内容，包括空格：

  1. $stock = (string) $row->STOCK;
  2. var_dump($stock);

会得到：

  1. string(38) "
  2.                 In Stock
  3.             "

这可能不是您想要的，所以使用trim()去除多余的空格：

  1. $stock = trim((string) $row->STOCK);

其他注意事项：

  • 您的某些项目使用No Stock，而某些项目使用No Stock.带有句点。如果您基于此设置条件，可能会遇到问题。此值可能应该是一个简单的整数表示，因此“无库存”条件将简单为<STOCK>0</STOCK>。
  • 如果您将SKU和NAME放在属性中，最好将库存也放在属性中，如<PRODUCT ITEM='sku-01' NAME='This Is Title 1' STOCK='0'>。将库存作为单独的元素放置的唯一原因是，如果每个项目可以有多个值，这在这种情况下似乎没有意义。
  • 不要忽略SQL注入问题，这是一个重要问题。
英文: 
  1. $product = $row-&gt;product;

There is no $row-&gt;product because the &lt;PRODUCT&gt; element is itself the row. You want the attribute named ITEM in that element:

  1. $attributes = $row-&gt;attributes();
  2. $sku = (string) $attributes[&#39;ITEM&#39;];

Or:

  1. $sku = (string) $row-&gt;attributes()[&#39;ITEM&#39;];

Or just:

  1. $sku = (string) $row[&#39;ITEM&#39;];

Also note that $row-&gt;STOCK will get you everything in that attribute, including whitespace:

  1. $stock = (string) $row-&gt;STOCK;
  2. var_dump($stock);

Yields:

  1. string(38) &quot;
  2.             In Stock
  3.         &quot;

This is probably not what you want, so trim() the excess:

  1. $stock = trim((string) $row-&gt;STOCK);

Other concerns:

  • Some of your items use No Stock and some use No Stock. with a period. If you base conditionals on this, you're gonna have a bad time. This value probably should be a simple integer representation, so the "no stock" condition would be simply &lt;STOCK&gt;0&lt;/STOCK&gt;.
  • If you're going to put the SKU and NAME in attributes, you might as well put the stock in an attribute too, like &lt;PRODUCT ITEM=&#39;sku-01&#39; NAME=&#39;This Is Title 1&#39; STOCK=&#39;0&#39;&gt;. The only reason you'd put the stock as a separate element is if you can have more than one value per item, which doesn't seem to make sense here.
  • Don't ignore your SQL injections, that's a big deal.

huangapple
  • 本文由 发表于 2023年3月21日 00:35:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/75792964.html
  • import
  • php
  • simplexml
  • xml
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定