Communicating with the Server of a MySQL Client with AJAX - Running queries on the server: a simple PHP script
(Page 4 of 4 )
As you possibly noticed, each time a new query is entered by the user on the corresponding command box, the JavaScript “sendHttpRequest()” function is called up, and a new file, which I creatively named “mysql_server.php” is requested. Not surprisingly, this file will be responsible for processing the query typed by the user, in addition to showing the returned database rows, just in case a SELECT statement is entered.
Admittedly, all the tasks that I mentioned before sound like a hard thing to do, but in fact they can be performed by coding a simple PHP script, which will carry out all of them.
Based on the explanation that I gave above about the group of tasks that must be performed by the “mysql_server.php” file that I referenced before, here is how the signature of this file looks:
<?php
session_start();
// retrieve connection parameters
$host=$_SESSION['host'];
$user=$_SESSION['user'];
$pass=$_SESSION['pass'];
$database=$_SESSION['database'];
// connect to MySQL server
if(!$conId=mysql_connect($host,$user,$pass)){
trigger_error('Error connecting to the server',E_USER_ERROR);
}
// select database
if(!mysql_select_db($database,$conId)){
trigger_error('Error selecting database',E_USER_ERROR);
}
// prepare query
$query=get_magic_quotes_gpc()?stripslashes($_GET['query']):$_GET
['query'];
// run query
if(!$result=@mysql_query($query,$conId)){
trigger_error('Error running query',E_USER_ERROR);
}
// check if the query returns a result set
if(@get_resource_type($result)=='mysql result'){
while($row=mysql_fetch_assoc($result)){
foreach($row as $data){
echo $data.' ';
}
echo "<br />".str_repeat('-',97)."<br />";
}
}
else{
echo 'Query Ok';
}
?>
If you take some time and examine the script listed above, then you’ll notice a few things worth stressing. First, notice how the PHP snippet retrieves the respective connection parameters that were initially stored on session variables by the login module. Obviously, there is a simple reason for this: each time a query must be executed against the selected database, a new connection to MySQL must be established.
Since I don’t want to work with persistent connections, this is a good approach for using the values entered on the login form to run the specified queries. Wasn’t that simple?
As you can see, the rest of the script is very simple to follow: after obtaining the connection parameters mentioned previously, a new connection to MySQL is performed, then the appropriate database is selected, and finally the entered query is also executed, after being escaped by mean of the “stripslashes()” PHP built-in function.
Perhaps the most interesting part of the above script is the one that deals with processing result sets. In case a SELECT statement returns a non-empty data set, the snippet checks this condition by using the PHP “get_resource_type()” function and echoes the values of the returned rows directly to the browser (remember that this data is retrieved by the client via the “resposeText” property that belongs to the respective requester object).
Otherwise, if the query doesn’t return anything, that is an insert, update or delete command has been executed, then a “Query Ok” message is sent back to the client for display.
At this point, I provided you with an in-depth explanation of the specific function of each module of this AJAX-based MySQL client. Of course, the application supports many improvements that can be introduced with minor hassles, like working with multiple databases, or even using DDL commands. Indeed, possibilities are numerous and exciting!
Final thoughts
That’s all for now. In this three-part series, I hopefully contributed to the vast terrain of AJAX-driven applications by adding to the list another one: a highly expandable web-based MySQL client, which can be used for performing some common DML statements against a specified database.
As I said before, the project is open to many modifications, and certainly I’ll be glad if you take the challenge. See you in the next web development tutorial!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 8 
