How to export the data from MySQL into a XML file with PHP?

MySQLThere are a couple of ways to export data from MySQL to a XML file. This post looks at how to export the data from MySQL into a XML file with PHP.

The example code below uses the raw mysql_* functions but it should be easy enough to substitute a database library’s functions instead. It also writes the data out line by line to the XML file whereas you could buffer the whole file in memory and write it out at one go; however if the resultset is large it may be better to write it out line by line so as not to consume too much memory.

The $server, $login, $password, $db and $table variables should be obvious in their purpose.

<!--?php 
session_start();
// you can change the following details as per your database configuration
$server = 'localhost'; // host for your database
$login = 'user name'; // username for database
$password = 'password'; // paasword for database
$db = 'database name'; //database name
$tables = array("table name"); //table name

// establish database connection

$conn = mysql_connect($server, $login, $password) or die(mysql_error());
mysql_select_db($db, $conn) or die(mysql_error($conn));

$filename = "Sample_".date("Y-m-d-h-i-s-A");

header('Content-type: text/xml');
header('Content-Disposition: attachment; filename="'.$filename.'.xml"');

$text ='<?xml version="1.0" encoding="iso-8859-1"?-->
&lt;'.$db_name.'&gt;';

foreach($tables as $table){
    $result = mysql_query("SELECT * FROM  `$table`");
    $text .='
    &lt;table_'.$table.'&gt;';

    while($row=mysql_fetch_array($result)) {
        $text .=  "
        &lt;$table&gt;";
            foreach($row as $key =&gt; $val){
                if (!preg_match('#^d+$#', $key, $match)){
                    $text .=  "
                    &lt;$key&gt;$val";
                }
            }
        $text .=  "
        ";
    }

    $text .= "
    ";
}
    $text .= "
";
echo $text;
mysql_close($conn);
?&gt;
Tags: create, export, mysql, PHP, xml,

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *