Insert XML Data to MySQL Table Using PHP

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.

<pre class="prettyprint"><code>&lt;span style="font-family: inherit;">&lt;items&gt;&lt;/span>

<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.

<span style="line-height: 16px; text-align: -webkit-auto;">CREATE TABLE </span><b style="border: 0px none; color: blue; line-height: 16px; margin: 0px; outline: 0px; padding: 0px; text-align: -webkit-auto;">items</b><span style="line-height: 16px; text-align: -webkit-auto;">(</span><span style="border: 0px none; color: #cc0000; line-height: 16px; margin: 0px; outline: 0px; padding: 0px; text-align: -webkit-auto;">id</span><span style="line-height: 16px; text-align: -webkit-auto;"> INT </span><span style="border: 0px none; color: blue; line-height: 16px; margin: 0px; outline: 0px; padding: 0px; text-align: -webkit-auto;">PRIMARY KEY</span><span style="line-height: 16px; text-align: -webkit-auto;"> </span><span style="border: 0px none; color: #a64d79; line-height: 16px; margin: 0px; outline: 0px; padding: 0px; text-align: -webkit-auto;">AUTO_INCREMENT</span><span style="line-height: 16px; text-align: -webkit-auto;">, </span><span style="line-height: 16px; text-align: -webkit-auto;"><span style="color: #cc0000;">title</span> TEXT, </span><span style="border: 0px none; color: #cc0000; line-height: 16px; margin: 0px; outline: 0px; padding: 0px; text-align: -webkit-auto;">url</span><span style="line-height: 16px; text-align: -webkit-auto;"> TEXT</span><span style="line-height: 16px; text-align: -webkit-auto;">);</span>

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.