MYSQL to xml
Posted: Fri Apr 15, 2011 1:12 pm
hi
I was checking on the code to extract data from mysql and revert it back in xml form.
I have been testing a few codes that i found online but none of them works perfectly...
#1 code
<?php
DEFINE ('DB_USER', 'abc');
DEFINE ('DB_PASSWORD', 'abc');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'abc');
// No closing PHP tag to avoid 'headers already sent' errors.
//////////////////////////////////////////////////////////////////////
// Send the content-type header:
header('Content-Type: text/xml');
// Start the XML:
echo '<?xml version="1.0" encoding="utf-8" ?>
<employees>
';
// Include the database information script:
require_once('mysql.inc.php');
// Connect to the database:
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// If a connection was established, run the query:
if ($dbc) {
// Define the query:
$q = "SELECT * FROM user where datetime like '2011-04-%'";
// Run the query:
$r = mysqli_query($dbc, $q);
// Confirm that some rows were returned:
if (mysqli_num_rows($r) > 0) {
// Fetch every row and print it as XML:
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
echo "<user>
<status>{$row['status']}</status>
<datetime>{$row['datetime']}</datetime>
</user>
";
} // End of WHILE loop.
} // End of mysqli_num_rows() IF.
} // End of $dbc IF.
// Complete the XML:
echo '</user>';
?>
and the output.. i am getting this error :s
XML Parsing Error: no element found
Location: http://test.com/soccc.php
Line Number 7, Column 1:
i am wondering... if i am missing this file ? "require_once('mysql.inc.php');"
code #2
this code seems good but getting some minor error AND biggest part of failure is.. it is not in form of xml
<?php
error_reporting(E_ALL);
// --------------------------- Configuration section
// Fill in according to your db settings (example is for a local and locked "play" WAMP server)
// Make sure to keep the ; and the ""
$host = "localhost";
$user = "abc";
$pass = "abc";
$database = "abc";
// Replace by a query that matches your database
$SQL_query = "SELECT * FROM userwhere datetime like '2011-04-%'";
// Optional: add the name of XSLT file.
// $xslt_file = "mysql-result.xsl";
// -------------------------- no changes needed below
$DB_link = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $DB_link) or die ("Could not find or access the database.");
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");
// we produce XML
header("Content-type: text/xml");
$XML = "<?xml version=\"1.0\"?>\n";
if ($xslt_file) $XML .= "<?xml-stylesheet href=\"$xslt_file\" type=\"text/xsl\" ?>";
// root node
$XML .= "<result>\n";
// rows
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$XML .= "\t<row>\n";
$i = 0;
// cells
foreach ($row as $cell) {
// Escaping illegal characters - not tested actually
$cell = str_replace("&", "&", $cell);
$cell = str_replace("<", "<", $cell);
$cell = str_replace(">", ">", $cell);
$cell = str_replace("\"", """, $cell);
$col_name = mysql_field_name($result,$i);
// creates the "<tag>contents</tag>" representing the column
$XML .= "\t\t<" . $col_name . ">" . $cell . "</" . $col_name . ">\n";
$i++;
}
$XML .= "\t</row>\n";
}
$XML .= "</result>\n";
// output the whole XML string
echo $XML;
?>
Can anyone here please point out the issue for this ? or is there any further reference i can take for such work ?
thank you
I was checking on the code to extract data from mysql and revert it back in xml form.
I have been testing a few codes that i found online but none of them works perfectly...
#1 code
<?php
DEFINE ('DB_USER', 'abc');
DEFINE ('DB_PASSWORD', 'abc');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'abc');
// No closing PHP tag to avoid 'headers already sent' errors.
//////////////////////////////////////////////////////////////////////
// Send the content-type header:
header('Content-Type: text/xml');
// Start the XML:
echo '<?xml version="1.0" encoding="utf-8" ?>
<employees>
';
// Include the database information script:
require_once('mysql.inc.php');
// Connect to the database:
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
// If a connection was established, run the query:
if ($dbc) {
// Define the query:
$q = "SELECT * FROM user where datetime like '2011-04-%'";
// Run the query:
$r = mysqli_query($dbc, $q);
// Confirm that some rows were returned:
if (mysqli_num_rows($r) > 0) {
// Fetch every row and print it as XML:
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
echo "<user>
<status>{$row['status']}</status>
<datetime>{$row['datetime']}</datetime>
</user>
";
} // End of WHILE loop.
} // End of mysqli_num_rows() IF.
} // End of $dbc IF.
// Complete the XML:
echo '</user>';
?>
and the output.. i am getting this error :s
XML Parsing Error: no element found
Location: http://test.com/soccc.php
Line Number 7, Column 1:
i am wondering... if i am missing this file ? "require_once('mysql.inc.php');"
code #2
this code seems good but getting some minor error AND biggest part of failure is.. it is not in form of xml
<?php
error_reporting(E_ALL);
// --------------------------- Configuration section
// Fill in according to your db settings (example is for a local and locked "play" WAMP server)
// Make sure to keep the ; and the ""
$host = "localhost";
$user = "abc";
$pass = "abc";
$database = "abc";
// Replace by a query that matches your database
$SQL_query = "SELECT * FROM userwhere datetime like '2011-04-%'";
// Optional: add the name of XSLT file.
// $xslt_file = "mysql-result.xsl";
// -------------------------- no changes needed below
$DB_link = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $DB_link) or die ("Could not find or access the database.");
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");
// we produce XML
header("Content-type: text/xml");
$XML = "<?xml version=\"1.0\"?>\n";
if ($xslt_file) $XML .= "<?xml-stylesheet href=\"$xslt_file\" type=\"text/xsl\" ?>";
// root node
$XML .= "<result>\n";
// rows
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$XML .= "\t<row>\n";
$i = 0;
// cells
foreach ($row as $cell) {
// Escaping illegal characters - not tested actually
$cell = str_replace("&", "&", $cell);
$cell = str_replace("<", "<", $cell);
$cell = str_replace(">", ">", $cell);
$cell = str_replace("\"", """, $cell);
$col_name = mysql_field_name($result,$i);
// creates the "<tag>contents</tag>" representing the column
$XML .= "\t\t<" . $col_name . ">" . $cell . "</" . $col_name . ">\n";
$i++;
}
$XML .= "\t</row>\n";
}
$XML .= "</result>\n";
// output the whole XML string
echo $XML;
?>
Can anyone here please point out the issue for this ? or is there any further reference i can take for such work ?
thank you