Create XML file from mysql table


Hello Experts today i will discus about a XML write function and this XML value come from mysql table. Its very simple for use. Here is a sample mysql table and steps are described. I think it will be helpful for Experts…

<?php
/**
* writeXML : Converting mysql table into XML
*
* @author : S.M. Saidur Rahman ,
* Moderator, joomla_experts (http://tech.groups.yahoo.com/group/joomla_experts/)
Moderator, cakephpexperts (http://tech.groups.yahoo.com/group/cakephpexperts/)
* URL: https://ranawd.wordpress.com/
* @version : 1.0
* @date 2008-07-09
* Purpose : Write XML file and collect data from mysql table
*/

/*
@Steps:
#Create a table name “sampletable”
#Create a XML file name “sample.xml”
#Correct root path as define value
#Call this function and Enjoy!
*/

//Here is an example of mysql table
CREATE TABLE `sampletable` (
`id` int(11) NOT NULL auto_increment,
`image` varchar(255) collate latin1_general_ci NOT NULL,
`thumbnail` varchar(255) collate latin1_general_ci NOT NULL,
`caption` varchar(150) collate latin1_general_ci NOT NULL,
`ordering` int(11) NOT NULL default ‘0’,
PRIMARY KEY (`id`)
)

//Define XML file root path
define(‘ROOT_PATH’, $_SERVER[‘DOCUMENT_ROOT’]);

//XML write function
function writeXML($xmlfile, $tablename)
{
$xmlfile_path= ROOT_PATH.”/”.$xmlfile;
$query_xmlw = “SELECT * FROM $tablename ORDER BY ordering DESC”;
$result_xmlw = mysql_query($query_xmlw);

$xmlstore=”;
$xmlstore .=”;

while($row_xmlw = mysql_fetch_array($result_xmlw)) {
$xmlstore .=”.$row_xmlw[‘image’].”;
$xmlstore .=”.$row_xmlw[‘thumbnail’].”;
$xmlstore .=’

‘.$row_xmlw[‘caption’].’

‘;
$xmlstore .=”;
}

$xmlstore .=”;

$handle = fopen($xmlfile_path, ‘w’);

fwrite($handle, $xmlstore);
}

//Put XML file name and mysql table name simultaniously
writeXML(‘sample.xml’, ‘sampletable’);
?>

Advertisements

XMLToArray Generator Class


Here is a PHP class that read xml file and make an array. First save this class file as xmltoarray.php and then create an object like new XmlToArray($xml_data). Finally just call createArray() function. It will be helpful when we don’t want to use database or any kind of web services. Bellow i have given full source code. I think it will be helpful.

<?
/**
* XMLToArray : Converting XML into Array
*
* @author  :  MA Razzaque Rupom <rupom_315@yahoo.com>, <rupom.bd@gmail.com>
*             Moderator, phpResource (http://groups.yahoo.com/group/phpresource/)
*             URL: http://www.rupom.info
* @version :  1.0
* @date       11/03/2006
* Purpose  :  Creating Hierarchical Array from XML Data
*/

class XmlToArray
{

private $xml=”; //To use in PHP4, use “var”

/**
* Default Constructor
* @param $xml = xml data
* @return none
*/

function XmlToArray($xml)
{
$this->xml = $xml;
}

/**
* _struct_to_array($values, &$i)
*
* This adds the contents of the return xml into the array for easier processing.
* Recursive, Static
*
* @access    private
* @param    array  $values this is the xml data in an array
* @param    int    $i  this is the current location in the array
* @return    Array
*/

function _struct_to_array($values, &$i)
{
$child = array();

if (isset($values[$i][‘value’]))
{
array_push($child, $values[$i][‘value’]);
}

while ($i++ < count($values))
{
switch ($values[$i][‘type’])
{
case ‘cdata’:
array_push($child, $values[$i][‘value’]);
break;

case ‘complete’:
$name = $values[$i][‘tag’];
if(!empty($name))
{
$child[$name]= ($values[$i][‘value’])?($values[$i][‘value’]):”;

if(isset($values[$i][‘attributes’]))
{
$child[$name] = $values[$i][‘attributes’];
}
}
break;

case ‘open’:
$name = $values[$i][‘tag’];
$size = isset($child[$name]) ? sizeof($child[$name]) : 0;
$child[$name][$size] = $this->_struct_to_array($values, $i);
break;

case ‘close’:
return $child;
break;
}
}
return $child;

}//EO _struct_to_array

/**
* createArray($data)
*
* This adds the contents of the return xml into the array for easier processing.
*
* @access    public
* @param    string    $data this is the string of the xml data
* @return    Array
*/
function createArray()
{
$xml    = $this->xml;
$values = array();
$index  = array();
$array  = array();
$parser = xml_parser_create();
xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, 1);
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
xml_parse_into_struct($parser, $xml, $values, $index);
xml_parser_free($parser);
$i = 0;
$name = $values[$i][‘tag’];
$array[$name] = isset($values[$i][‘attributes’]) ? $values[$i][‘attributes’] : ”;
$array[$name] = $this->_struct_to_array($values, $i);
return $array;
}//createArray

}//EO Class XmlToArray

