MYSQL to xml

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
firezz
Forum Newbie
Posts: 1
Joined: Fri Apr 15, 2011 1:03 pm

MYSQL to xml

Post by firezz »

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
Post Reply