Creating XML files from database queries

First I'm a network administrator and php developer. Not a writer.

Now, Here is the tech...

Having a lot of experince using the file functions related to php, which are needed to create a file.
NOTE: "You will also need write permission in the directory you run this script..."

...I started hacking....

BTW I'm the developer of PCCS MySQLDatabase Admin Tool and this code has been mergered into the code base...
URL: Admin Tool

$ nedit writexml.php & // my favorite editor for php coding

Script to create XML file

We will start writing code now... After working on the script for a while. I added form support.

I hope you can follow the code below... if not send me an email or check php.net. NOTE: use http://php.net/xml or /mysql

<?php

// If this is a submit create file...
if($submit) {

// $xml_filename="this variable is set via the form";
//file for writing
if(!$xmlfile = f$xml_filename,"w")) {
  print "Couldn'tfile";
} else {  
        // Make connection to dbhost
        $f = mysql_connect("localhost","root","") or die("Database Down");

        // Strip slashes that cause errors... 
        $query = stripslashes($query);

        // Execute database query: gotten from form submit
        $query = "
        SELECT accounts.*,account_type.*,transactions.*
        FROM accounts,account_type,transactions
        WHERE transactions.trans_acctid = accounts.acct_id
        AND accounts.acct_typeid = account_type.acct_typeid
        ORDER BY trans_date ASC LIMIT 3";
        
                
        $res = mysql_db_query("$dbname","$query");
        if(!$res) { 
          print "ERROR: " . mysql_error();
        } else {

        // Start creating file 
        // Formating xml file based on output from odbcsocket server
        // xml statement and version 
        fputs($xmlfile,"<?xml version=\"1.0\" ?>\n");
        
        // begin xml (similar to <table>)
        fputs($xmlfile,"<result state=\"success\">\n");
        $field = array();
        
        // Get the field names.
        $field=mysql_fetch_field($res);
        for($i=0; $i < count($field); $i++) {
          $fieldname = $field->name;
        }
           
        // Get result values   
        while($row=mysql_fetch_array($res)) {
           // begin row (similar to <tr>)
           @fputs($xmlfile,"<row>\n");
           $fields = array();

           
           for($i=0; $i < mysql_num_fields($res); $i++) {
             $datavalue = $row[$i];
             $datadescr = mysql_field_name($res,$i);
             
             // turning off error output. using @
             // was getting error even though file was being create
             // with out errors...
             
             // begin of data (similar to <td>)   
             @fputs($xmlfile,"\t<column name=\"". $datadescr
."\">");
             
             // end of data (similar to </td>)                 
             @fputs($xmlfile,$datavalue."</column>\n");
        }
          // end row (similar to </tr>)
          @fputs($xmlfile,"</row>\n");

     }  

        // end xml (similar to </table>)
        @fputs($xmlfile,"</result>");
        
        // Close my xml file after writing it...
        @fclose($xmlfile);
  
   
  } // end if(!$res) 
 
 } // end if(!$xmlfile) 

// Display some menu like options

 print "\n\nXML File written...\n";
 

 print "<P> View/Edit XML script";
 print "   <A
HREF=\"xmleditor.php?directory=./&filename=".$xml_filename."\">".$xml_filename."</a>";

 print "<P> Parse XML script";
 print "   <A
HREF=\"xmlparse.php?xml_file=".$xml_filename."\">".$xml_filename."</a>";

} else {

// if it wasn't a submit display query form
// allow database name, filename to be created
// and sql query


 print "<CENTER>\n";
 print "<FORM ACTION=\"".$PHP_SELF ."\" METHOD=post>\n";
 print "<TABLE>\n";
 print "<TR>\n";
 print "<TD>Database</TD><TD><INPUT TYPE=text
NAME=dbname SIZE=20></TD>\n";
 print "</TR><TR>\n";
 print "<TD>XML FileName</TD><TD><INPUT TYPE=text
NAME=xml_filename SIZE=20></TD>\n";
 print "</TR><TR>\n";
 print "<TD COLSPAN=2>SQL QUERY</TD>\n";
 print "</TR><TR>\n";
 print "<TD COLSPAN=2><TEXTAREA NAME=query COLS=50
ROWS=20></TEXTAREA></TD>\n";
 print "</TR><TR>\n";
 print "<TD COLSPAN=2 ALIGN=center><INPUT TYPE=submit NAME=submit
VALUE=\"Create XML File\">\n";
 print "</TABLE>\n";
 print "</FORM>\n";
 print " </CENTER>\n";
 } // end of script...
?>

Output of the script

Took me about 4 hours to get the output perfect...

<?xml version="1.0" ?>
<result state="success">
<row>
        <column name="acct_id">1</column>
        <column name="acct_number">610</column>
        <column name="acct_name">Meals</column>
        <column name="acct_typeid">1</column>
        <column name="acct_typeid">1</column>
        <column name="acct_type">Expenses</column>
        <column name="trans_id">1</column>
        <column name="trans_number">2</column>
        <column name="trans_date"></column>
        <column name="trans_description">Fancy Meal</column>
        <column name="trans_acctid">1</column>
        <column name="trans_withdraw">7.00</column>
        <column name="trans_deposit">0.00</column>
        <column name="trans_taxable">Yes</column>
