CODEWITHSUNDEEP
phpmysqlxmldatabasemysqli
Jason Chen
Jason Chen

Reputation: 2577

Storing XML data into MySQL using PHP

I have an XML file (exemel.xml) as follows:

<XMLmeta>
<Container>

    <Group>
        <Name>Persephone</Name>
        <Order>1</Order>
            <School>
                <Schoolname>Best High School</Schoolname>
                <Grade>10</Grade>
            </School>
    </Group>

 <Group>
      <Name>John</Name>
      <Order>2</Order>
      <School>
      <Schoolname>Wurst High School</Schoolname>
      <Grade>9</Grade>
      </School>
 </Group>

 </Container>
 </XMLmeta>

What I am trying to understand is how to add this data into MYSQL. Currently I know how to select the XML, but I am lost on how exactly to select my values (name, order, etc). I do not know how to insert this XML data into MYSQL.

My code

$danielbryan = new mysqli('localhost', 'root', '', 'database');
$url = 'exemel.xml';
$output = file_get_contents($url);
$xml = simplexml_load_string($output);

foreach ($xml){
 //what do i do here//
 $insert = $danielbryan->prepare('INSERT INTO mytable () VALUES(?,?,?,?)');
 $insert->execute();
}

Upvotes: 2

Views: 1094

Answers (1)

alistaircol
alistaircol

Reputation: 1453

You can iterate over $xml->Container->Group to achieve this.

You can read more about SimpleXMLElement, but basically it parses the XML document into a standard Object.

Example:

<?php
$entries = [];
foreach ($xml->Container->Group as $group) {
  $entry = [];
  $entry['name'] = $group->Name->__toString();
  $entry['order'] = $group->Order->__toString();
  $entry['school_name'] = $group->School->Schoolname->__toString();
  $entry['grade'] = $group->School->Grade->__toString();
  $entries[] = $entry;
}

Demo: https://eval.in/805278

Output:

Array
(
    [0] => Array
        (
            [name] => Persephone
            [order] => 1
            [school_name] => Best High School
            [grade] => 10
        )

    [1] => Array
        (
            [name] => John
            [order] => 2
            [school_name] => Wurst High School
            [grade] => 9
        )

)

This gives you a complete simple array format which you can then insert into a database table.

Maybe something like this:

foreach ($entries as $entry) {
  $insert = $danielbryan->prepare('INSERT INTO mytable (`name`, `order`, `school_name`, `grade`) VALUES(?,?,?)');
  $insert->bind_param("sdsd", $entry['name'], $entry['order'], $entry['school_name'], $entry['grade']);
  $insert->execute();
}

Hope this helps.

Upvotes: 1

Related Questions