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();
?>