CODEWITHSUNDEEP
phpmysqlxmlforeachsimplexml
Christoph C.
Christoph C.

Reputation: 938

XML to database - foreach loop to MySQL

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:

enter image description here

And here you can see what it should look like: enter image description here

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.

Upvotes: 1

Views: 344

Answers (1)

user3783243
user3783243

Reputation: 5224

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

Upvotes: 1

Related Questions