H4CK3R 4/hacker04/04_HACK04.ISO/src/PHP/mysql2xml.php3.txt

Text File | 2002-05-06 | 7.9 KB | 238 lines

MySQL2XML

Class to generate an XML document from a mysql table. You define the database and table names and the xmltagnames to be used for each tablefield. Where clauses are also supported. REQUIRES the MySQL Database class by me. Also available here



<?php
/*
This is a sample script. Paste this in to
an empty php3 file and run it.

if( empty($xml_inc_php3) )
   include("xml.inc.php3");

   $XMLGenerator = new XMLDefinition( "hostname", "databasename", "username", "password", "tablename", "rootnodename", "rownodename","iso-8859-1");
   $XMLGenerator->AddNode( "id", "recordid" );
   $XMLGenerator->AddNode( "prj_id", "projectid" );
   $XMLGenerator->AddNode( "subprj_id", "subprjid" );
   $XMLGenerator->AddNode( "taet_id", "whatid" );
   $XMLGenerator->AddNode( "mitarb_id", "programerid" );
   $XMLGenerator->AddNode( "Bemerkung", "comment", 1 , 1);
   $XMLGenerator->AddNode( "Wert", "duration" );
   $XMLGenerator->AddNode( "Datum", "date" );
   $XMLGenerator->AddRestriction("projectid", "=", "57");

   echo $XMLGenerator->GetXML();
   //$XMLGenerator->EchoXML();
*/
/*

  This class REQUIRES the mysql Database Class by me available
  from http://phpbuilder.px.sklar.com.
  You can contact me by mail at [e-mail].

*/
if( empty($database_inc_php3) )
   include( "database.inc.php3");

     class NodeDefinition
     {
        var $SQLFieldName;
        var $XMLNodeName;
        var $CDATANodeBeg;
        var $CDATANodeEnd;
        var $Operator;
        var $Value;
        var $CharField;

        function NodeDefinition($SQLField, $XMLNode, $bCDATA = 0, $Char = 0)
        {
           global $__debug__;
           $this->SQLFieldName   = $SQLField;
           $this->XMLNodeName    = $XMLNode;

           if( $bCDATA != 0 )
           {
            $this->CDATANodeBeg     =  "<![CDATA[";
            $this->CDATANodeEnd     =  "]]>";
           }
           else
           {
            $this->CDATANodeBeg     =  "";
            $this->CDATANodeEnd     =  "";
           }

           if( $Char != 0)
               $this->CharField =  "'";
        }
        function AddRestriction( $Op, $Val)
        {
           global $__debug__;

           $this->Operator    = $Op;
           $this->Value       = $Val;
        }
     }

     class XMLDefinition
     {
        var $UserName;
        var $Password;
        var $DataBaseHost;
        var $DataBaseName;
        var $SQLDataSource;
        var $XMLRootName;
        var $XMLRecordNodeName;
        var $NodeList;
        var $iNumNodes;
        var $SQLString;
        var $Encoding;

        function XMLDefinition( $DBHost, $DBName, $Usr, $Pwd, $SQLTable, $XMLRoot, $XMLRecord =  "record", $Enc =  "iso-8859-1")
        {
           global $__debug__;
           $this->DataBaseHost      = $DBHost;
           $this->DataBaseName      = $DBName;
           $this->UserName          = $Usr;
           $this->Password          = $Pwd;
           $this->SQLDataSource     = $SQLTable;
           $this->XMLRootName       = $XMLRoot;
           $this->XMLRecordNodeName = $XMLRecord;
           $this->Encoding          = $Enc;
           $this->iNumNodes = 0;
        }

        function AddNode( $SQLField, $XMLNode, $bCDATA = 0, $Char = 0 )
        {
           global $__debug__;
           $this->NodeList[$this->iNumNodes] = new NodeDefinition($SQLField, $XMLNode, $bCDATA, $Char);
           $this->iNumNodes++;
        }

        function AddRestriction( $XMLNode, $Oper, $Valu )
        {
           global $__debug__;
            $CurNode = false;

            for($i=0; $i < $this->iNumNodes; $i++)
            {
               $CurNode = $this->NodeList[$i];
               if( $CurNode->XMLNodeName == $XMLNode )
               {
                  if( $CurNode )
                  {
                     $CurNode->AddRestriction( $Oper, $Valu);
                     $this->NodeList[$i] = $CurNode;
                  }

                  break;
               }
            }


        }

        function GenerateSQL()
        {
           global $__debug__;

           $this->SQLString =  "";
           $SQLString =  "SELECT ";
           $WhereString =  " WHERE ";
            for($i=0; $i < $this->iNumNodes; $i++)
            {
               $CurNode = $this->NodeList[$i];
               $SQLString = $SQLString . $CurNode->SQLFieldName;
               if( $i < $this->iNumNodes-1 )
                  $SQLString = $SQLString .  ",";
               $SQLString = $SQLString .  " ";

               if( strlen($CurNode->Operator) > 0 &&
                     strlen($CurNode->Value) > 0 )
               {
                  $WhereString = $WhereString . $CurNode->SQLFieldName .
                                  " " . $CurNode->Operator .  " " . $CurNode->CharField .
                                 $CurNode->Value . $CurNode->CharField.  " AND ";
               }
            }

            $SQLString = $SQLString .  "FROM " . $this->SQLDataSource;

            if( substr($WhereString, strlen($WhereString)-5,5) == " AND " )
            {
               $WhereString = substr($WhereString, 0,strlen($WhereString)-5);
               $SQLString = $SQLString .  " " . $WhereString;
            }



            $this->SQLString = $SQLString;
           if( $__debug__ == 1)
            echo  "<br>" . $this->SQLString;
        }

        function EchoXML()
        {
           global $__debug__;
            if( strlen($this->SQLString) == 0 )
               $this->GenerateSQL();

            $opDataBase = new MySQLDatabase;
            $opDataBase->Open( $this->DataBaseName, $this->DataBaseHost, $this->UserName, $this->Password );
            $opDataBase->Query($this->SQLString);

            echo  "<?xml version=\"1.0\" encoding=\"" . $this->Encoding .  "\"?>";
            echo  "<" . $this->XMLRootName .  ">";
            while( $opDataBase->NextRow())
            {
               echo  "<" . $this->XMLRecordNodeName .  ">";
               for($i=0; $i < $this->iNumNodes; $i++)
               {
                  $CurNode = $this->NodeList[$i];

                  echo  "<" . $CurNode->XMLNodeName .  ">" . $CurNode->CDATANodeBeg .
                        $opDataBase->GetField( $CurNode->SQLFieldName) .
                        $CurNode->CDATANodeEnd .  "</" . $CurNode->XMLNodeName .  ">";
               }
               echo  "</" . $this->XMLRecordNodeName .  ">";

            }
            echo  "</" . $this->XMLRootName .  ">";
        }

        function GetXML()
        {
           global $__debug__;
            if( strlen($this->SQLString) == 0 )
               $this->GenerateSQL();

            $opDataBase = new MySQLDatabase;
            $opDataBase->Open( $this->DataBaseName, $this->DataBaseHost, $this->UserName, $this->Password );
            $opDataBase->Query($this->SQLString);

            $ret =  "<?xml version=\"1.0\" encoding=\""  . $this->Encoding .  "\"?>";
            $ret = $ret .  "<" . $this->XMLRootName .  ">";
            while( $opDataBase->NextRow())
            {
               $ret .=  "<" . $this->XMLRecordNodeName .  ">";
               for($i=0; $i < $this->iNumNodes; $i++)
               {
                  $CurNode = $this->NodeList[$i];

                  $ret .=  "<" . $CurNode->XMLNodeName .  ">" . $CurNode->CDATANodeBeg .
                        $opDataBase->GetField( $CurNode->SQLFieldName) .
                        $CurNode->CDATANodeEnd .  "</" . $CurNode->XMLNodeName .  ">";
               }
              $ret .=  "</" . $this->XMLRecordNodeName .  ">";

            }
            $ret .=  "</" . $this->XMLRootName .  ">";

            return $ret;
        }
     }
   $xml_inc_php3 = 1;
?>