Practical Web Programming

Tuesday, January 27, 2009

PHP: How to Create XML Document from MySQL Records

In this example, I'll be showing how to create XML document from MySQL records using PHP.

XML (Extensible Markup Language) is a general-purpose specification for creating custom markup languages. Aside from creating web pages, the most important use of XML is data sharing. Using XML you can pass data to different computer and applications without it being converted into another form.

Fortunately for PHP developers, creating XML from MySQL data is just like creating HTML. I'll be using the database table from this post. Here's the source codes.

<?php

//CREATE A DATABASE CONNECTION
$db_conn = mysql_connect("localhost", "root", "root1")
or die("Cannot connect to the database. ".mysql_error());
//SELECT THE DATABASE TO USE
mysql_select_db("test");

$sql = "SELECT
first_name, last_name, email
FROM employees";

$result = mysql_query($sql, $db_conn);

$xml = "<?xml version='1.0' encoding='UTF-8'?>
<employees>";
while ($row = mysql_fetch_assoc($result))
{
$xml .= "<employee>";
$xml .= "<firstname>".$row["first_name"]."</firstname>";
$xml .= "<lastname>".$row["last_name"]."</lastname>";
$xml .= "</employee>";
}
$xml .= "</employees>";

//DISPLAY THE XML
print $xml;

?>


This will output the following. I added line breaks (using enter key) at the end of every </employee> tags to make it more readable.

<?xml version='1.0' encoding='UTF-8'?>
<employees>
<employee><firstname>Steven</firstname><lastname>King</lastname></employee>
<employee><firstname>Neena</firstname><lastname>Kochhar</lastname></employee>
<employee><firstname>Lex</firstname><lastname>De Haan</lastname></employee>
<employee><firstname>Alexander</firstname><lastname>Hunold</lastname></employee>
<employee><firstname>Bruce</firstname><lastname>Ernst</lastname></employee>
<employee><firstname>David</firstname><lastname>Austin</lastname></employee>
<employee><firstname>Valli</firstname><lastname>Pataballa</lastname></employee>
<employee><firstname>Diana</firstname><lastname>Lorentz</lastname></employee>
<employee><firstname>Nancy</firstname><lastname>Greenberg</lastname></employee>
<employee><firstname>Daniel</firstname><lastname>Faviet</lastname></employee>
<employee><firstname>John</firstname><lastname>Chen</lastname></employee>
<employee><firstname>Ismael</firstname><lastname>Sciarra</lastname></employee>
<employee><firstname>Jose Manuel</firstname><lastname>Urman</lastname></employee>
<employee><firstname>Luis</firstname><lastname>Popp</lastname></employee>
<employee><firstname>Den</firstname><lastname>Raphaely</lastname></employee>
<employee><firstname>Alexander</firstname><lastname>Khoo</lastname></employee>
<employee><firstname>Shelli</firstname><lastname>Baida</lastname></employee>
<employee><firstname>Sigal</firstname><lastname>Tobias</lastname></employee>
<employee><firstname>Guy</firstname><lastname>Himuro</lastname></employee>
<employee><firstname>Karen</firstname><lastname>Colmenares</lastname></employee>
<employee><firstname>Matthew</firstname><lastname>Weiss</lastname></employee>
</employees>


Note: To view the full output, right click on the browser and select 'View Source' or 'View Page Source'.

0 comments:

Recent Post