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

}

edwardmp · Accepted Answer · 2014-06-29 00:11:18Z

1

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

}

answered Jun 29, 2014 at 0:11

edwardmp

6,6316 gold badges53 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Smith Over a year ago

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?

edwardmp Over a year ago

That's just to make it clear. You can change the following: $productNamesSeparatedWithCommas = $insert->product;Then it should work.

David Smith Over a year ago

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.

Collectives™ on Stack Overflow

Extracting XML data into MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related