I-Am-Bot Code, technology and life

Javascript to generate query from form

Posted by Srinath

One of the most mundane tasks in web development is insertion of forms into databases. Writing a query for each form is cumbersome and isn't the best way to go about it. Now before I get started, there are "far better" ways of doing it like using datagrid, or form designers, etc. Since I don't find them suitable for my taste, I've put together a very simple javascript that can do the job.

My example is based on PHP/MySQL and uses jQuery

The Problem: Generating an Insert Query for the particular form. Traditionally, if you form had say 25 fields, you'll name them, and post/get them to a server side script like php. Then you have to get each field using the $_POST or $_GET variables, and generate the query. Imagine doing this for 25 fields! Hope you get the point.

The Solution: Automatically traverse the form, generate the query using javascript, then submit it to the server side script by Ajax POST using jQuery. The function takes 3 arguments: table name, success message, error message.

The Limitations: As you'd imagine, there are a few serious limitations:

• The order of the form elements should match the order of the fields in the table. Needless to say the number of form elements should also match the number of fields in the table.
• The database name is hard coded into the server side script. Since my work involves only a single database, I find it easier to keep things simple.

Steps:

1. In your normal HTML form, arrange the input boxes in the order that they appear in your database table. In this example, we have 3 text boxes: user, email and pass.

<div id="container">
<form id="loginForm" method="POST" action="dbprocess.php">
<dd>
<dl><label for="user">Username</label></dl>
<dt><input type="text" name="user" maxlength="20" /></dt>
</dd>
<dd>
<dl><label for="email">Email</label></dl>
<dt><input type="text" name="email" maxlength="20" /></dt>
</dd>
<dd>
<dl><label for="pass">Password</label></dl>
<dt><input type="password" name="pass" maxlength="8" /></dt>
</dd>
<input type="submit" value="Submit" id="submitForm" />
</form>
</div>

2. Include the jQuery.js and formSubmit.js files in the header

3. Insert the following Javascript code that disables the submit button of the form, and calls our formSubmit function instead. The form submission is handled in the function via jQuery Ajax request.

<script type="text/javascript>

$(document).ready(function(){
$("#loginForm").submit(function(){
return false;
});
$("#submitForm").click(function(){
formSubmit("users","New user added successfully!","Error adding user!");
});
});
</script>

4. Write your server side PHP script to connect to the database, and perform the operation

<?php
$query = stripslashes($_POST["query"]);

if(!$con = mysql_connect("localhost","root",""))
die("Database Connection Error");

if(!mysql_select_db("login"))
die("Database does not exist");

$result = mysql_query($query,$con);
if($result)
echo "1";
else
echo mysql_error($con);
mysql_close($con);
?>

And that's it! Your form insertion should be working provided you took care of the limitations. This script is useful if you have many forms with lots of input fields in your application. Comments, suggestions and criticisms welcome!

Download the script with example