Location: PHPKode > scripts > Remote MySQL Query > remote-mysql-query/remote_query_server.php
<?php
// ========================================================
// Remote Query XML Server by Jeff L. Williams
// ========================================================
// Returns remote queries as XML from a MySQL database
// Use with remotequery.class.php client
//
// Special thank you to Fernando Reis GuimarĂ£es for a fix
// that allows non-SELECT SQL queries.
//
// Example URL:
// http://yourserver.com/remote_query_server?id=pass123&host=dbhost&db=database&user=dbuser&pass=dbpassword&sql=SELECT * FROM table
// ========================================================

// Set up a password to access this page
// Leave it blank if you do not want it
$securityKey = 'pass123';

// Hard code any of these values if you
// do not wish to pass it in for security
$server   = 'localhost';
$database = '';
$username = '';
$password = '';
$charset  = '';
$sql      = '';

// --------------------------------------------------------

// Check the security password
if (getFormValue('key') <> $securityKey) {
    exit('Permission denied');
}

// Get any parameters passed in
$server   = getFormValue('host'    , $server);
$database = getFormValue('db'      , $database);
$username = getFormValue('user'    , $username);
$password = getFormValue('pass'    , $password);
$charset  = getFormValue('charset' , $charset);
$sql      = getFormValue('sql'     , $sql);

// Attempt to connect to the server
$db = @mysql_connect($server, $username, $password);
if (! $db) {
    exit('Could not connect: ' . mysql_error());
}

// Attempt to select the database
if (! (mysql_select_db($database))) {
    $error = 'Cannot select database: ' . mysql_error();
    @mysql_close($db);
    exit($error);
}

// Set the character set
if ((strlen($charset) > 0)) {
    if (! (mysql_query("SET CHARACTER SET '{" . $charset . "}'", $db))) {
        $error = 'Invalid character set: ' . mysql_error();
        @mysql_close($db);
        exit($error);
    }
}

// Start the timer
$parts = explode(' ',microtime());
$timerStart = $parts[1].substr($parts[0],1);

// Execute the query
$result = @mysql_query($sql, $db);
if (!$result) {
    exit(mysql_error());
}

// Stop the timer
$parts  = explode(' ',microtime());
$timerStop = $parts[1].substr($parts[0],1);
$timer  = ($timerStop - $timerStart);

// Create a new XML document
$doc = new DomDocument('1.0'); // ,'UTF-8');

// Create the root node
$root = $doc->createElement('root');
$root = $doc->appendChild($root);
$root->setAttribute('rows', @mysql_num_rows($result));
$root->setAttribute('microseconds', $timer);
$root->setAttribute('query', $sql);

// Reset the row count
$rowCount = 0;

// Only process if results are returned
if (is_resource($result)) {

	// process one row at a time
	while ($row = mysql_fetch_assoc($result)) {

	    // Keep the row count
	    $rowCount = $rowCount + 1;

	    // Add node for each row
	    $element = $doc->createElement('row');
	    $element = $root->appendChild($element);
	    $element->setAttribute('index', $rowCount);

	    // Add a child node for each field
	    foreach ($row as $fieldname => $fieldvalue) {
	        $child = $doc->createElement($fieldname);
	        $child = $element->appendChild($child);

	        // $fieldvalue = iconv("ISO-8859-1", "UTF-8", $fieldvalue);
	        $fieldvalue = htmlspecialchars($fieldvalue);
	        $value = $doc->createTextNode($fieldvalue);
	        $value = $child->appendChild($value);
	    } // foreach

	} // while

} // if

// Release memory and close the connection
@mysql_free_result($result);
@mysql_close($db);

// Show the XML document
echo $doc->saveXML();

// Clean up and release memory
unset($child);
unset($element);
unset($root);
unset($doc);

// --------------------------------------------------------

// Function to get form values with an optional default if empty
function getFormValue($name, $default = '') {
    if (isset($_POST[$name]))
    {
        return stripslashes($_POST[$name]);
    } else {
        if (isset($_GET[$name]))
        {
            return stripslashes($_GET[$name]);
        } else {
            return $default;
        }
    }
}
Return current item: Remote MySQL Query