将 XML 数据提取到 MySQL [英] Extracting XML data into MySQL

问题描述

花了无数个小时试图解决这个问题，但无济于事.我得到了一大堆 XML 文件，理想情况下，我正在寻找将交易直接导入数据库的方法，这样我就不必手动执行了.

Spent countless hours trying to figure this out to no avail. I get a whole load of XML files and ideally was looking to find way of importing the transaction straight into the database so I don't have to do it manually.

我已经设法获得了下面的结果，但无法弄清楚如何使用相同的剩余详细信息来扩展产品并将它们作为单独的交易分配给客户.

I have managed to get to a point of results below, but can't figure out how to explode the products and have them assigned as individual transaction to the customer using the same remaining details.

这是我迄今为止尝试和管理的:

This is what I have tried and managed so far :

////Daily XML I get sent in this format
<trans>
    <custtrans>
        <cust>564</cust>
        <cust_name>John David</cust_name>
        <product>P1,P2,P3,P4</product>
        <internal>Yes</internal>
    </custtrans>
    <custtrans>
        <cust>877</cust>
        <cust_name>James Harris</cust_name>
        <product>P2</product>
        <internal>No</internal>
    </custtrans>
</trans>

////I'd Like the transactions to be recorded in mysql like this
cust        |cust_name      |product    |internal
564         |John David     |P1         |Yes
564         |John David     |P2         |Yes
564         |John David     |P3         |Yes
564         |John David     |P4         |Yes
877         |James Harris   |P2         |No

////This is how it is being inserted which I do NOT WANT
cust        |cust_name      |product    |internal
564         |John David     |P1,P2,P3,P4|Yes
877         |James Harris   |P2         |No

////my PHP insert statement into database
$db = new PDO($dsn, $username, $password, $options);
$xml = simplexml_load_file('http://xml.com');

foreach ($xml as $insert)
{
        try {
            $stmt = $db->prepare('INSERT INTO customers (cust,cust_name,product,internal) 
            VALUES (:cust,:cust_name,:product,:internal)');
            $stmt->execute(array(
                ':cust' => $insert ->cust,
                ':cust_name' => $insert ->cust_name,
                ':product' => $insert ->product,
                ':internal' => $insert ->internal,
            ));

        //else catch the exception and show the error.
        } catch(PDOException $e) {
            $error[] = $e->getMessage();
        }

}

推荐答案

您可以将包含以逗号分隔的产品的字符串转换为产品数组，例如使用 explode() PHP 中的函数.

You can turn the string containing the products separated by a comma to an Array of products, for example using the explode() function in PHP.

然后简单地遍历数组中的所有产品，插入每个产品.

Then simply loop through all products in the array, inserting each single product.

示例:

foreach ($xml as $insert)
{
    $productNamesSeparatedWithCommas = "P1,P2,P3,P4";

    $productNamesArray = explode(",", $productNamesSeparatedWithCommas);

    foreach ($productNamesArray as $singleProduct)
    {
        try {
            $stmt = $db->prepare('INSERT INTO customers (cust,cust_name,product,internal) 
            VALUES (:cust,:cust_name,:product,:internal)');
            $stmt->execute(array(
                ':cust' => $insert ->cust,
                ':cust_name' => $insert ->cust_name,
                ':product' => $singleProduct,
                ':internal' => $insert ->internal,
            ));

        //else catch the exception and show the error.
        } catch(PDOException $e) {
            $error[] = $e->getMessage();
        }
    }

}

这篇关于将 XML 数据提取到 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！