SQLite to XML via PHP

Question

Hi,

I am sure this can be done. I need to pull data from a SQLite db & convert it to XML on the fly, so it can be read by fusioncharts to display pretty graphs.

I have seen it done with mysql but am finding it difficult to find information about SQlite to XML.

<?php 

header("Content-type: text/xml"); 

$host = "localhost"; 
$user = "root"; 
$pass = ""; 
$database = "test"; 

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host."); 
mysql_select_db($database, $linkID) or die("Could not find database."); 

$query = "SELECT * FROM blog ORDER BY date DESC"; 
$resultID = mysql_query($query, $linkID) or die("Data not found."); 

$xml_output = "<?xml version=\"1.0\"?>\n"; 
$xml_output .= "<entries>\n"; 

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){ 
    $row = mysql_fetch_assoc($resultID); 
    $xml_output .= "\t<entry>\n"; 
    $xml_output .= "\t\t<date>" . $row['date'] . "</date>\n"; 
        // Escaping illegal characters 
        $row['text'] = str_replace("&", "&", $row['text']); 
        $row['text'] = str_replace("<", "<", $row['text']); 
        $row['text'] = str_replace(">", "&gt;", $row['text']); 
        $row['text'] = str_replace("\"", "&quot;", $row['text']); 
    $xml_output .= "\t\t<text>" . $row['text'] . "</text>\n"; 
    $xml_output .= "\t</entry>\n"; 
} 

$xml_output .= "</entries>"; 

echo $xml_output; 

Taken from http://www.kirupa.com/web/mysql_xml_php.htm

Thanks in advance.

Answer 1

Here is the a version of the code you posted, but using SQLite instead of MySQL:

<?php

header("Content-type: text/xml");

$dbhandle = sqlite_open('sqlitedb');
$query    = sqlite_query($dbhandle, 'SELECT * FROM blog ORDER BY date DESC');
$result   = sqlite_fetch_all($query, SQLITE_ASSOC);

try {
    $db = new PDO("sqlite:/path/to/database.db");
} catch(PDOException $e) {
    die('Unable to connect to database: ' . $e->getMessage());
}
$query = $db->prepare("SELECT * FROM blog ORDER BY date DESC");
$query->execute();
$result = $query->fetchAll();

$xml_output  = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<entries>\n";
foreach ($result as $row) {
    $xml_output .= "\t<entry>\n";
    $xml_output .= "\t\t<date>" . $row['date'] . "</date>\n";
    // Escaping illegal characters
    $row['text'] = str_replace("&", "&", $row['text']);
    $row['text'] = str_replace("<", "<", $row['text']);
    $row['text'] = str_replace(">", "&gt;", $row['text']);
    $row['text'] = str_replace("\"", "&quot;", $row['text']);
    $xml_output .= "\t\t<text>" . $row['text'] . "</text>\n";
    $xml_output .= "\t</entry>\n";
}

$xml_output .= "</entries>";
$db = null;

Here is the PHP documentation for PDO for more information.

Here is a tutorial for using PDO.

EDIT: Changed code to use PDO instead of the sqlite_* functions.