<?php
/**
* Remote Query Client Class
* Executes a query on a remote server. (Requires server file)
*
* July 04, 2007 - Written by Jeff Williams (Initial Release)
* July 14, 2007 - Improved error handling
*
* @version 1.1
* @author Jeff L. Williams
*/
class RemoteQuery
{
// Hard code any of these values if you do
// not wish to pass them into the object
private $url = "";
private $securityKey = "";
private $server = "";
private $database = "";
private $username = "";
private $password = "";
private $charset = "";
/**
* The duration of a query in microseconds
*
* @var float
*/
public $duration;
/**
* The total number of rows returned from the last query
*
* @var integer
*/
public $rowCount = 0;
/**
* The last SQL statement executed
*
* @var string
*/
public $lastSQL = '';
/**
* Contains the error message returned from the last error
*
* @var string
*/
public $error = '';
/**
* CONSTRUCTOR
*
* @param string $url The URL to the Remote Query Server file
* @param string $securityKey Security password key (set in the Remote Query Server file)
* @param string $database Name of the database
* @param string $server Name of the MySQL host server
* @param string $username Database user name
* @param string $password Database password
* @param string $charset Database character set
*/
public function __construct($url = '', $securityKey = '', $database = '',
$server = '', $username = '', $password = '',
$charset = '') {
$this->connect($url, $securityKey, $database, $server,
$username, $password, $charset);
}
/**
* Sets connection data for the remote database and Remote Query Server
*
* @param string $url The URL to the Remote Query Server file
* @param string $securityKey Security password key (set in the Remote Query Server file)
* @param string $database Name of the database
* @param string $server Name of the MySQL host server
* @param string $username Database user name
* @param string $password Database password
* @param string $charset Database character set
*/
public function connect($url, $securityKey = '', $database = '',
$server = '', $username = '', $password = '',
$charset = '') {
// Save any passed in parameters in our local variables
if (strlen($url) > 0) $this->url = $url;
if (strlen($securityKey) > 0) $this->securityKey = $securityKey;
if (strlen($database) > 0) $this->database = $database;
if (strlen($server) > 0) $this->server = $server;
if (strlen($username) > 0) $this->username = $username;
if (strlen($password) > 0) $this->password = $password;
if (strlen($charset) > 0) $this->charset = $charset;
}
/**
* Executes a query on the remote server
*
* @param string $sql SQL query to execute on the remote server
* @return array An associative array containing the result set
*/
public function executeQuery($sql)
{
// Retrieve XML data from the remote server
$xml = file_get_contents($this->getURL($sql));
// If there was an error getting the data from this URL...
if (! $xml) {
// Set the error
// $this->error = 'Could not connect to URL: ' . $this->url;
$this->error = 'Could not connect to remote server';
return false;
} else {
// Check to make sure we got XML back
if (strtolower(substr($xml, 0, 5)) <> '<?xml') {
// If not, set the error
$this->error = $xml;
return false;
// Else, success - we have XML data
} else {
// Parse the XML into an array
$values = array();
$index = array();
$array = array();
$parser = xml_parser_create();
xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, 1);
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
xml_parse_into_struct($parser, $xml, $values, $index);
xml_parser_free($parser);
// Set local variables from return data
$this->rowCount = $values['0']['attributes']['rows'];
$this->duration = $values['0']['attributes']['microseconds'];
$this->lastSQL = $values['0']['attributes']['query'];
// If there is array data
if ($this->rowCount > 0) {
// Clean up the array
$i = 0;
$name = $values[$i]['tag'];
$array[$name] = isset($values[$i]['attributes']) ? $values[$i]['attributes'] : '';
$array[$name] = self::StructureToArray($values, $i);
// No error occured
$this->error = '';
// Return the part of the array we need
return $array['root']['row'];
// Else, we have no array data
} else {
// Return an empty array
return array();
}
}
}
}
/**
* Creates the full URL with GET parameters to execute a query on the server
*
* @param string $sql SQL query to execute
* @return string Full URL to return XML result set from remote server
*/
public function getURL($sql = '') {
// Add the GET parameters
$url = '';
if (strlen($this->securityKey) > 0) $url .= '&key=' . urlencode($this->securityKey);
if (strlen($this->database) > 0) $url .= '&db=' . urlencode($this->database);
if (strlen($this->server) > 0) $url .= '&host=' . urlencode($this->server);
if (strlen($this->username) > 0) $url .= '&user=' . urlencode($this->username);
if (strlen($this->password) > 0) $url .= '&pass=' . urlencode($this->password);
if (strlen($this->charset) > 0) $url .= '&charset=' . urlencode($this->charset);
if (strlen($sql) > 0) $url .= '&sql=' . urlencode($sql);
// Append these parameters onto the default URL
if (strlen($url) > 0) {
$url = $this->url . "?" . substr($url, 1);
} else {
$url = $this->url;
}
// Return the new query URL
return $url;
}
/**
* StructureToArray($array, $index)
*
* This is adds the contents of the returned xml into the array (recursive)
*
* @access private
* @param array $array this is the xml data in an array
* @param int $index this is the current location in the array
* @return array
*/
private static function StructureToArray($array, &$index)
{
$child = array();
if (isset($array[$index]['value'])) array_push($child, $array[$index]['value']);
while ($index++ < count($array)) {
switch ($array[$index]['type']) {
case 'cdata':
array_push($child, $array[$index]['value']);
break;
case 'complete':
$name = $array[$index]['tag'];
if(strlen($name) > 0) {
$child[$name]= ($array[$index]['value'])?($array[$index]['value']):'';
if(isset($array[$index]['attributes'])) {
$child[$name] = $array[$index]['attributes'];
}
}
break;
case 'open':
$name = $array[$index]['tag'];
$size = isset($child[$name]) ? sizeof($child[$name]) : 0;
$child[$name][$size] = self::StructureToArray($array, $index);
break;
case 'close':
return $child;
break;
}
}
return $child;
}
}
?>