I'm having an issue with trying to insert my XML data into my database. I've spent the past two days researching on google but I'm still struggling. If anyone could help that would be most appreciated. Thank you very much.
I get the following error messages when I try to insert the data:
Notice: Trying to get property of non-object in insert_into_database.php on line 13
Fatal error: Call to a member function item() on null in insert_into_database.php on line 13
insert_into_database.php
<?php
$db = new PDO('mysql:host=localhost;dbname=data', 'root', '');
$xmldoc = new DOMDocument();
$xmldoc->load('data.xml');
$xmldata = $xmldoc->getElementsByTagName('group');
$xmlcount = $xmldata->length;
for ($i=0; $i < $xmlcount; $i++) {
$id = $xmldata->item($i)->getElementsByTagName('id')->item(0)->childNodes->item(0)->nodeValue;
$group = $xmldata->item($i)->getElementsByTagName('group')->item(0)->childNodes->item(0)->nodeValue;
$category = $xmldata->item($i)->getElementsByTagName('category')->item(0)->childNodes->item(0)->nodeValue;
$question = $xmldata->item($i)->getElementsByTagName('question')->item(0)->childNodes->item(0)->nodeValue;
$a = $xmldata->item($i)->getElementsByTagName('a')->item(0)->childNodes->item(0)->nodeValue;
$b = $xmldata->item($i)->getElementsByTagName('b')->item(0)->childNodes->item(0)->nodeValue;
$c = $xmldata->item($i)->getElementsByTagName('c')->item(0)->childNodes->item(0)->nodeValue;
$d = $xmldata->item($i)->getElementsByTagName('d')->item(0)->childNodes->item(0)->nodeValue;
$stmt = $db->prepare("insert into xml values(?,?,?,?,?,?,?,?)");
$stmt->bindParam(1,$group);
$stmt->bindParam(2,$category);
$stmt->bindParam(3,$question);
$stmt->bindParam(4,$a);
$stmt->bindParam(5,$b);
$stmt->bindParam(6,$c);
$stmt->bindParam(7,$d);
$stmt->execute();
printf($name.'<br />');
}
?>
data.xml
<?xml version="1.0" encoding="UTF-8"?>
<questions>
<group name="Question Group 1">
<id>1</id>
<category>Category A</category>
<question name="Question 1" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 2" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 3" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 4" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 5" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
</group>
<group name="Question Group 2">
<id>2</id>
<category>Category B</category>
<question name="Question 1" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 2" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 3" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 4" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 5" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
</group>
<group name="Question Group 3">
<id>3</id>
<category>Category C</category>
<question name="Question 1" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 2" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 3" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 4" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question name="Question 5" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
</group>
</questions>
If you only want to get the group, then do this:
$group = $xmldata->item($i)->getAttribute('name');
Overall, you need to change your code like this:
$db = new PDO('mysql:host=localhost;dbname=data', 'root', '');
$xmldoc = new DOMDocument();
$xmldoc->load('data.xml');
$xmldata = $xmldoc->getElementsByTagName('group');
$xmlcount = $xmldata->length;
for($i = 0; $i < $xmlcount; ++$i){
$id = $xmldata->item($i)->getElementsByTagName('id')->item(0)->nodeValue;
$group = $xmldata->item($i)->getAttribute('name');
$category = $xmldata->item($i)->getElementsByTagName('category')->item(0)->nodeValue;
$questioncount = $xmldata->item(0)->getElementsByTagName('question')->length;
for($j = 0; $j < $questioncount; ++$j){
$question = $xmldata->item($i)->getElementsByTagName('question')->item($j)->getAttribute('name');
$a = $xmldata->item($i)->getElementsByTagName('question')->item($j)->getAttribute('a');
$b = $xmldata->item($i)->getElementsByTagName('question')->item($j)->getAttribute('b');
$c = $xmldata->item($i)->getElementsByTagName('question')->item($j)->getAttribute('c');
$d = $xmldata->item($i)->getElementsByTagName('question')->item($j)->getAttribute('d');
// Do your database operations
}
}
Consider using MySQL's LOAD XML LOCAL INFILE command for bulk import of XML document. This avoids any looping. However, the structure must adhere to following types:
<row column1="value1" column2="value2" .../>
<row>
<column1>value1</column1>
<column2>value2</column2>
</row>
<row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>
Hence, consider also XSLT (the transformation language to manipulate XML documents to various end use structures). Since most data resides in attributes, below XSLT (embedded as string but can be external file) transforms to first structure:
// LOAD XML AND XSL SOURCES
$doc = new DOMDocument();
$doc->load('data.xml');
$xsl = new DOMDocument;
$xslstr = '<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="UTF-8" indent="yes" />
<xsl:strip-space elements="*"/>
<xsl:template match="/questions">
<xsl:copy>
<xsl:apply-templates select="group"/>
</xsl:copy>
</xsl:template>
<xsl:template match="group">
<xsl:apply-templates select="question"/>
</xsl:template>
<xsl:template match="question">
<xsl:copy>
<xsl:attribute name="id"><xsl:value-of select="ancestor::group/id"/></xsl:attribute>
<xsl:attribute name="group"><xsl:value-of select="ancestor::group/@name"/></xsl:attribute>
<xsl:attribute name="category"><xsl:value-of select="ancestor::group/category"/></xsl:attribute>
<xsl:attribute name="question"><xsl:value-of select="@name"/></xsl:attribute>
<xsl:copy-of select="@a|@b|@c|@d"/>
</xsl:copy>
</xsl:template>
</xsl:transform>';
$xsl->loadXML($xslstr);
// CONFIGURE TRANSFORMER (ENABLE .php_xsl EXTENSION)
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl);
// TRANSFORM SOURCE
$newXml = $proc->transformToXML($doc);
// SAVE TO FILE
$xmlfile = 'output.xml';
file_put_contents($xmlfile, $newXml);
// RUN MYSQL COMMAND (MAY NEED TO ALLOW --local-infile IN SETTINGS)
try {
$db = new PDO('mysql:host=localhost;dbname=data', 'root', '');
$db->execute("LOAD XML DATA INFILE 'path/to/output.xml'
INTO TABLE xml
ROWS IDENTIFIED BY '<question>';");
} catch(PDOException $e) {
echo $e->getMessage();
}
Transformed XML (attribute names must match database fields)
<?xml version="1.0" encoding="UTF-8"?>
<questions>
<question id="1" group="Question Group 1" category="Category A" question="Question 1" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="1" group="Question Group 1" category="Category A" question="Question 2" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="1" group="Question Group 1" category="Category A" question="Question 3" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="1" group="Question Group 1" category="Category A" question="Question 4" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="1" group="Question Group 1" category="Category A" question="Question 5" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="2" group="Question Group 2" category="Category B" question="Question 1" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="2" group="Question Group 2" category="Category B" question="Question 2" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="2" group="Question Group 2" category="Category B" question="Question 3" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="2" group="Question Group 2" category="Category B" question="Question 4" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="2" group="Question Group 2" category="Category B" question="Question 5" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="3" group="Question Group 3" category="Category C" question="Question 1" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="3" group="Question Group 3" category="Category C" question="Question 2" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="3" group="Question Group 3" category="Category C" question="Question 3" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="3" group="Question Group 3" category="Category C" question="Question 4" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
<question id="3" group="Question Group 3" category="Category C" question="Question 5" a="Answer 1" b="Answer 2" c="Answer 3" d="Answer 4"/>
</questions>