</row>
<row>
        <column name="acct_id">1</column>
        <column name="acct_number">610</column>
        <column name="acct_name">Meals</column>
        <column name="acct_typeid">1</column>
        <column name="acct_typeid">1</column>
        <column name="acct_type">Expenses</column>
        <column name="trans_id">15</column>
        <column name="trans_number">2</column>
        <column name="trans_date"></column>
        <column name="trans_description">Fancy Meal</column>
        <column name="trans_acctid">1</column>
        <column name="trans_withdraw">7.00</column>
        <column name="trans_deposit">0.00</column>
        <column name="trans_taxable">Yes</column>
</row>
<row>
        <column name="acct_id">9</column>
        <column name="acct_number">430</column>
        <column name="acct_name">Second Mutual Fund</column>
        <column name="acct_typeid">2</column>
        <column name="acct_typeid">2</column>
        <column name="acct_type">Revenues</column>
        <column name="trans_id">12</column>
        <column name="trans_number">43</column>
        <column name="trans_date"></column>
        <column name="trans_description">Second Mutual
Fund</column>
        <column name="trans_acctid">9</column>
        <column name="trans_withdraw">0.00</column>
        <column name="trans_deposit">1000.00</column>
        <column name="trans_taxable">No</column>
</row>
</result>

Output of xml parse...

Script used to parse xml file...

<?php
print '
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<HTML lang="en">
<HEAD>
<TITLE>XML Parser</TITLE>
</HEAD>
<BODY>';
        // Hacked this script from various snippets of code...
        //handler for the start elements
        function beginElement($parser, $name, $attribs)
        {
                if (strtolower($name) == "row")
                {
                        //handler for the row element
                        print "<tr>";
                }
                if (strtolower($name) == "column") 
                {
                        //handler for the column
                        print "<td>";
                }
                if (strtolower($name) == "error")  
                {
                        //handler for the error
                        print "<tr><td>";
                }
                if (strtolower($name) == "result")
                {
                        print "<br><table border=0 cellpadding=2
cellspacing=0>";
                }
        }

        //handler for the end of elements
        function endElement($parser, $name)
        {
                if (strtolower($name) == "row")
                {
                        //handler for the row element
                        print "</tr>";
                }
                if (strtolower($name) == "column")
                {
                        //handler for the column
                        print "</td>";
                }
                if (strtolower($name) == "error")
                {
                        //handler for the error
                        print "</td></tr>";
                }
                if (strtolower($name) == "result")
                {
                        print "</table> <br>";
                }
        }

        //handler for character data
        function characterData($parser, $data)
        {
                print "$data";
        }

        // $xml_file = 'this.xml';
        
        // declare the character set - UTF-8 is the default
        $type = 'UTF-8';
                
        // create our parser
        $xml_parser = xml_parser_create($type);
                
        // set some parser options
        // enable case-folding for this parser
        xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, true);
        
        // set source encoding for this parser
        xml_parser_set_option($xml_parser, XML_OPTION_TARGET_ENCODING,
'UTF-8');


        //parse the XML
        $xml_parser = xml_parser_create();
        
        // set up start and end element handlers
        xml_set_element_handler($xml_parser, "beginElement",
"endElement");
        
        // set up character data handler
        xml_set_character_data_handler($xml_parser, "characterData");
                      

        if (!($fp = f$xml_file, 'r'))) {
            die("Could not$xml_file for parsing!\n");
        }

        // loop through the file and parse!
        while ($data = fread($fp, 4096)) {
            if (!($data = utf8_encode($data))) {
                echo 'ERROR'."\n";
            }
            if (!xml_parse($xml_parser, $data, feof($fp))) {
                die(sprintf( "XML error: %s at line %d\n\n",
                xml_error_string(xml_get_error_code($xml_parser)),
                xml_get_current_line_number($xml_parser)));
            }
        }
        // Free this XML parser
        xml_parser_free($xml_parser);
        
print '
</BODY>
</HTML>';
?>

Script to display/edit xml file

<?   
// Hacked from phpgen.

        //******************* Save edited file...
        if ($action == "save_edited"):
        $fp = f$file,"w");
        fputs($fp,$to_edit);
        fclose($fp);
        $path = dirname($file);
?>
<SCRIPT LANGUAGE="JavaScript">
        document.location="./";
</SCRIPT>
        
<?   endif;

        //******************* Edit a file
        if (empty($action)):
        $file = $directory . $filename;
        if (!file_exists($file)) {
                echo "<H2>$filename does not exist!";
                echo "<A
HREF=javascript:history.back()>Return</A>";
                exit;
        }

        echo "<H2>Edit of $file</H2>";
        echo "<FORM METHOD=POST>";
        echo "<INPUT TYPE=HIDDEN NAME=action VALUE=save_edited>";
        echo "<INPUT TYPE=HIDDEN NAME=file VALUE='$file'>";
        echo "<TEXTAREA NAME=to_edit COLS=70 ROWS=25>";
        $fp = f$file,"r"); 
        while (!feof($fp)):
                $r = fgets($fp,9999);
                echo $r;
        endwhile;
        fclose($fp);
        echo "</TEXTAREA><BR>";
        echo "<A
HREF=javascript:history.back()>Cancel</A><BR>";
        echo "<B>Filename: <INPUT SIZE=40 NAME=file
VALUE='$file'>";
        echo "<INPUT TYPE=SUBMIT VALUE=Save>";
        
        echo "</FORM>";
        endif; //*Edit 

?>

>> Comments/FeedBack