Thursday, January 29, 2009

PHP scripts for outputting a complete database table to xml (PostgreSQL and MySQL)

The first php scripts below connects to a postgres database, queries a full table and outputs this table as an XML page.
The second script is similar but implements a MySQL database.

The goal of creating an XML from a table is that XML files are an easy way to communicate data to Adobe Flex (and Flash)

Both PHP scripts will output a xml page that looks like this:

<?xml version="1.0" encoding="iso-8859-1"?>
<entries>
<entry><column_1_name>column_1_row_1_value</column_1_name><column_2_name>column_2_row_1_value</column_2_name> .......... </entry>
<entry><column_1_name>column_1_row_2_value</column_1_name><column_2_name>column_2_row_2_value</column_2_name> .......... </entry>
........
........
</entries>

Here is the php script using PostgreSQL:

<?php
session_start();
header("Cache-control: private");
//connect to the database.
$link = pg_connect("dbname=database user=databaseuser password=databasepassword");
//Get the data
$Query = "SELECT * from tablename";
$Result = pg_query($Query); //Execute the query
$XML = "";
$NumFields = pg_num_fields($Result);
$XML .= "<?xml version="1.0" encoding="iso-8859-1"?>\n<entries>\n";
$row = true;
while ($row = pg_fetch_row($Result)){
	$XML .= "<entry>";
	for ($i=0; $i < $NumFields; $i++)
    {   
	    $XML .= "<" . pg_field_name($Result, $i) . ">" . $row[$i] . "</" . pg_field_name($Result, $i) . ">";
    }
	$XML .= "</entry>\n";
}
$XML .= "</entries>";
echo ($XML);

pg_free_result($Result);
pg_close();
?>

Here is the php script using MySQL:

<?php
session_start();
header("Cache-control: private");
define( "DATABASE_SERVER", "localhost" );
define( "DATABASE_USERNAME", "user" );
define( "DATABASE_PASSWORD", "password" );
define( "DATABASE_NAME", "database" );
//connect to the database.
$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);
mysql_select_db( DATABASE_NAME );
//Get the data
$Query = "SELECT * from table";
$Result = mysql_query( $Query );
$XML = "";
$NumFields = mysql_num_fields($Result);
$XML .= "<?xml version="1.0" encoding="iso-8859-1"?>\n";
$XML .= "<entries>\n";
$row = true;
while ($row = mysql_fetch_row($Result)){
	$XML .= "<entry>";
	for ($i=0; $i < $NumFields; $i++)
    {   
	    $XML .= "<" . mysql_field_name($Result, $i) . ">" . $row[$i] . "</" . mysql_field_name($Result, $i) . ">";
    }
	$XML .= "</entry>\n";
}
$XML .= "</entries>";
echo ($XML);

mysql_free_result($Result);
mysql_close();
?>