In this post we have discuss one more Web development tutorial by using PHP script with Ajax. Here we will learn how to fetch data from XML file and insert into Mysql database table by using simple PHP code with Ajax JQuery. In different terms we can also say how to import XML file data into Mysql table with PHP code with JQuery Ajax. We have already discuss many topic on how to import and export data from mysql to CSV, Excel, JSON using PHP script. But we have not cover this topic. So, in this post we have make web tutorial on importing of XML data into Mysql table by using PHP Ajax and Jquery.
We all know XML stands for extensible markup language and it is used for to save and transferred data on web and this data also not only readable to human but machine also. This type of data mainly used for transferred data from one web to another on internet and it is also compatible with any programming language. Define data in XML format is very easy and we want create node one by one to store data into this format and we can easily understand this type of data. So here we have see how XML data can be inserted into Msyql table.
For Import or Insert XML file data into Msyql table, so we have make simple form for upload CSV file to server and here we have put validation for upload only xml file. After making form we want to submit XML file to server, so we have use Ajax request, so in Ajax we have use FormData() object for submit selected file to PHP script and in script it will validate file is selected or not and selected file XML or not. If selected file is XML then after we have use simplexml_load_file() PHP function, this function will convert selected XML file into Simple XML Element object. So from this object we can fetch data in our PHP by using loop and after this we will insert into Mysql table. So, this way we can Import or Insert XML file data into Mysql table by using PHP script with Ajax JQuery.
Source Code
index.php
<?php
//index.php
?>
<!DOCTYPE html>
<html>
<head>
<title>How to Import XML Data into Mysql Table Using Ajax PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<br />
<div class="container">
<div class="row">
<h2 align="center">How to Import XML Data into Mysql Table Using Ajax PHP</h2>
<br />
<div class="col-md-9" style="margin:0 auto; float:none;">
<span id="message"></span>
<form method="post" id="import_form" enctype="multipart/form-data">
<div class="form-group">
<label>Select XML File</label>
<input type="file" name="file" id="file" />
</div>
<br />
<div class="form-group">
<input type="submit" name="submit" id="submit" class="btn btn-info" value="Import" />
</div>
</form>
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#import_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"import.php",
method:"POST",
data: new FormData(this),
contentType:false,
cache:false,
processData:false,
beforeSend:function(){
$('#submit').attr('disabled','disabled'),
$('#submit').val('Importing...');
},
success:function(data)
{
$('#message').html(data);
$('#import_form')[0].reset();
$('#submit').attr('disabled', false);
$('#submit').val('Import');
}
})
setInterval(function(){
$('#message').html('');
}, 5000);
});
});
</script>
import.php
<?php
//import.php
sleep(3);
$output = '';
if(isset($_FILES['file']['name']) && $_FILES['file']['name'] != '')
{
$valid_extension = array('xml');
$file_data = explode('.', $_FILES['file']['name']);
$file_extension = end($file_data);
if(in_array($file_extension, $valid_extension))
{
$data = simplexml_load_file($_FILES['file']['tmp_name']);
$connect = new PDO('mysql:host=localhost;dbname=testing','root', '');
$query = "
INSERT INTO employee
(name, address, gender, designation, age)
VALUES(:name, :address, :gender, :designation, :age);
";
$statement = $connect->prepare($query);
for($i = 0; $i < count($data); $i++)
{
$statement->execute(
array(
':name' => $data->employee[$i]->name,
':address' => $data->employee[$i]->address,
':gender' => $data->employee[$i]->gender,
':designation' => $data->employee[$i]->designation,
':age' => $data->employee[$i]->age
)
);
}
$result = $statement->fetchAll();
if(isset($result))
{
$output = '<div class="alert alert-success">Import Data Done</div>';
}
}
else
{
$output = '<div class="alert alert-warning">Invalid File</div>';
}
}
else
{
$output = '<div class="alert alert-warning">Please Select XML File</div>';
}
echo $output;
?>
employee.xml
<employees>
<employee>
<name>William T. Strauss</name>
<address>2210 Roosevelt Road Pittsburg, KS 66762</address>
<gender>Male</gender>
<designation>Transmission technician</designation>
<age>42</age>
</employee>
<employee>
<name>David N. Bateman</name>
<address>3729 Sycamore Fork Road Miami, FL 33176</address>
<gender>Male</gender>
<designation>Vegetable cook</designation>
<age>44</age>
</employee>
<employee>
<name>Gail P. Robinson</name>
<address>991 Ashmor Drive Crookston, MN 56716</address>
<gender>Female</gender>
<designation>Personnel administrator</designation>
<age>51</age>
</employee>
<employee>
<name>James M. Cardin</name>
<address>120 Kuhl Avenue Atlanta, GA 30303</address>
<gender>Male</gender>
<designation>Switchboard operator</designation>
<age>21</age>
</employee>
<employee>
<name>Jason K. Peterson</name>
<address>1029 Lords Way Memphis, TN 38118</address>
<gender>Male</gender>
<designation>Pediatrician</designation>
<age>43</age>
</employee>
<employee>
<name>Penni G. Vazquez</name>
<address>1545 Whiteman Street Camden, NJ 08102</address>
<gender>Female</gender>
<designation>Tractor driver</designation>
<age>33</age>
</employee>
<employee>
<name>David A. Davis</name>
<address>617 Spirit Drive Port Orange, FL 32019</address>
<gender>Male</gender>
<designation>Oncology nurse</designation>
<age>34</age>
</employee>
<employee>
<name>Kimberly J. Hemingway</name>
<address>4874 Lynn Street Woburn, MA 01801</address>
<gender>Female</gender>
<designation>Leasing manager</designation>
<age>32</age>
</employee>
<employee>
<name>Corine C. Conner</name>
<address>1595 Meadowview Drive Fredericksburg, VA 22408</address>
<gender>Female</gender>
<designation>Model</designation>
<age>22</age>
</employee>
<employee>
<name>Ben A. Champagne</name>
<address>3736 Hartland Avenue Appleton, WI 54913</address>
<gender>Male</gender>
<designation>Commentator</designation>
<age>21</age>
</employee>
<employee>
<name>Mary J. Smith</name>
<address>727 Alexander Drive Arlington, TX 76011</address>
<gender>Female</gender>
<designation>Safety inspector</designation>
<age>35</age>
</employee>
<employee>
<name>Buford R. Quinn</name>
<address>2717 McDowell Street Nashville, TN 37210</address>
<gender>Male</gender>
<designation>Log debarker</designation>
<age>23</age>
</employee>
<employee>
<name>Lawrence P. Walters</name>
<address>4488 Stratford Court Rocky Mount, NC 27801</address>
<gender>Male</gender>
<designation>Mechanical drafter</designation>
<age>26</age>
</employee>
<employee>
<name>Armando T. Trainor</name>
<address>1272 Small Street New York, NY 10016</address>
<gender>Male</gender>
<designation>Clinical laboratory technologist</designation>
<age>21</age>
</employee>
<employee>
<name>Cathy H. Maldonado</name>
<address>287 Lakeland Terrace Southfield, MI 48075</address>
<gender>Female</gender>
<designation>Dipper</designation>
<age>31</age>
</employee>
<employee>
<name>Elizabeth M. Manning</name>
<address>2398 Wines Lane Houston, TX 77032</address>
<gender>Female</gender>
<designation>Hearing therapist</designation>
<age>34</age>
</employee>
<employee>
<name>Jon B. Parker</name>
<address>4317 Stuart Street Saxonsburg, PA 16056</address>
<gender>Male</gender>
<designation>Watch repairer</designation>
<age>28</age>
</employee>
<employee>
<name>Lindsey C. Myers</name>
<address>3529 Confederate Drive Earlville, NY 13332</address>
<gender>Male</gender>
<designation>Cleaner</designation>
<age>42</age>
</employee>
<employee>
<name>Stephen T. Armijo</name>
<address>2488 Confederate Drive Syracuse, NY 13202</address>
<gender>Male</gender>
<designation>Legal secretary</designation>
<age>46</age>
</employee>
<employee>
<name>Estelle A. Sawyer</name>
<address>2613 Creekside Lane Santa Barbara, CA 93178</address>
<gender>Female</gender>
<designation>Dispatcher</designation>
<age>39</age>
</employee>
</employees>
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `employee`
--
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`address` text NOT NULL,
`gender` varchar(10) NOT NULL,
`designation` varchar(100) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `employee`
--
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
We can use Ms Excel 2016. If You Really Want to convert your very deeply parameterized XML files to CSV formats you can do it manually with it. In Ms Excel there will be an option to Get Data from different source,with that browse your XML file and after that with edit option check or un-check the parameters and load that file. After loading save that file as .CSV files and it is done. Check your CSV file now. Its Done Enjoy...
ReplyDeleteHi, Thanks for this tutorial. I am having an issue, it says "Import Data Done" but the data is not imported in my database.
ReplyDeleteThanks, this is a great tutorial. I have a question how to add custom input field in this script. Let me explain the problem.
ReplyDeleteI have a product list XML file and I want to make import using this simple and efficient script.
The product table has a field Username.
To resolve this I need a custom input field where I will enter the username and import XML file for the specific member.
How to do that
thanks
Hey HOW can i import Attributes with this Method ? This isnt Working :(
ReplyDelete':name' => $data->WKR["A"],
':address' => $data->WKR["B"]
THX for your Help
I used your code, but didn't work.
ReplyDelete