Retrieve data from MySQL table and generate XML in PHP

     Hi, guys. Today I am going to explain about retrieve data from a MySQL table and show them in a PHP web page but in XML format.

     After you see the following image, you will be understand what I am going to do today. This is the final out put I am expecting from todays’ lesson.

Figure 1

     Today also I am using the database.php file and constant.php file that I have used in one of my earliest post. So the following is the content which generate the above page.

...
require("database.php");

function parseToXML($htmlStr) 
{ 
...
// String replace content goes here
...
return $xmlStr; 
} 

// Select all the rows in the member_details table
$query = "SELECT * FROM `member_details`";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

// To see the db errors and php errors, comment the following line
header("Content-type: text/xml");

// Start XML file, echo parent node
echo '**anuja>';

// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
  echo '**anuj ';
  echo 'ID="' . parseToXML($row['id']) . '" ';
  echo 'NIC="' . parseToXML($row['nic']) . '" ';
  echo 'Name="' . $row['name'] . '" ';
  echo 'Age="' . $row['age'] . '" ';
  echo '/>';
}

// End XML file
echo '**/anuja>';
...

     First of all you have to fill the missing parts of the above code sample. I had to eliminate some code parts because of the formatting problem giving by the WordPress blog.

     Replace ** with < bracket. The relevant code phase for “// String replace content goes here” is;

$xmlStr=str_replace('<','&lt;',$htmlStr); 
$xmlStr=str_replace('>','&gt;',$xmlStr); 
$xmlStr=str_replace('"','&quot;',$xmlStr); 
$xmlStr=str_replace("'",'&apos;',$xmlStr); 
$xmlStr=str_replace("&",'&amp;',$xmlStr); 
return $xmlStr;

     So let me tell you where these kinds of work practically used in developing. I met this code while I was developing Google map component. So the credit goes to the Google and thank you Google for guiding us good code practice rather that download a component and plug it in to our existing systems.

That’s it for today 🙂 Bye…

Advertisement