Insert XML Data to MySql Table

Input XML Data

<?xml version="1.0" encoding="UTF-8"?>
 <items> 
     <item>
         <title>PHP Shopping Cart with PayPal Payment Gateway Integration</title>
         <link>http://phppot.com/php/php-shopping-cart-with-paypal-payment-gateway-integration/</link>
         <description>Shopping cart checkout with payment gateway integration (PayPal) is a most wanted article for the PHP shopping cart coders. In this article, we are going to integrate PayPal payment gateway with the shopping cart.</description> 
         <keywords>Shopping,Cart</keywords> 
     </item> 
     <item>
         <title>Responsive Contact Form with PHP</title>
         <link>http://phppot.com/php/responsive-contact-form-with-php/</link>
         <description>The responsive contact form is designed to fit various viewport in different width. I used CSS media queries to design this responsive contact form.</description> 
         <keywords>Contact-Form, Responsive</keywords> 
     </item> 
     <item>
         <title>Loading Dynamic Content on a Bootstrap Modal using jQuery</title>
         <link>http://phppot.com/jquery/loading-dynamic-content-on-a-bootstrap-modal-using-jquery/</link>
         <description>Modal window can be shown in various ways by using jQuery, Bootstrap and others. In this tutorial, we are going to show the Bootstrap modal. </description> 
         <keywords>Modal-Window, Bootstrap</keywords> 
     </item> 
 </items>

and the SQL script for creating the database table is,

CREATE TABLE IF NOT EXISTS `tbl_tutorials` (
`item_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `link` varchar(100) NOT NULL,
  `description` varchar(400) NOT NULL,
  `keywords` varchar(50) NOT NULL
);

Parse XML and Insert Data to MySQL using PHP

In this PHP code, we use simple XML parsing to load the input XML file to create the file handle. Using this file handle the XML items will be start iterated to read the child nodes. Then I create the MySQL insert query by using the data read from the XML.

After inserting the data, a message will be shown to the user about how many rows are newly added into the database. In case of any error occurrences, then the error message will be shown to the user about the cause of the error.

<?php
$conn = mysqli_connect("localhost", "root", "test", "phpsamples");

$affectedRow = 0;

$xml = simplexml_load_file("input.xml") or die("Error: Cannot create object");

foreach ($xml->children() as $row) {
    $title = $row->title;
    $link = $row->link;
    $description = $row->description;
    $keywords = $row->keywords;
    
    $sql = "INSERT INTO tbl_tutorials(title,link,description,keywords) VALUES ('" . $title . "','" . $link . "','" . $description . "','" . $keywords . "')";
    
    $result = mysqli_query($conn, $sql);
    
    if (! empty($result)) {
        $affectedRow ++;
    } else {
        $error_message = mysqli_error($conn) . "\n";
    }
}
?>
<h2>Insert XML Data to MySql Table Output</h2>
<?php
if ($affectedRow > 0) {
    $message = $affectedRow . " records inserted";
} else {
    $message = "No records inserted";
}

?>