Tag: xml to database

Kategori
PHP

Load and Save the XML Data to Database with PHP & Mysql

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

  1. Create database named db_tutorial
  2. Create table named tb_dataxml, the structure is as shown below.

  3. Done!

Step 2 : Prepare the work folder

  1. Create folder named tutorphp in your document root
  2. Save all the file in this tutorial in this folder.

Step 3 : Create a connection to db script

  1. Type the following script,

    [sourcecode language=”php”]<br /><br />
    &lt;?php<br /><br />
    $host = &quot;localhost&quot;;<br /><br />
    $user = &quot;root&quot;; //adjust according to your mysql setting<br /><br />
    $pass = &quot;&quot;; //adjust according to your mysql setting<br /><br />
    $dbName = &quot;db_tutorial&quot;;<br /><br />
    mysql_connect($host, $user, $pass);<br /><br />
    mysql_select_db($dbName)<br /><br />
    or die (&quot;Connect Failed !! : &quot;.mysql_error());<br /><br />
    ?&gt;<br /><br />
    [/sourcecode]

  2. save with the name connect.php

Step 4 : Prepare the Sample XML Date

  1. Type the following script,

    [sourcecode language=”xml”]<br /><br />
    &lt;?xml version=&quot;1.0&quot; encoding=&quot;iso-8859-1&quot;?&gt;<br /><br />
    &lt;data&gt;<br /><br />
    &lt;record&gt;<br /><br />
    &lt;title&gt;Time to Go&lt;/title&gt;<br /><br />
    &lt;author&gt;June Sushan&lt;/author&gt;<br /><br />
    &lt;publisher&gt;Gramedia Inc&lt;/publisher&gt;<br /><br />
    &lt;date&gt;<br /><br />
    &lt;month&gt;June&lt;/month&gt;<br /><br />
    &lt;year&gt;1989&lt;/year&gt;<br /><br />
    &lt;/date&gt;<br /><br />
    &lt;/record&gt;</p>
    <p> &lt;record&gt;<br /><br />
    &lt;title&gt;Time to Come&lt;/title&gt;<br /><br />
    &lt;author&gt;Alan Smart&lt;/author&gt;<br /><br />
    &lt;publisher&gt;Elexmedia Inc&lt;/publisher&gt;<br /><br />
    &lt;date&gt;<br /><br />
    &lt;month&gt;April&lt;/month&gt;<br /><br />
    &lt;year&gt;1989&lt;/year&gt;<br /><br />
    &lt;/date&gt;<br /><br />
    &lt;/record&gt;</p>
    <p>&lt;/data&gt;</p>
    <p>[/sourcecode]

  2. save with the name record.xml

Step 5 : Create script for loads the XML, save it database, and show the XML

  1. Type the following script,

    [sourcecode language=”php”]<br /><br />
    &lt;?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 ‘&lt;h1&gt;This is the Data&lt;/h1&gt;’;<br /><br />
    foreach( $xml as $record ) //parse the xml file into object<br /><br />
    {<br /><br />
    $title = $record-&gt;title; //get the childnode title<br /><br />
    $author = $record-&gt;author; //get the child node author<br /><br />
    $publisher = $record-&gt;publisher; //get the child node publisher<br /><br />
    $month = $record-&gt;date-&gt;month; //get the child node month<br /><br />
    $year = $record-&gt;date-&gt;year; //get the child node year</p>
    <p> echo ‘Title : ‘.$title.’&lt;br /&gt;’;<br /><br />
    echo ‘Author : ‘.$author.’&lt;br /&gt;’;<br /><br />
    echo ‘Publisher : ‘.$publisher.’&lt;br /&gt;’;<br /><br />
    echo ‘Month : ‘.$month.’&lt;br /&gt;’;<br /><br />
    echo ‘Year : ‘.$year.’&lt;br /&gt;’;<br /><br />
    echo ‘&lt;br&gt;’;</p>
    <p>//save to database<br /><br />
    $q = &quot;INSERT INTO tb_dataxml VALUES(”,’$title’,’$author’,’$publisher’,’$month $year’)&quot;;<br /><br />
    $result = mysql_query($q);<br /><br />
    }<br /><br />
    if ($result) {<br /><br />
    echo ‘&lt;h2&gt;Success Save to Database &lt;/h2&gt;’;<br /><br />
    }<br /><br />
    else echo ‘&lt;h2&gt;Failed Save to Databaase&lt;/h2&gt;’;<br /><br />
    }<br /><br />
    ?&gt; [/sourcecode]

  2. Save with thename loadxml.php

Step 6 : Testing Code

  1. Go to http://localhost/tutorphp/loadxml.php. You will see the data from record.xml already parsed and showed like in the picture 😀

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