JAVASCRIPT

Creating a MySQL Client with AJAX to Administer Databases from the Web

In this first article of a three-part series, I’ll show you in a friendly fashion how to create the front end of an extensible MySQL client application that uses AJAX to send queries to a selected database in the background.

Author Info:

By: Alejandro Gervasio

Poor Best

Rating:

/ 6

August 28, 2006

TABLE OF CONTENTS:

As I said in the section that you just read, once the corresponding login page has been created, the only thing that remains undone is adding a simple PHP script that performs a simply validation on the values entered during the login process, and then registers these values (provided that they're correct) on session variables.

The reason for doing this is simply to make all the inputted connection values, along with the selected database, available to the scope of the MySQL client's main page. This way, they can be used for running queries against the given database. Sounds pretty simple, doesn't it?

After explaining how the login form will be checked out, and how their entered values will be registered for further processing, here is the PHP script that performs all the tasks that I mentioned before:

if($_POST['connect']){
    if($_POST['host']&&$_POST['user']&&$_POST['pass']&&$_POST
['database']){
        session_start();
        $_SESSION['host']=$_POST['host'];
        $_SESSION['user']=$_POST['user'];
        $_SESSION['pass']=$_POST['pass'];
        $_SESSION['database']=$_POST['database'];
        header('location:mysql_client.php');
    }
    else{
        header('location:'.$_SERVER['PHP_SELF']);
    }
}

As you can see, the above PHP snippet first verifies that the login form has been submitted successfully, and then redirects the user to the application's main page, in this case called "mysql_client.php," after registering all the connection parameters on session variables.

In addition, if you're going to use this script in potentially risky situations, I recommend that you add a mechanism for filtering user input and escaping unwanted characters. In this case I omitted this step deliberately to keep the code more readable, but as you know, user-supplied data must always be properly validated.

Right, now that you know how the previous PHP checking script does its thing, I'm sure you want to see how the full login page looks. In response to that, below I listed the complete source code for this page, this time including the PHP checking snippet. Take a look:

<?php
if($_POST['connect']){
    if($_POST['host']&&$_POST['user']&&$_POST['pass']&&$_POST
['database']){
        session_start();
        $_SESSION['host']=$_POST['host'];
        $_SESSION['user']=$_POST['user'];
        $_SESSION['pass']=$_POST['pass'];
        $_SESSION['database']=$_POST['database'];
        header('location:mysql_client.php');
    }
    else{
        header('location:'.$_SERVER['PHP_SELF']);
    }
}
?>

<!doctype html public "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<title>MySQL Client Login Page</title>
<meta http-equiv="Content-Type" content="text/html;charset=iso-
8859-1" />
<style type="text/css">
body{
    padding: 0;
    margin: 0;
    background: #eee;
}
p{
    font: bold 24px Verdana, Arial;
    color: #000;
    margin: 20px 0 0 0;
}
p{
    font: bold 12px Verdana, Arial;
    color: #000;
}
#maincontainer{
    width: 300px;
    height: 300px;
    background: #fc3;
    border: 1px solid #000;
    padding: 0 100px 0 0;
    margin-left: auto;
    margin-right: auto;
    margin-top: 10%;
    text-align: right;
}
.button{
    width: 145px;
    font: bold 12px Verdana, Arial;
    color: #000;
}
</style>
<script language="javascript">
window.onload=function(){document.getElementsByTagName('form')
[0].elements[0].focus()};
</script>
</head>
<body>
<div id="maincontainer">
<form method="post" action="<?php echo $_SERVER['PHP_SELF']?>">
<p>MySQL Client Login</p>
<p>Server Host <input type="text" name="host" title="Enter
MySQL's hostname" /></p>
<p>User <input type="text" name="user" title="Enter your user
name" /></p>
<p>Password <input type="password" name="pass" title="Enter your
password" /></p>
<p>Database <input type="text" name="database" title="Enter
database name" /></p>
<p><input type="submit" name="connect" value="Connect" class="button" /></p>
</form>
</div>
</body>
</html>

As you saw above, once the login form has been properly checked, the user is redirected to a "mysql_client.php" page. Obviously, this file will include all the required JavaScript functions and (X)HTML markup necessary for querying the selected MySQL database.

However, although this will certainly be the subject of the next tutorial, you can download a ZIP file containing the complete source files that correspond to this AJAX-driven MySQL client (this same file is available at the beginning of this article).

Wrapping up

In this first article of the series, I've shown you in a friendly fashion how to create the front end of an extensible MySQL client application that uses AJAX to send send queries to a selected database in the background.

In the next part, I'll introduce the group of JavaScript functions that sends silent HTTP requests, in order to run select, insert, update and delete commands directly from a web browser. See you there!

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.