Case Study : Get the XML data and save it to database (mysql)
Requirements : Webserver Packages, already installed.
OKAY, no need for trivial chit-chat. Let’s just practice 😀
Step 1 : Prepare the Database
- Create database named db_tutorial
- Create table named tb_dataxml, the structure is as shown below.
- Done!
Step 2 : Prepare the work folder
- Create folder named tutorphp in your document root
- Save all the file in this tutorial in this folder.
Step 3 : Create a connection to db script
- Type the following script,
[sourcecode language=”php”]<br /><br />
<?php<br /><br />
$host = "localhost";<br /><br />
$user = "root"; //adjust according to your mysql setting<br /><br />
$pass = ""; //adjust according to your mysql setting<br /><br />
$dbName = "db_tutorial";<br /><br />
mysql_connect($host, $user, $pass);<br /><br />
mysql_select_db($dbName)<br /><br />
or die ("Connect Failed !! : ".mysql_error());<br /><br />
?><br /><br />
[/sourcecode] - save with the name connect.php
Step 4 : Prepare the Sample XML Date
- Type the following script,
[sourcecode language=”xml”]<br /><br />
<?xml version="1.0" encoding="iso-8859-1"?><br /><br />
<data><br /><br />
<record><br /><br />
<title>Time to Go</title><br /><br />
<author>June Sushan</author><br /><br />
<publisher>Gramedia Inc</publisher><br /><br />
<date><br /><br />
<month>June</month><br /><br />
<year>1989</year><br /><br />
</date><br /><br />
</record></p>
<p> <record><br /><br />
<title>Time to Come</title><br /><br />
<author>Alan Smart</author><br /><br />
<publisher>Elexmedia Inc</publisher><br /><br />
<date><br /><br />
<month>April</month><br /><br />
<year>1989</year><br /><br />
</date><br /><br />
</record></p>
<p></data></p>
<p>[/sourcecode] - save with the name record.xml
Step 5 : Create script for loads the XML, save it database, and show the XML
- Type the following script,
[sourcecode language=”php”]<br /><br />
<?php<br /><br />
include ‘connect.php’;<br /><br />
if( !$xml = simplexml_load_file(‘record.xml’) ) //using simplexml_load_file function to load xml file<br /><br />
{<br /><br />
echo ‘load XML failed ! ‘;<br /><br />
}<br /><br />
else<br /><br />
{<br /><br />
echo ‘<h1>This is the Data</h1>’;<br /><br />
foreach( $xml as $record ) //parse the xml file into object<br /><br />
{<br /><br />
$title = $record->title; //get the childnode title<br /><br />
$author = $record->author; //get the child node author<br /><br />
$publisher = $record->publisher; //get the child node publisher<br /><br />
$month = $record->date->month; //get the child node month<br /><br />
$year = $record->date->year; //get the child node year</p>
<p> echo ‘Title : ‘.$title.’<br />’;<br /><br />
echo ‘Author : ‘.$author.’<br />’;<br /><br />
echo ‘Publisher : ‘.$publisher.’<br />’;<br /><br />
echo ‘Month : ‘.$month.’<br />’;<br /><br />
echo ‘Year : ‘.$year.’<br />’;<br /><br />
echo ‘<br>’;</p>
<p>//save to database<br /><br />
$q = "INSERT INTO tb_dataxml VALUES(”,’$title’,’$author’,’$publisher’,’$month $year’)";<br /><br />
$result = mysql_query($q);<br /><br />
}<br /><br />
if ($result) {<br /><br />
echo ‘<h2>Success Save to Database </h2>’;<br /><br />
}<br /><br />
else echo ‘<h2>Failed Save to Databaase</h2>’;<br /><br />
}<br /><br />
?> [/sourcecode] - Save with thename loadxml.php
Step 6 : Testing Code
- Go to http://localhost/tutorphp/loadxml.php. You will see the data from record.xml already parsed and showed like in the picture 😀
- Check the database, make sure that the xml data already saved in the table you’ve created on step 1
So, this is time to trivial chit-chat Ha Ha 😀 XML is extensible Markup Language.
Why XML? XML is the standar format for data communication. Because of this, XML often used on the data communication because the platform independen format 😀