Location: PHPKode > scripts > Remote MySQL Query > remote-mysql-query/remotequery.class.php
<?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;
    }

}
?>
Return current item: Remote MySQL Query