XML to database - foreach loop to MySQL

Question

I am fetching a XML-file with simpleXML and I want to store those values inside my database with a foreach loop. Here is my code:

<?php
$xml = simplexml_load_string('XML-FILE');

foreach ($xml->METADATA->DATA as $item) {
    foreach ($item->children() as $child) {
        $sel_stmt = "INSERT INTO dwh_xml (".$child->getName().") VALUES ('".$child."') ";
        directSQL($sel_stmt);
    }
}
?>

My XML file looks like this:

<xml version="1.0" encoding="utf-8">
    <METADATA>
        <DATA>
            <FIELD_1>001</FIELD_1>
            <FIELD_2>ENTRY_2</FIELD_2>
            <FIELD_3>ENTRY_3</FIELD_3>
            <FIELD_4>ENTRY_4</FIELD_4>
            <FIELD_5>ENTRY_5</FIELD_5>
            <FIELD_6>000003</FIELD_6>
            <FIELD_7>ENTRY_7</FIELD_7>
            <FIELD_8>ENTRY_8</FIELD_8>
            <FIELD_9>ENTRY_9</FIELD_9>
            <FIELD_10>ENTRY_10</FIELD_10>
            <FIELD_11>ENTRY_11</FIELD_11>
            <FIELD_12>ENTRY_12</FIELD_12>
            <FIELD_13>ENTRY_13</FIELD_13>
        </DATA>
    <METADATA>
</xml>

The import to my database is working but for each value it generates a new entry and I do not know how my above code must be changed so that it will become a single entry inside my database with one id. Here is a screenshot of my test entry:

And here you can see what it should look like:

I know that I am doing something wrong with my foreach loop and I know it may be easy for some of you guys but right now I am unable to fix it by myself. Any help would be really appreciated.

Thanks for your answer. May you share some code with me? I am using mysqli instead of PDO. Would appreciate your help. — Christoph C.
– Christoph C., Commented Dec 10, 2018 at 13:52
the one before has right, but also the loop i for date try to make it for the field...just suppose that you have more values in one field,,,and it will work — Ilia Tapia
– Ilia Tapia, Commented Dec 10, 2018 at 13:52

user3783243 · Accepted Answer · 2018-12-10 14:03:57Z

1

You need to build the full query outside the inner most foreach. Something like this will be a good start. This thread should be able to help you to get the binding to work with mysqli, MySQLI binding params using call_user_func_array. With PDO you'd just pass $params to the execute function.

foreach ($xml->METADATA->DATA as $item) {
    foreach ($item->children() as $child) {
        $cols[] = $child->getName();
        $params[] = $child->nodeValue;
    }
    if(!empty($params)) {
         $columns = implode(',', $cols);
         $placeholders = rtrim(str_repeat('?, ', count($params)), ', ');
         $sql = 'INSERT INTO dwh_xml ( ' . $columns . ') VALUES(' . $placeholders . ')';
         $stmt = $db_con->prepare($sql);
         $stmt->execute();
         unset($params, $cols, $placeholders);
    }
}

https://3v4l.org/nhJi7

answered Dec 10, 2018 at 14:03

user3783243

5,2125 gold badges27 silver badges45 bronze badges

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

3 Comments

Christoph C. Over a year ago

thanks for your help. Your code seems to work fine but I am unable to achieve an entry inside my database. I always get the error: "Column count doesn't match value count at row 1". Any idea why? Doest it has something to do with my auto increment ID column? Do you have an idea?

Nigel Ren Over a year ago

If this is using PDO - then you can pass the parameters in $stmt->execute($params);

Christoph C. Over a year ago

got it working with PDO. Thank you very much for your help.

Collectives™ on Stack Overflow

XML to database - foreach loop to 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