Location: PHPKode > scripts > ADOXML > adoxml/adoxml.php
<?php
/*
ADOXML - Version 0.4
SQL2XML - XML2SQL
Copyright (C) 2004  F.J. De Klerk

This file is part of ADOXML.

ADOXML is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

ADOXML by Johan De Klerk
hide@address.com

Many thanks to David Williams for this great idea!

ADOXML has been tested on:
MySQL 4.0.*
SQLServer 2000

Let me know if you get it working on other DBMS
*/

require_once('adodb.inc.php');
require_once('ezxml.php');

class ADOXML {

	var $include_pk = false;
	
	/* Builds xml string
	 * $rs -> ADORecordset Object
	 * $tablename -> the table of the select query
	*/
	function & BuildXML(&$rs,$tablename) {
		@$rs->Move(0);

		$xml = '<?xml version="1.0"?>'."\n";
		$xml .= '<table name="'.$tablename.'">'."\n";

		$j = 0;

		while (!$rs->EOF) {
			$row = $rs->GetRowAssoc();

			$xml .= "\t".'<row num="'.$j.'">'."\n";

			$keys = array_keys($row);
			
			if ($this->include_pk) {
				$begin = 0;
			}
			else {
				$begin = 1;
			}

			for ($i = $begin; $i < count($row); $i++) {
				$xml .= "\t\t".'<'.strtolower($keys[$i]).'>';

				$content = $row[$keys[$i]];
				
				$xml .= str_replace('&','',$content);

				$xml .= '</'.strtolower($keys[$i]).'>'."\n";
			}

			$xml .= "\t".'</row>'."\n";

			$rs->moveNext();
			$j++;
		}

		$xml .= '</table>';
		
		@$rs->Move(0);

		return $xml;
	}

	/* Builds XML String from select query
	 * $query -> the select query to transform
	*/
	function & BuildXMLWithQuery($query,&$conn) {
		if (!is_object($conn)) {
			trigger_error('ADOXML: Connection object not valid, for function BuildXMLWithQuery',E_USER_ERROR);
		}

		$rs = $conn->Execute($query);

		if (!$rs) {
			trigger_error('ADOXML: Error with select: '.$conn->ErrorMsg(),E_USER_ERROR);
		}

		return $this->BuildXML($rs,$this->_getTableName($query));
	}
	
	/* Read XML from file into string
	 * $xmlfile -> path to the file
	*/
	function & ReadXMLFile($xmlfile) {
		if (file_exists($xmlfile)) {
			$f = fopen($xmlfile,'r');
			$xml = fread($f,filesize($xmlfile));
			fclose($f);
			return $xml;
		}
		else {
			trigger_error('ADOXML: File: '.$xmlfile.' does not exist.',E_USER_ERROR);
		}
	}
	
	/* Write XML string to file
	 * $xmlfile -> path to the file to be overwritten or created
	*/
	function WriteXMLFile(&$xml,$xmlfile) {
		//@chmod('adoxml.php',777);
		$f = @fopen($xmlfile,'w');
		@fwrite($f,$xml);
		@fclose($f);

		if (file_exists($xmlfile)) {
			return true;
		}
		else {
			trigger_error('ADOXML: Error Writing File: '.$xmlfile,E_USER_ERROR);
		}
	}
	
	/* Insert XML into table
	*  $xml    -> the xml string (must adhere to adoxml xml format)
	*  $connto -> the ADO Connection object to use to execute the insert query
	*  $table  -> the table you wish to insert to
	*/
	function InsertXML($xml,&$connto,$tablename) {
		$this->_StripXML($xml);

		if (!is_object($connto)) {
			trigger_error('ADOXML: Connection object not valid, for function Insert',E_USER_ERROR);
		}
		
		$query = 'select * from '.$tablename;
		@$connto->SelectLimit($query,0,0);
		$rs =& $connto->Execute($query);

		if (!$rs) {
			trigger_error('ADOXML: Error with select: '.$connto->ErrorMsg(),E_USER_ERROR);
		}
		
		$root = eZXML::domTree($xml,array('TrimWhiteSpace'=>true));

		$query = '';

		foreach($root->children as $table) {
			
			foreach($table->children as $row) {

				if ($row->name == 'row') {

					$query1 = 'insert into '.$tablename.'(';
					$query2 = 'values(';

					foreach($row->children as $field) {
						if ($this->_fieldInRs($field->name,$rs)) {
							$query1 .= $field->name.',';
							$query2 .= "'".get_contents($field)."',";
						}
					}

					$query1 = substr($query1,0,strlen($query1)-1).') ';
					$query2 = substr($query2,0,strlen($query2)-1).') ';

					$query = $query1.$query2;

					if ($connto->Execute($query) == false) {
						trigger_error('ADOXML: Error inserting: '.$connto->ErrorMsg(),E_USER_ERROR);
					}
				}
			}
		}
	}
	
