Martin Gardner.co.uk

How to use AJAX to Query a MYSQL Database and Check availability of a username

Posted on September 20, 2008 by Marty

Hi, Thanks for reading my article on how you can use the JQuery language, PHP and SQL to query your database searching for a username which is already taken and then if the username is taken show a nice fade in message without leaving that page…

First of, what is AJAX, well AJAX stands for Asynchronous JavaScript and XML, developers use this to retrieve data from the server asynchronously in the background without interfering with the display and behavior of the existing page.

You can download the latest JQuery file here: JQuery

the other files we will be using are:

• register.php
• checkusername.php

Save these pages as php and upload them to your web-server, open up checkusername.php and change the mysql connection string values to match your own test server and you should be ready to test, the included file just uses the basic dreamweaver code so you should be able to hook it up through that using your own connection file, change the value of the recordset to match your own database name (dbname) .

register.php
this file will house our html code which the user will see as the front end, the css will be embedded on this page as will our AJAX script and call to the file jquery.js, the user wont be redirected from this page, or the page wont reload, it will query the database without leaving this page, (of course if we were using this commercially we would have a page our form would post to after registration is complete).

checkusername.php
this is our page which will be executed within our AJAX script it will have our MySQL database connection string, it will have our SQL statement which will be run to query our database, and it will have our returned data which will be used in our AJAX to determine if the username is taken already or not..

THE FORM

The basic form on our page will be a fictitious registration form and will have 2 textfields, one with an ID of username and the other with be a password, this username ID will be used to collect the value which is entered into it by the user, we use an onblur method to invoke the script, which means after the user clicks out of or tabs out of the text field it will run the script…

THE RUNNING SCRIPT

once the user has tabbed out of the text field we can use the firefox plugin firebug
to double check what our script is doing, we want the script to pass the username entered in the textfield as a GET method, which means it use’s the url to pass information like checkusername.php?uname=’the value’ were the value would be the username or text entered into the text field…

in the picture above we can see that the URL has the parameter uname which had a value of marty, this was sent to the script were $_GET php code trapped the value and used this to check the database for a match.

the firebug plugin also has a tab showing the response back from the server, if i had a picture of that above it would have had a response of 1, were the SQL didn’t find any matches and so printed the message on screen that i can use this username…

please feel free to give this a shot, after a while you’ll get to grips with all that’s happening in the background and understand the code behind it all and hopefully you can put it to good use..