« Return

Generate XML/RSS file from MySQL query results

Query a database and fetch the results wrapping it in the correct ( or necessary ) XML schema. Option 1 uses PEAR DB package, Option 2 I code it with mysqli object oriented style.

file 1 ( index.php ) / OPTION 1

<?php
  /* uncomment these lines and comment out line 25 to generate/download XML
# ------------------------------------------------------------------------------------------
  $filename = time();
  header("Content-type: text/xml");
  header("Content-Disposition: attachment; filename=$filename");
    
  header("Pragma: no-cache"); // HTTP/1.0
  header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
  header("Expires: Sat Jun 27 23:25:37 PDT 2009"); // set a date in the past
  */
  
# ------------------------------------------------------------------------------------------
  
require_once ('DB.php');
$db = DB::connect("mysql://username:password@localhost/database_name");
if(DB::isError($db)): die($db->getMessage()); endif;

# ------------------------------------------------------------------------------------------

$sql = "SELECT * FROM register ORDER BY date_created";
$result = $db->query($sql);

echo '<?xml version="1.0" encoding="UTF-8" ?>' . "\n";
echo '<rss version="2.0">' . "\n\n";
echo "<channel>" . "\n\n";

while ($result->fetchInto($row)):

    foreach($row as $key => $value) { // encode htmlspecialchars in the array
    $row[$key] = htmlspecialchars($value, ENT_QUOTES, 'utf-8', false); 
   }
   
echo "<item>" . "\n";
echo "\t" . "<entry_id>".$row[0]."</entry_id>" . "\n";
echo "\t" . "<title>".$row[1]."</title>" . "\n";
echo "\t" . "<description>".$row[2]."</description>" . "\n";
echo "\t" . "<date_created>".$row[3]."</date_created>" . "\n";
echo "</item>" . "\n\n";
endwhile;

echo "</channel>";
$db->disconnect();
?>

file 1 ( index.php ) / OPTION 2

<?php
	$filename = time();
  header("Content-type: text/xml");
	header("Content-Disposition: attachment; filename=$filename");
    
	header("Pragma: no-cache"); // HTTP/1.0
	header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
	header("Expires: Sat Jun 27 23:25:37 PDT 2009"); // set a date in the past
  
# ------------------------------------------------------------------------------------------

$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if ($mysqli->connect_errno):
    die('Connect Error: ' . $mysqli->connect_errno);
endif;

# ------------------------------------------------------------------------------------------

$sql = "SELECT * FROM register ORDER BY date_created";
$result = $mysqli->query($sql);

echo '<?xml version="1.0" encoding="UTF-8" ?>' . "\n";
echo "<root>" . "\n";

while ($row = $result->fetch_row()):

    foreach($row as $key => $value) { // encode htmlspecialchars in the array
    $row[$key] = htmlspecialchars($value, ENT_QUOTES, 'utf-8', false); 
   }
   
echo "\t" . "<item>" . "\n";
echo "\t\t" . "<entry_id>".$row[0]."</entry_id>" . "\n";
echo "\t\t" . "<title>".$row[1]."</title>" . "\n";
echo "\t\t" . "<description>".$row[2]."</description>" . "\n";
echo "\t\t" . "<date_created>".$row[3]."</date_created>" . "\n";
echo "\t" . "</item>" . "\n";
endwhile;

echo "</root>";
$mysqli->close();
?>