Extracting XML data into MySQL

Question

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();
        }

}

score 1 · Accepted Answer · answered Jun 29 '14 at 00:11

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.

Example:

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();
        }
    }

}

Going to try this now but I can see you explicitly specified the product range. The above code for product is an example. In real life I would have products with a variety of different names. How can you change is so product field can be anything? — David Smith, Jun 29 '14 at 00:49
That's just to make it clear. You can change the following: `$productNamesSeparatedWithCommas = $insert->product;`Then it should work. — edwardmp, Jun 29 '14 at 00:51
Ahhhhh fabulous! I wish I had your brain. Worked perfectly, tried one with more transactions on it. Worked perfectly too. many thanks for that +1. — David Smith, Jun 29 '14 at 01:38

Extracting XML data into MySQL

1 Answers1

Linked