I am working on XML file, which i need to read through PHP and then save that data in a database. So far i was successful. The problem is that, it is made specifically for the XML below. What i need, is a generic code which could do the same but for any kind of XML code.
Here is my XML and PHP code
<?xml version="1.0" encoding="ISO-8859-1"?>
<list>
<person>
<person_id>1</person_id>
<fname>Mikael</fname>
<lname>Ronstrom</lname>
</person>
<person>
<person_id>2</person_id>
<fname>Lars</fname>
<lname>Thalmann</lname>
</person>
<person>
<person_id>3</person_id>
<fname>Mikael</fname>
<lname>Ronstrom</lname>
</person>
<person>
<person_id>4</person_id>
<fname>Lars</fname>
<lname>Thalmann</lname>
</person>
<person>
<person_id>5</person_id>
<fname>Mikael</fname>
<lname>Ronstrom</lname>
</person>
<person>
<person_id>6</person_id>
<fname>Lars</fname>
<lname>Thalmann</lname>
</person>
</list>
here is my Php code
$con = mysql_connect("localhost","root","vertrigo");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $con);
$xml = simplexml_load_file("xml.xml");
$aa = "";
foreach($xml->children() as $child)
{
foreach($child->children() as $childs)
{
$aa .= "'".$childs ."',";
}
$aa = substr_replace($aa, "", -1);
$sql = "INSERT into tbl_xmldata (person_id,first_name,last_name) values ( $aa )";
$aa = "";
$rr = mysql_query($sql);
}
if ($rr){ echo "data successfully captured from XML and inserted in db";}else{ echo "sorry no data insertion";}
Update:
Searching on the internet, I found this class that maybe can help you:
It is called MySQL to XML - XML to MySQL and inserts XML in MySQL and exports MySQL to XML
Link: http://www.phpclasses.org/package/782-PHP-Insert-XML-in-MySQL-and-export-MySQL-to-XML.html
First idea:
As someone said in the comments of your question:
"A database has a predefined structure - tables, columns, field types,etc., so to import "any" kind of XML means that you're going to have to build some advanced functions that can build the database structures on the fly so that your PHP script can properly insert the data into the right places within that database"
And he's right, It's nearly impossible. My suggestion is to use a class for parsing the XML. This class converts the XML into an $array so then you can get any element you want for your mysql insert (I think is the best you can do).
Download PHP class "Clean XML to array" here:
http://dl.dropbox.com/u/15208254/stackoverflow/lib.xml.php
Example:
<?php
header('Content-type: text/plain');
include('lib.xml.php');
$xml = new Xml;
// PARSE
// Parse from a source into a variable (default source type : FILE)
$source = '<?xml version="1.0" encoding="utf-8"?>
<test>
<this a="b">
<is c="d">
<a e="f" g="h"> first test</a>
</is>
<is>
<b hello="world">second test</b>
</is>
</this>
</test>';
$out = $xml->parse($source, NULL);
print_r($out);
/* will output
Array
(
[this] => Array
(
[is] => Array
(
[0] => Array
(
[a] => first test
[a-ATTR] => Array
(
[e] => f
[g] => h
)
)
[1] => Array
(
[b] => second test
[b-ATTR] => Array
(
[hello] => world
)
)
)
[is-ATTR] => Array
(
[c] => d
)
)
[this-ATTR] => Array
(
[a] => b
)
)
*/
// Parse from a local file
$out = $xml->parse('myfile.xml', 'FILE');
print_r($out);
// Parse from a Web file
$out = $xml->parse('http://site.tld/myfile.xml', 'CURL');
print_r($out);
// ENCODING
// You could specify an encoding type (default : utf-8)
$out = $xml->parse('myfile.xml', 'FILE', 'ISO-8859-15');
print_r($out);
// have fun ;-)
?>
How to insert the values?
Do a for each for the elements that repeats, otherwise to access other elements just do this:
<?php
$array = array(
"foo" => "bar",
42 => 24,
"multi" => array(
"dimensional" => array(
"array" => "foo"
)
)
);
/* Values are:
* $array[42] --> outputs 24
* $array['foo'] --> outputs bar
* $array['multi']['dimensional']['array'] --> outputs foo
*/
$sql = "INSERT INTO table (id, value1, value2) VALUES ($array[42], '$array['foo']', '$array['multi']['dimensional']['array']')";
?>
I agree with folks to an extent. It is true that trying to make something like this possible is difficult but it's not impossible. If the XML you receive is standard (same number of children etc) and the xml is fairly simple like your example then it's possible.
This code is based off something similar I did a while back. Works for this file. Try it with different XML and modify if needed. (keep in mind this is old code and sort of ugly so feel free to spruce it up if you wish)
// create the database connection
$con = mysql_connect("127.0.0.1","root","lolzapassword") or die('Could not connect: ' . mysql_error());
$xml_obj = simplexml_load_file('xml.xml');
// init some stuff
$sql = array();
$fields = array();
// initial name is the table name
$table_name = $xml_obj->getName();
// Create db if one doesn't exist (this is up to you. i use test)
mysql_query('CREATE DATABASE IF NOT EXISTS test');
foreach($xml_obj->children() as $child)
{
$row = array();
foreach($child->children() as $child)
{
$field = $child->getName(); // grab the name before we convert to string
$child = trim((string)$child); // converts from SimpleXMLElement and removes extra whitespace
if($child == '')
$child = -1; // make -1 if empty
$row[$field] = "'".mysql_real_escape_string($child, $con)."'"; // save the value hooray!
// fill the field creation array for table creation
if(!isset($fields[$field])) $fields[$field] = $field.' text';
}
$sql[] = "INSERT INTO test.{$table_name} (".join(',', array_keys($row)).") values (".join(',',array_values($row)).")"; // store the row!
}
// create the table if it doesn't exist already. Since we have no idea what
// format stuff comes in I make them text. If you want to be more specific you can
// check types as best you can and make some assumptions from that.
mysql_query('CREATE TABLE IF NOT EXISTS test.'.$table_name.' ( '. join(',', $fields). ')');
// now we insert. remember, this code assumes that the xml is standard and there's
// no extra nodes or children than expected.
//print_r($sql); // if u want to see the sql commands
foreach($sql as $insert){
$result = mysql_query($insert, $con);
if(!$result)
exit("ZOMG ERROR! ".mysql_error($con));
}