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

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

  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 πŸ˜€

Oleh littleflow3r

obviously, a girl. with sweet smile, off course. turning her 21 years trapped on Informatics departement and just started to having some crush with it lately. she uses wordpress. she loves php. she loves to satisfied her shopping hobbies just via looking around in online shop. oh yeah, she got an A for her english subject^^