How to use XML with Database using PHP

Post by **Neo** » Sun Feb 28, 2010 10:01 pm

PHP treats an XML document like a normal file, it is easy to interact with it, even though its structure is complicated. For example to create a XML document, we use the common functions that PHP uses to create files. For example:

Code: Select all

if(){
//first make sure that no other file with that name exists
if(!file_exists($xmldoc)){ 
//try to open the file
if($fp=fopen($xmldoc,'w+')){
//write to the file
fwrite($fp,"<?xml version=1.0 ?><br>");
//set the root element i.e: <rootelementname>
fwrite($fp,”<articles>);
fwrite($fp,"<article>\n");
//Now we set the child elements
//Title
fwrite($fp,'<title>The Amazing truth</title>\n’);
//Set te Author name
fwrite($fp,'<auth>John Doe</auth>\n");
//Set the date
fwrite($fp,'<date>2008-11-12</date>\n");
//Set the description
fwrite($fp,'<description>Some long description here</description>\n’);
//close child element
fwrite($fp,"</article>");
//Now close the root element
fwrite($fp,"</articles>");
//close the file
fclose($fp);

The above code demonstrates an easy way to create an XML document. The problem with the above code is that the data in it is static. What if we have a site that needs to share updated content with other programs or applications or websites? Most sites that have this kind of dynamic content are link to a database that provides this kind of information. Let’s say, for arguments sake, that you want to make a list of contacts available to any and all applications. These contact names are stored in a database. What you will have to do is first create some kind of data input form that will collect the information and store it in a database. Because we want different applications to be able to process the information, we create an XML document and put it up on our website. For anyone who wants to view a list of our contacts we create a link to the XML document. This creates a link between the database and the XML document. So how do we achieve this? Let’s take a look at a practical example.

Our document is going to be called contacts.xml; it will be updated via our database also called contacts, which contains a table called contact. First, we create the database table. We want the table to have the following fields:

name – name of the contact
email – email address of the contact
address- address of contact

Feel free to add or remove as much information as you want. Below is the SQL to create the database table and some sample data:

Code: Select all

CREATE TABLE `contact` (
  `id` int(4) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `address` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

#
# Dumping data for table `contact`
#
INSERT INTO `contact` VALUES (1, 'eno', '[email protected]', '12 ghd');
INSERT INTO `contact` VALUES (2, 'simon', '/[email protected]', '78 Panny Road');
INSERT INTO `contact` VALUES (3, 'Xuro', '[email protected]', '368 Lann Road');

Simply copy and paste the data into your MYSQL client. Finally, we create the XML document. The document will have the following structure:

Code: Select all

<?xml version="1.0" ?>
<contacts>
       <contact>
          <name></name>
          <email></email>
          <address></address>   
      </contact>
</contacts>

Now for the actual program that will create the XML document. This program will use live data that is stored in the database to create an XML file and fill in all the information that is needed. First, it starts with a page that is going to present a list of options to choose from. The program has four scripts in total:

menu.php:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<table width="100%" border="1">
  <tr>
    <td bgcolor="#CCCCCC"><h1 align="center">XML Document Proccessor </h1></td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td><a href="form.php">Create New XML Document </a></td>
  </tr>
  <tr>
    <td><a href="inputform.php">Add new XML data to Database </a></td>
  </tr>
  <tr>
    <td><a href="write2xmldoc.php">Create Updated XML document </a></td>
  </tr>
  <tr>
    <td> </td>
  </tr>
</table>
</body>
</html>

inputform.php – This script enables the user to input up to date contacts data. Below is the code for it:

Code: Select all

<?php 
//create file
if(isset($_POST['submit'])){
//initialise vars
$msg = "";
//make sure that all the form fields are filled in
if(empty($_POST['name'])){
$msg = "Please enter a <b>Name</b> for the contact<br>";
}
if(empty($_POST['email'])){
$msg .= "Please enter a <b>Email Address</b> for the contact<br>";
}
if(empty($_POST['address'])){
$msg .= "Please enter a <b>Address</b> for the contact<br>";
}

if(empty($msg)){

//1. Store the information in the database

$q = "INSERT INTO contact (id,name,email,address) values (".$_POST['name'].",".$_POST['email'].",".$_POST['address'].")"; 
$r = mysql_query($q);

if($r){
$msg = "Contact has been added to the database";
}else{
$msg = "The following error occurred:<br>" mysql_error();
}


}

}//end submit check

?>

The HTML portion of the script has the following code:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="">
  <table width="100%" border="1">
    <tr>
      <td colspan="2" bgcolor="#CCCCCC">Contacts Information Input Form </td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
    </tr>
    <tr>
      <td width="12%"><strong>Name:</strong></td>
      <td width="88%"><label>
        <input name="name" type="text" id="name" />
      </label></td>
    </tr>
    <tr>
      <td><strong>Email:</strong></td>
      <td><label>
        <input name="email" type="text" id="email" />
      </label></td>
    </tr>
    <tr>
      <td><strong>Address:</strong></td>
      <td><label>
        <textarea name="address" id="address"></textarea>
      </label></td>
    </tr>
    <tr>
      <td> </td>
      <td><label>
        <input name="submit" type="submit" id="submit" value="Save Contact Information" />
      </label></td>
    </tr>
  </table>
</form>
</body>
</html>

The form.php script simply enables the user to create an xml document; we have already looked at this script. Here’s its code:

Code: Select all

<?php 
//create file
if(isset($_POST['submit'])){
//initialise vars
$msg = "";
//make sure that all the form fields are filled in
if(empty($_POST['fn'])){
$msg = "Please enter a file name<br>";
}
if(empty($_POST['rootEl'])){
$msg .= "Please enter a <b>root element</b> name<br>";
}
if(empty($_POST['title'])){
$msg .= "Please enter a <b>Title</b><br>";
}
if(empty($_POST['auth'])){
$msg .= "Please enter a <b>Author</b> name<br>";
}
if(empty($_POST['desc'])){
$msg .= "Please enter a <b>Description</b>";
}


if(empty($msg)){
//set the name of the file
$xmldoc  = $_POST['fn'].".xml";
//first make sure that no other file with that name exists
if(!file_exists($xmldoc)){ 
//try to open the file
if($fp=fopen($xmldoc,'w+')){
//write to the file
fwrite($fp,"<?xml version='1.0' ?>");
//set the root element i.e: <rootelementname>
fwrite($fp,'<'.$_POST['rootEl'].'>');
fwrite($fp,"<article>\n");
//Now we set the child elements
//Title
fwrite($fp,'<title>'.$_POST['title']."</title>\n");
//Set te Author name
fwrite($fp,'<auth>'.$_POST['auth']."</auth>\n");
//Set the date
fwrite($fp,'<date>'.$_POST['dte']."</date>\n");
//Set the description
fwrite($fp,'<description>'.$_POST['desc']."</description>\n");
//close child element
fwrite($fp,"</article>");
//Now close the root element
fwrite($fp,"</".$_POST['rootEl'].">");
//close the file
fclose($fp);

//inform user that the writing was a success 
$msg =  "The XML document called ".$xmldoc. " has been created";
}else{
$msg =  "There was an error, could not create the XML document.";
}
}else{
$msg = "File name already exists, please try again";
}
}//err check
}//submit
?>

The HTML part of the script has the following code:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
    color: #FF0000;
    font-weight: bold;
}
-->
</style>
</head>

<body>
<form action="" method="post">
<table width="100%" border="1">
  <tr>
    <td colspan="2" bgcolor="#CCCCCC"><h1 align="center">XML and PHP </h1></td>
    </tr>
  <tr>
    <td colspan="2" valign="top"><?php if(isset($msg)){
    echo "<span class='style1'>".$msg."</span>";
    }?>
      </td>
    </tr>
  <tr>
    <td valign="top">New XML document name: </td>
    <td><label>
      <input name="fn" type="text" id="fn" />
    </label></td>
  </tr>
  <tr valign="top">
    <td colspan="2" bgcolor="#CCCCCC"><strong>Root Element: </strong></td>
    </tr>
  <tr>
    <td valign="top">Root Element Name: </td>
    <td><label>
      <input name="rootEl" type="text" id="rootEl"  value="articles"/>
    </label></td>
  </tr>
  <tr>
    <td colspan="2" valign="top" bgcolor="#CCCCCC"><strong>Child Elements: </strong></td>
    </tr>
  <tr>
    <td valign="top">Title</td>
    <td><label>
      <input name="title" type="text" id="title"  value="The Amazing Truth"/>
    </label></td>
  </tr>
  <tr>
    <td valign="top">Author</td>
    <td><label>
      <input name="auth" type="text" id="auth"  value="Axaro !Noabeb"/>
    </label></td>
  </tr>
  <tr>
    <td valign="top">Date</td>
    <td>
      <label>
      <input type="text" name="dte"  value="<?php echo date("Y-m-d")?>"/>
      </label></td>
  </tr>
  <tr>
    <td valign="top">Description</td>
    <td><label>
      <textarea name="desc" cols="25" rows="5" id="desc">Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Donec molestie. Sed  aliquam sem ut arcu. Phasellus sollicitudin. Vestibulum condimentum facilisis nulla.  In hac habitasse platea dictumst. Nulla nonummy. Cras quis libero. Cras venenatis.  Aliquam posuere lobortis pede. Nullam fringilla urna id leo. Praesent aliquet  pretium erat. Praesent non odio. Pellentesque a magna a mauris vulputate lacinia.  Aenean viverra. Class aptent taciti sociosqu ad litora torquent per conubia nostra,  per inceptos hymenaeos. Aliquam lacus. Mauris magna eros, semper a, tempor et,  rutrum et, tortor.  </textarea>
    </label></td>
  </tr>
  <tr>
    <td> </td>
    <td><label>
      <input name="submit" type="submit" id="submit" value="Create XML Document" />
    </label></td>
  </tr>
</table>

</form>

</body>
</html>

The last script is called write2toxmldoc.php. It extracts the latest contact details from the database and creates an updated xml document. It also provides a link to the location of this file. Below is its code:

Code: Select all

<?php
$xmldoc  = "myxml.xml";
//first make sure that no other file with that name exists
if(!file_exists($xmldoc)){ 
//try to open the file
if($fp=fopen($xmldoc,'w+')){
//write to the file
fwrite($fp,"<?xml version='1.0' ?>");
//set the root element i.e: <rootelementname>
fwrite($fp,"<contacts>");
//connect to database server
$host ="localhost";
$pw ="pass";
$user = "root";
$dbname = "contacts";
$dbc=mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

$q = "SELECT * FROM contact";
$r = mysql_query($q);
if($r){
while($row = mysql_fetch_assoc($r)){
fwrite($fp,"<contact>\n");
//Now we set the child elements
//Name of contact
fwrite($fp,'<name>'.$row['name']."</name>\n");
//Set the email address of contact
fwrite($fp,'<email>'.$row['email']."</email>\n");
//Set the address of the contact
fwrite($fp,'<address>'.$row['address']."</address>\n");
//close child element
fwrite($fp,"</contact>");
}//while
//Now close the root element
fwrite($fp,"</contacts>");
//close the file
fclose($fp);
}else{
$msg = "Error occurred ".mysql_error();
}// if $r

//inform user that the writing was a success 
$msg =  "The XML document called ".$xmldoc. " has been created.<br>To view the updated XML document click <a href=".$xmldoc.">here</a>";
}else{
$msg =  "There was an error, could not create the XML document.";
}
}else{
$msg = "File name already exists, please try again";
}

?>

It has the following HTML code:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
    color: #FF0000;
    font-weight: bold;
}
-->
</style>
</head>

<body>
<?php if(isset($msg)){
    echo "<span class='style1'>".$msg."</span>";
    }?>
</body>
</html>

The above program works very well when creating and writing to an XML document, but it will become very difficult when you have to read XML data from an xml document. Why? Because an XML document is very complicated, they are not as straight forward as the normal files. The XML documents that we used so far are very simple and straightforward; a more complicated XML document cannot realistically be accessed by the file functions that we have used so far. PHP has specific functions that can access any part of an XML document and retrieve that information. We will look at some of those functions in a bit. In the next section, we will discuss the code and then look at how to format the XML document so that it can be viewed in a web browser.