CODEWITHSUNDEEP
phpsqlxml
JackTheJack
JackTheJack

Reputation: 187

SQL data to XML file

Is it possible to save the below output to an XML file as its currently just displayed in the source?

$host = "localhost"; // host name
$user = "#"; // database user name
$pass = "#"; // database password
$database = "#"; // database name
// connecting to database
$connect = @mysql_connect($host,$user,$pass)or die (@mysql_error());
// selecting database
@mysql_select_db($database,$connect) or die (@mysql_error());

// default header(don't delete)
header("Content-Type: text/xml;charset=iso-8859-1");
    echo '<?xml version="1.0" encoding="UTF-8"?>
    <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';

// mytable = your content table name
$query = @mysql_query("SELECT * FROM urls");
while($row = @mysql_fetch_array($query)){
// [url] = content url
$url = $row['url'];
// [time] = content date
$date = date("Y-m-d", $row['time']);

 // NO CHANGES BELOW
        echo
        '<url>
         <loc>' . $url .'</loc>
         <lastmod>'. $date .'</lastmod>
         <changefreq>daily</changefreq>
         <priority>0.8</priority>
         </url>
        ';
    }
        echo '</urlset>';

I know I can use .htaccess to make the file be seen as an XML format however I want the data to be saved onto an actual file.

Upvotes: 0

Views: 1005

Answers (4)

Ross
Ross

Reputation: 47057

You could try changing each echo to append the line to a string variable, for example:

// Instead of
echo '<?xml version="1.0"?>';
echo '<url>';
// etc.

$xml = '<?xml version="1.0"?>';
$xml .= '<url>';
// and so on

Then use one of the file functions to save to a file. file_put_contents is a simple method:

file_put_contents("/path/to/file.xml", $xml);

A more robust solution, if you want to take this further, could be to use the DOM module to build the XML structure:

$document = new DOMDocument("1.0");

$root = $document->createElement("urlset");
$root->setAttribute("xmlns", "http://www.sitemaps.org/schemas/sitemap/0.9");
$document->appendChild($root);

while ($row = mysql_query($query)) {
    $item = $document->createElement("url");
    $root->append($item);
    // etc.
}

echo $document->saveXML();

Upvotes: 2

GordonM
GordonM

Reputation: 31770

NOTE: This answer assumes that by "save file" you mean "trigger the Save As dialog in the browser when someone views the page".

  • text/xml isn't really the correct content-type. You really should at least application/xml for generic XML or the appropriate content type for XML sub-formats such as RSS or docx.
  • If you want to trigger a file download dialog in the client browser, then you also need to send a content-disposition header that tells the browser that you want it to download the file and give a preferred filename.

There are some issues with your code that need addressing too.

  • Overuse of @ for error suppression. This is a bad idea for a huge variety of reasons. Remove the @ operators and handle any generated errors in a more robust way.
  • Your character encoding heading specifies one character set (latin-1) but your XML preamble specifies a totally different one (UTF-8). That's a recipe for disaster.

Upvotes: 1

Manse
Manse

Reputation: 38147

Using fwrite it should be straight forward :

$f = fopen('data.xml', 'w');  //open a file for writing 
fwrite($f, $myxmltext);       // write some things to it
fclose($f);                   // close it when finished

Upvotes: 0

Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

Use output buffers

ob_start();

... do everything you actually did before ...

$content = ob_get_contents();
ob_end_clean();

//Write to a file
file_put_contents('filename.xml', $content);

And thats all...

Upvotes: 0

Related Questions