Tag Archives: mysql

AJAX registration form using jQuery

I have created this simple registration form which makes user registration using asynchronous requests in the background, checking if a duplicate username or email exists, and presenting the user with status of registration on the same page without have to reload the page even once.

It is simple, but with some prerequisites for my particular example. They are

  1. MySQL database for saving registrations
  2. PHP registration page for connecting to database
  3. Some basic javascript knowledge

So having set up the above stuff, we shall get started. If you don’t want to code all yourself, use my code from Github.

First of all, create for yourself a nice form. It doesn’t take much right? Just make sure you attach an ‘id’ attribute to each input field for convenience later. Here is my version.

If you see, after the form I have left an empty div. It is for displaying the response we get from our ajax request. You can, of course, add nice styling to this div. Having our form set, we can concentrate on the jQuery part.

Start with making sure our DOM is loaded with $(document).ready() function. Grab hold of the click event of the form submit button. Then we get the values in the username, email and password fields.

Here, we can have some basic checks to make sure form is filled up. We could have used the required attribute to make sure all fields are filled, but that does not give us full control, so use Javascript or jQuery and display some custom messages. I clubbed them together in a single if.

Next, using the simple $.ajax() function, make a request and get the response code. My action page is register.php, and I have make simple error codes to detect what response the backend gave. Here are the codes, for some reference.

// Code returns 101 for "username already exists"
// Code returns 102 for "email already exists"
// Code returns 104 for "internal server error"
// Code returns 202 for "user created successfully"

Refer register.php for complete code.

If the query succeeds, we capture the response in responseText variable. We can then do any response based manipulation we need for our specific needs. Just make sure to return false at the end to prevent default submit behavior. Here is my final jQuery snippet.

Finally, the entire HTML code has nothing more, just the standard doctype and stuff. Here is what to expect, or similar. LOL I was trying some CSS so ignore all that 😉

Also, I wanted to tell you, I switched from the classy syntax highlighter to this new (for me!) gist. It is cool, only a little more effort. Loved it. If you own a blog and post code frequently, give it a try. Thank you for reading.

Creating an autocomplete field | Setting up the backend | Part 1

Hello folks, so my exams are over and its time to roll. LOL pretty excited here. I am creating this series of two articles in which we will download all the pincodes (or zipcodes), add them to a MySQL database, write an API in PHP to retrieve the data in JSON, and finally we will write our form, in which, when we enter a pincode, we automatically get the ‘taluka’, ‘district’ and ‘state’ fields filled. So lets begin.
(and if you are from outside India, a ‘taluka‘ is a small region, like a city)

Step 1 – Getting the Pincode list

This was my biggest concern. Where do I get a list of all the pincodes in India. I could write a script and iterate through all the possible pincodes from 100000 to 999999, but, of course, there had to be a better way (and by the way, I would have got banned by the site if I sent around a million requests in a short period of time).
Fortunately, after some googling, I found a list here, at data.gov.in, to directly download the list, click here. The data is in .csv format, which is essentially a text document with some proper ordering (LOL).

Step 2 – Setting up MySQL database

We will now add the values to a database with will serve as our source to retrieve data in the form. I am selecting MySQL because, well, that is the only SQL I currently know (wink!). I assume you know the basics of how to create a database, and even if you don’t, you can do it easily by using the  phpmyadmin. It is graphical, and will get the work done.

The columns available for inserting data are:
officename,pincode,officeType,Deliverystatus,divisionname,regionname,circlename,Taluk,Districtname,statename

I will be using only ‘pincode’, ‘Taluk’, ‘Districtname’, ‘Statename’ here to avoid any unnecessary cluttering.

Create a database: ‘turnouts’
Create a table: ‘pincode’
Create 4 columns: ‘pincode’, ‘taluka’, ‘district’, ‘state’
Needless to say, these are just names and you can have what you want, but just don’t go insane over them. This is how it should look, or similar.
Having done that, move to step 3.

Step 3 – Writing the PHP script to enumerate database

