This tutorial will help you to insert XML file’s contents to a MySQL Database table using php. Note- If you can’t open PHP files in browser in Ubuntu Linux See the Tutorial : http://subinsb.com/how-to-open-php-files-in-ubuntu-instead-of-downloading

This is the XML data we are going to insert in to our MySQL Table.

<items>
  <item>
   <title>Google</title>
   <url>google.com</url>
  </item>
  <item>
   <title>Google Accounts</title>
   <url>accounts.google.com</url>
  </item>
</items> 
The name of the XML file is “items.xml “.

MySQL Database Table Creation

This is the SQL code to create a table named “items” in which we will add the XML data to.

CREATE TABLE items(id INT PRIMARY KEY AUTO_INCREMENT, title TEXT, url TEXT);

Create a PHP file named “add.php” in the same directory where the XML file is. Open the add.php file in text editor and add the following code into it :

$xmlDoc = new DOMDocument();
$xmlDoc->load("items.xml");
$mysql_hostname = "hostname"; // Example : localhost
$mysql_user     = "username";
$mysql_password = "password";
$mysql_database = "database_name";

$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Oops some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Oops some thing went wrong");

$xmlObject = $xmlDoc->getElementsByTagName('item');
$itemCount = $xmlObject->length;

for ($i=0; $i < $itemCount; $i++){
  $title = $xmlObject->item($i)->getElementsByTagName('title')->item(0)->childNodes->item(0)->nodeValue;
  $link  = $xmlObject->item($i)->getElementsByTagName('url')->item(0)->childNodes->item(0)->nodeValue;
  $sql   = "INSERT INTO `my_table_name` (title, url) VALUES ('$title', '$link')";
  mysql_query($sql);
  print "Finished Item $title n<br/>";
}

We’re using the depreciated mysql_* functions in the above code, but for a simple task like this, it can be used. If you really want the latest technology, use PDO and the code will be something like this :

$xmlDoc = new DOMDocument();
$xmlDoc->load("items.xml");
$mysql_hostname = "hostname"; // Example : localhost
$mysql_user = "username";
$mysql_password = "password";
$mysql_database = "database_name";

$dbh = new PDO("mysql:dbname={$mysql_database};host={$mysql_hostname};port=3306", $mysql_user, $mysql_password);

$xmlObject = $xmlDoc->getElementsByTagName('item');
$itemCount = $xmlObject->length;

for ($i=0; $i < $itemCount; $i++){
   $title = $xmlObject->item($i)->getElementsByTagName('title')->item(0)->childNodes->item(0)->nodeValue;
   $link = $xmlObject->item($i)->getElementsByTagName('url')->item(0)->childNodes->item(0)->nodeValue;
   $sql = $dbh->prepare("INSERT INTO `my_table_name` (`title`, `url`) VALUES (?, ?)");
   $sql->execute(array(
     $title,
     $link
   ));
   print "Finished Item $title n<br/>";
}

As you see, you have to change the variables for connecting to Database. Open the file “add.php” in your website or in a localhost server Or Execute it via terminal with the command (Linux) :

php add.php

When it’s opened it will show a success message of every nodes in the items.xml file to make sure that the XML file contents are added to the MySQL Table. That’s it.