	/* Update a table with XML as source of changes
	*  $xml       -> the xml string (must adhere to adoxml xml format)
	*  $connto    -> the ADO Connection object you want to use to perform the update query
	*  $tablename -> The table to update
	* 
	*  Note: This function assumes the first field in the XML source is the primary key field or the field you want to use to 		          identify the row.
	*        You can generate XML String with primary key by setting class var include_pk to true
	*/
	function UpdateXML($xml,&$connto,$tablename) {
		$this->_StripXML($xml);

		if (!is_object($connto)) {
			trigger_error('ADOXML: Connection object not valid, for function Update',E_USER_ERROR);
		}
	
		$query = 'select * from '.$tablename;
		@$connto->SelectLimit($query,0,0);
		$rs =& $connto->Execute($query);

		if (!$rs) {
			trigger_error('ADOXML: Error with select: '.$connto->ErrorMsg(),E_USER_ERROR);
		}

		$root = eZXML::domTree($xml,array('TrimWhiteSpace'=>true));

		$query = '';

		foreach($root->children as $table) {
			
			foreach($table->children as $row) {

				if ($row->name == 'row') {

					$first = true;

					$query = 'update '.$tablename.' set ';

					foreach($row->children as $field) {

						if ($first) {
							$query1 = ' where '.$field->name."='".get_contents($field)."'";
							$first = false;
						}

						if ($this->_fieldInRs($field->name,$rs)) {
							$query .= $field->name."='".get_contents($field)."',";
						}
					}

					$query = substr($query,0,strlen($query)-1);

					$query .= $query1;

					if ($connto->Execute($query) == false) {
						trigger_error('ADOXML: Error updating: '.$connto->ErrorMsg(),E_USER_ERROR);
					}
				}
			}
		}
	}

	//PRIVATE FUNCTIONS
	function _StripXML(&$xml) {
		$chars = preg_split('//', $xml, -1, PREG_SPLIT_NO_EMPTY);

		$intable = false;
		$xml = '<?xml version="1.0"?>'."\n";

		for ($i = 0; $i < count($chars); $i++) {
			
			if (!$intable) {
				if ($chars[$i] == '<') {
					$tablename = $chars[$i+1].$chars[$i+2].$chars[$i+3].$chars[$i+4].$chars[$i+5];

					if ($tablename == 'table') {
						$intable = true;
						$i--;
						continue;
					}
				}
			}
			else {
				$xml .= $chars[$i];

				if ($chars[$i] == '>') {
					$tablename = $chars[$i-6].$chars[$i-5].$chars[$i-4].$chars[$i-3].$chars[$i-2].$chars[$i-1];

					if ($tablename == '/table') {
						$intable = false;
						break;
					}
				}
			}
		}
		return $xml;
	}

	function & _GetTableName($query) {
		$query = strtolower(trim($query));
		$pos = strpos($query,'from');

		$tablename = substr($query,$pos+5);
		trim($tablename);

		if (strstr($tablename,' ')) {
			$pos = strpos($tablename,' ');
			$tablename = substr($tablename,0,$pos);
		}

		return $tablename;
	}

	function _fieldInRs($field,$rs) {

		for ($i = 0; $i < $rs->FieldCount(); $i++) {
			$fl = $rs->FetchField($i);

			if ($fl->name == $field) {
				return true;
			}
		}

		return false;
	}
}
?>
Return current item: ADOXML