So now we need to fill those database fields with data. If you take a look, the .csv file that we downloaded has around 152,000 lines. We can insert them manually, but that would take two and a half month, so we are better off writing a script. Language is on you. Python would work, but I preferred PHP, no good reason, I just choose it.
Basically we read a line in the file, select the data that we are concerned about, generate a MySQL query dynamically in a loop, and execute the query. Then move to the next line till feof or end of file.
Here is the php code.
<?php
$file = fopen("pincodes.csv", "r") or die("Open file");
$server = "localhost";
$username = "root";
$password = "password";
$db = "turnouts";
$conn = mysql_connect($server, $username, $password);
mysql_select_db("turnouts") or die();
if(!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
while(!feof($file)) {
$line = fgets($file);
$words = explode(",", $line);
$pincode = $words[1];
$taluka = $words[7];
$district = $words[8];
$state = $words[9];
$sql = "INSERT INTO pincode (pincode, taluka, district, state) VALUES ('$pincode', '$taluka', '$district', '$state')";
mysql_query($sql, $conn);
mysql_error($conn);
}
fclose($file);
mysql_close($conn);
?>

This code will take some time to execute. On my low end box, it took 90 minutes approx. After having executed, you should have a database with some real data. Here’s the row count, a whooping 154,797.

Now that we have our database setup, we are all set to write the front end. To make it easy to follow, I will write it in the second part of this series.

Edit: Here is the Part 2 Enjoy!

Creating an autocomplete field | Setting up frontend | Part 2

In the first part of this article, we had set up the backend MySQL database. Now we can write the code necessary to get information from the server and convert it to a format we can parse, JSON in my case here. So lets begin.

Step 4 – Writing script to get data from MySQL database

As always, we can write it in any language we are comfortable with. PHP happens to be mine. The script is straight forward, accept the pincode via a GET request, ‘pincode’ parameter. Query the database for its existence, add it to an array and using json_encode() function in PHP, display the output. If value does not exist, it will return null, and it is not our concern here about what happens after that, as it will be taken care by the application using this API.
Code: retrieve.php
<?php
$pincode = $_GET['pincode'];
$conn = mysql_connect("localhost", "root", "password");
mysql_select_db("turnouts") or die();

$pincode = (string)mysql_real_escape_string($pincode);

$sql = "SELECT taluka, district, state FROM pincode WHERE pincode = '$pincode'";
$data = mysql_query($sql);
if(!$data) {
die(mysql_error());
}
$row = mysql_fetch_row($data);
$taluka = $row[0];
$district = $row[1];
$state = $row[2];
$state = str_replace("\r\n", "", $state);
$taluka = strtolower($taluka);
$district = strtolower($district);
$state = strtolower($state);
header('Content-type: application/json');
$array = array('taluka'=>$taluka, 'district'=>$district, 'state'=>$state);
echo json_encode($array);
?> 

The URL will follow the pattern, /retrieve.php?pincode=000000 and return data. As an example, the following image,

If you have come successfully to this step, then bingo! We now have a working API.

Step 5 – Writing an AJAX form using jQuery

 We will now write a simple form with 4 fields in HTML. Include jQuery in that page, and then write the script at the bottom which will query our API asynchronously.
Code: pincodes.html
<html>
<head>
<title>Pincodes</title>
<script src="js/jquery-1.10.2.min.js"></script>
</head>
<body>
<div id="form">
<label for="pincode">Pincode:</label>
<input type="text" name="pincode" id="pincode" required /><br />

<label for="taluka">Taluka:</label>
<input type="text" name="taluka" id="taluka" /><br />
<label for="district">District: </label>
<input type="text" name="district" id="district"><br />
<label for="state">State:</label>
<input type="text" name="state" id="state"><br />
</div>
<script>
$(document).ready(function() {
$('#pincode').keyup(function(e) {
var pincode = $(this).val();

if(pincode.length == 6 && $.isNumeric(pincode)) {
var req = 'retrive.php?pincode=' + pincode;
$.getJSON(req, null, function(data) {
                 
$('#taluka').val(data.taluka);
$('#district').val(data.district);
$('#state').val(data.state);
});
};
});
</script>
</body>
</html>

Some explanation goes here.
The .keyup() event fires when a key is pressed in that field. We could have use onFocus() or onBlur() in javascript but that only fires when a field is focused or blurred, so this is much better. Since we have pincodes of length 6 digits here in India, to prevent script from querying for the first five digits, we have added a if(pincode.length == 6) to our code.

As I said, the even fires as soon as one enters the 6th digit and populates the remaining three fields with the data it retrieves.

It is hard to show it in a still image, but having just entered the pin, rest of the fields get populated.

Finally

So this was it. I hope you enjoyed it. Any corrections or suggesting, mail them to me or just comment down below. Thank you for reading.