?>

<?php

/**
* phpResourceRssFeed : phpResource Feeds for Your Site
*
* @author  :  MA Razzaque Rupom <rupom_315@yahoo.com>, <rupom.bd@gmail.com>
*             Moderator, phpResource (http://groups.yahoo.com/group/phpresource/)
*             URL: http://www.rupom.info
* @version :  1.0
* @date       11/03/2006
*/

?>
<title>phpResource Feeds for Your Site</title>
<?php

require_once(“XmlToArray.class.php”);

$xml_data = file_get_contents(“http://rss.groups.yahoo.com/group/phpresource/rss&#8221;);

//Creating Instance of the Class
$xmlObj    = new XmlToArray($xml_data);

//Creating Array
$arrayData = $xmlObj->createArray();

$rssData = array();

$rssData = $arrayData[‘rss’][‘channel’][0][‘item’];

$colors = array(‘#CCCCCC’,’#f5f5f5′);

$numColors = count($colors);

if(!empty($rssData))
{
?>
<table align=”left”>
<tr>
<td>&nbsp;</td>
</tr>
<tr bgcolor=”#6699CC”>
<td colspan=”2″><font size=”5″><b>Latest Posts of <a href=”http://groups.yahoo.com/group/phpresource/”>phpResource Group</a></b></font></td>
</tr>
<tr>
<td colspan=”2″>&nbsp;</td>
</tr>
<?php
$i = 0;
foreach($rssData as $i=>$v)
{

$title   = $v[‘title’];
$link    = $v[‘link’];
$author  = $v[‘author’];
$pubDate = $v[‘pubDate’];
$desc    = $v[‘description’];

?>

<tr bgcolor=”<?=$colors[$i%$numColors];?>”>
<td><a href=”<?=$link?>”><font size=”4″><?=$title?></font></a><br />&nbsp;&nbsp;&nbsp;On <?=$pubDate?> By <b><?=$author?></b></td>
</tr>
<tr>
<td>
<?php

$desc = wordwrap( $desc, 72, “<br>”);
echo “$desc …. “;

//echo $desc.’ <b>…</b> ‘;
?>
<br />
<a href=”<?=$link?>”>Read More…</a>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<?php
$i++;
}
}

function dBug($arr)
{
echo “<PRE>”;
print_r($arr);
echo “</PRE>”;
}
?>

<tr bgcolor=”#6699CC”>
<td><a href=”http://groups.yahoo.com/group/phpresource/join/”><font size=”5″>Click Here to Join phpResource</font></a></td>
</tr>

</table>

Write The XML File


Recently I have made a function that write xml file and bring data from mysql database. Its very easy to use. First put this function and call that function after mysql row insert, update or delete. Finally we may use this xml for different purpose. I think it will be helpful for someone.

function xmlfilewrite($xmlfile, $tablename)
{
// Write The XML File
$xmlfile_path= ROOT_PATH.”/”.$xmlfile; // ROOT_PATH like define(‘ROOT_PATH’, $_SERVER[‘DOCUMENT_ROOT’].”/sitename”);
$query_xmlw = “SELECT * FROM $tablename ORDER BY ordering DESC”;
$result_xmlw = mysql_query($query_xmlw);

$xmlstore='<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>’;
$xmlstore .='<images>’;

while($row_xmlw = mysql_fetch_array($result_xmlw)) {
$xmlstore .='<pic><image>’.$row_xmlw[‘image’].'</image>’;
$xmlstore .='<thumbnail>’.$row_xmlw[‘thumbnail’].'</thumbnail>’;
$xmlstore .='<caption>’.$row_xmlw[‘caption’].'</caption>’; //Create XML with caption
$xmlstore .='</pic>’;
}

$xmlstore .='</images>’;
$handle = fopen($xmlfile_path, ‘w’);
fwrite($handle, $xmlstore);
}

$this->xmlfilewrite(‘xmlfile.xml’, ‘tablename’); // write the xml file
?>

Here the MySql table i have used
CREATE TABLE `tablename` (
`id` int(11) NOT NULL auto_increment,
`image` varchar(255) collate latin1_general_ci NOT NULL,
`thumbnail` varchar(255) collate latin1_general_ci NOT NULL,
`caption` varchar(150) collate latin1_general_ci NOT NULL,
`ordering` int(11) NOT NULL default ‘0’,
PRIMARY KEY (`id`)
) ;

And finally create this type XML
<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<images>
<pic>
<image>1211873814.jpg</image>
<thumbnail>thumb_1211873814.jpg</thumbnail>
<caption>Test Image</caption>
</pic>
</images>