Papermashup

Subscribe


Tweets


"RT @bethgordon: .@Tesco Please rethink archaic 'Approved by Mums'. Pretty sure it's not just mothers who make childcare decisions. https://…"

@ashleyford 2 days ago

"Is there ever a time that @DFS doesn't have a sale on?"

@ashleyford 6 days ago

Designer and web developer, Co-founder and Technical Director at Harkable.com. Previously I worked at Spotify, MySpace and InMobi. Contact me - ashley[at]papermashup.com

jQuery PHP & MySQL Username Availability Checker

AshleyAshley

I was reading Nettuts.com this week and saw the post by Matt Vickers on checking the availability of a username with Mootools. I tend not to use Mootools so i thought I’d replicate the technique using jQuery. The code degrades gracefully if JavaScript is disabled, it is advised that you always check the username against the database again, just before you add any data to the database.

username

The JavaScript

  1. So to start with we have our jQuery on document ready function, within this we have an event listener .keyup function which when triggered runs the function ‘username_check’.
  2. Within the ‘username_check’ function we firstly assign the variable ‘username’ to the value of the input field with the id #username.
  3. We now run a few checks that the username input field isn’t empty and that its 4 or more characters in length. If everything is ok and the username variable is over 4 characters we can go ahead and make the ajax request.

Its worth noting that because were checking the username against the database on keyUp we’re making a unique request each time. To help limit these requests we check that the username is more than 4 characters before perfoming an ajax request.

console


<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js"></script>
<script>
$(document).ready(function(){
$('#username').keyup(username_check);
});
	
function username_check(){
	
var username = $('#username').val();

if(username == "" || username.length < 4){
$('#username').css('border', '3px #CCC solid');
$('#tick').hide();
}else{

jQuery.ajax({
   type: "POST",
   url: "check.php",
   data: 'username='+ username,
   cache: false,
   success: function(response){
if(response == 1){
	$('#username').css('border', '3px #C33 solid');	
	$('#tick').hide();
	$('#cross').fadeIn();
	}else{
	$('#username').css('border', '3px #090 solid');
	$('#cross').hide();
	$('#tick').fadeIn();
	     }

}
});
}



}

</script>

Looking at ‘check.php’

This is the code that checks the database to see if the username exists or not. dbConnector.php is the database connection class that i use in all my projects and is in the download of the project files.

The first thing we do is trim any white space and convert the string to lowercase. i’ve ensured that the usernames in the database are all lowercase. So the usernames aren’t case sensitive when a user types one in to the box.

IMPORTANT! because we’re handling unknown user generated data we have to use the mysql_escape_string() function to stop SQL injection and to strip any unwanted chars

Notice that i have omitted the last php tag. If a page contains only PHP code, best practice is to omit the PHP end tag ( ?> ). PHP does not require the end tag in PHP only pages.

Including the end tag can cause unwanted injection of trailing white spaces which can cause header already sent errors.


<?php
include("dbConnector.php");
$connector = new DbConnector();

$username = trim(strtolower($_POST['username']));
$username = mysql_escape_string($username);

$query = "SELECT username FROM usernameCheck WHERE username = '$username' LIMIT 1";
$result = $connector->query($query);
$num = mysql_num_rows($result);

echo $num;
mysql_close();

The HTML

Here’s the input form field and the two images.



Username: <input name="username" id="username" type="text" />
<img id="tick" src="tick.png" width="16" height="16"/>
<img id="cross" src="cross.png" width="16" height="16"/>

The CSS

Here’s the basic style to format the username input field, and to hide the tick and cross image.


#username{
	padding:3px;
	font-size:18px;
	border:3px #CCC solid;
}

#tick{display:none}
#cross{display:none}

demodownload

Designer and web developer, Co-founder and Technical Director at Harkable.com. Previously I worked at Spotify, MySpace and InMobi. Contact me - ashley[at]papermashup.com

Comments 60
  • Iris
    Posted on

    Iris Iris

    Reply Author

    The code works fine with mysql, but mysql_* it will be no more supported so I did the convert into mysqli_* but after converting the code doesn’t work, it always come the green tick. What should I do


  • Norbert
    Posted on

    Norbert Norbert

    Reply Author

    Hi i want to check email availablility, can you help me with this…
    jQuery.ajax({
    type: “POST”,
    url: “check.php”,
    data: ‘username=’+ username,
    cache: false,

    username = alias input but tld is @tld.com select box


  • Louis Blais
    Posted on

    Louis Blais Louis Blais

    Reply Author

    Hello, I hope to have an answer! No matter what I type too, I always get a “green username availability”
    How do I fix that ?


  • Bob
    Posted on

    Bob Bob

    Reply Author

    Hi Ashley,

    Great routine – just what I was looking for. Couple of questions though, I can’t get the script to work inside Dreamweaver using XAMPP as a localhost. Does the username field within the database have to be;

    1) indexed?
    2) Char, varchar, or other type?

    No matter what I type in (when using localhost) even an existing username, I always get a tick??

    Bob


  • Hermine
    Posted on

    Hermine Hermine

    Reply Author

    Spot on with this write-up, I really believe this amazing site needs
    far more attention. I’ll probably be back again to read through more, thanks for the info!


  • Burak
    Posted on

    Burak Burak

    Reply Author

    It works great but there is a problem that it doesnt check availability by filling the text field clicking to list of usernames that the browser brigns username you used before to register to another web site and while you are typing same username which is gonna appear under text field


    • Arvis
      Posted on

      Arvis Arvis

      Reply Author

      $(‘#username’).blur(username_check);
      });

      Change keyup on blur :)


  • John
    Posted on

    John John

    Reply Author

    Thanks Ashley, the code really worked for me.
    But I need an additional help. I want the submit button to be disabled until the username has been verified, so that the username won’t end up having duplicate entry.
    Thanks again.


  • Usman
    Posted on

    Usman Usman

    Reply Author

    Good Article !!
    Thnks for sharing :)


  • Usman
    Posted on

    Usman Usman

    Reply Author

    Good Article !!
    Thnks for sharing :)


  • Usman
    Posted on

    Usman Usman

    Reply Author

    Good Article !!
    Thnks for sharing :)


  • Ramon
    Posted on

    Ramon Ramon

    Reply Author

    I tried to implement this and it keep saying it is available when it is not a little help pleas


  • Ryan
    Posted on

    Ryan Ryan

    Reply Author

    This script works great but I’m running into a potentially huge snag.

    I load my signup.php form into a jQuery UI modal window and while the form itself loads fine however the validation doesn’t happen – the imgs are never shown. However if I load the signup.php by itself in a browser window, all works perfectly.

    Please advise. Any help is greatly appreciated.


  • syed mohammed ahmed
    Posted on

    syed mohammed ahmed syed mohammed ahmed

    Reply Author

  • Ernie
    Posted on

    Ernie Ernie

    Reply Author

    great tutorial..thanks for sharing…


  • Georgi Peychinov
    Posted on

    Georgi Peychinov Georgi Peychinov

    Reply Author

    Hello Ashley,
    great code but doesnt work properly in Chrome and FireFox


  • Deeni
    Posted on

    Deeni Deeni

    Reply Author

    The code is really working, i have try it,…thanks you,…


  • Zack
    Posted on

    Zack Zack

    Reply Author

    Very neat code! Thanks for providing the scripts.


  • Abbas
    Posted on

    Abbas Abbas

    Reply Author

    hello I’m using smart wizard which check for fields in each step if one of the filed not filled the wizard will not go to the next step. it was working fine but i tried to check for if username does exist i need to ask user to change user name then proceed but unfortunately if user click next the wizard will take them to next step although a message saying username does exists shown
    im using Ajax to retrieve value from database
    if you need code please do not hesitate in contacting me


  • Luke
    Posted on

    Luke Luke

    Reply Author

    Great post, very helpful!

    I’d use .change() handler for when people have finished typing in the field before to checks and use a sprite image for the cross/tick and just .css() to switch from cross/tick, then you should avoid routine errors.


  • Marco
    Posted on

    Marco Marco

    Reply Author

    Great tutorial, very clear and powerful!

    I’d like to know how to use it with the combination of jquery validation (http://docs.jquery.com/Plugins/Validation) if it’s possible… thanks!


  • Dennis
    Posted on

    Dennis Dennis

    Reply Author

    Awesome tutorial. I love jQuery! I changed the script to show a reverse. Checking to make sure the username exists on the server. It will show a checkmark if the email address is found. Sweet! ty ty


  • Derek
    Posted on

    Derek Derek

    Reply Author

    This code wasn’t quite what I needed, but I managed to get it working with a date as a string. (currently testing)

    My requirements are a date checker to allow prospective wedding customers to book only on free dates.

    If you get chance to create code specifically for this purpose complete with date validation you will get loads of takers. I had to think outside the box because this solution is just not available on the web.

    For newbies who have limited knowledge:

    1) Create a database on your hosting site
    2) Create a table (usernameCheck)
    3) Create a field (username)
    4) Create a read only user and set a password
    5) Edit dbConnector.php entering the host, database, username and password
    6) copy all files up to your host site

    Hope this helps someone


  • mirzade
    Posted on

    mirzade mirzade

    Reply Author

    hi,once thanks again
    how can i
    query to $table check.php_?
    for example
    $table=table1; (test.php)

    select * from $table………. (check.php)


  • mirzade
    Posted on

    mirzade mirzade

    Reply Author

    thanks a lot.fine
    you had keep my work….


  • rida
    Posted on

    rida rida

    Reply Author

    hey Ashley,

    I really like it 😉

    Would have been good also to add some comments like if you enter a username which is less than 3 characters such as “username must be more than 3 characters”

    I tried to enter Sam and nothing happened. I was aware that you need to enter >= 4 characters but a simple user may not understand what is going on

    Thanks


  • Bhadra
    Posted on

    Bhadra Bhadra

    Reply Author

    Hi..i downloaded files from this website but and included every thing corectly
    but not working.
    In my project i have to check mobile numbers from database plz help me.


  • Peter Nicholls
    Posted on

    Peter Nicholls Peter Nicholls

    Reply Author

    Niiiice. I like this, and the fact it is very immediate too is sooooo good! I’m going to be using this trick on http://www.startintv.com :)
    Pete


  • Matt
    Posted on

    Matt Matt

    Reply Author

    Great! thanks for sharing man


  • Deroeck
    Posted on

    Deroeck Deroeck

    Reply Author

    We did not understand your second paragraph by any means. What can you mean by that? This really is an interesting topic to me so I want to understand everything that you have to say. We were not able to find a number of other articles during my search although I’m not very computer literate so that could be why. I am hoping to see you publishing more frequently.


  • Leclec
    Posted on

    Leclec Leclec

    Reply Author

    can someone post the dbConnector.php script file?

    thanks


  • Bugz R
    Posted on

    Bugz R Bugz R

    Reply Author

    Appreciate mannnn…..:)


  • Joyel Puryear
    Posted on

    Joyel Puryear Joyel Puryear

    Reply Author

    I just recently implemented what you showed here in your blog. It is very affective. I wanted to offer another bit of feedback on how to slow some of the hit on the server (if your validating by email address). If your validating by email address then you can validate on valid email address. Meaning along with 4 characters, if you also check to make sure it’s a valid email BEFORE hitting the server it’ll restrict some of the flow to the server. However you can only do that if it’s a email address that’s being used as the username (which ends up happening a lot, at least in the systems I build).

    Either way it was a great idea and a very nice/clear implementation.

    —–
    http://www.infotechnologist.biz
    http://www.realmofwriting.com


  • gary
    Posted on

    gary gary

    Reply Author

    Hi,
    I want to check if an email exists. I have used this on the email field but if you put an incomplete email it will accept it and show a green tick and border.


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @ Gary,

      I’d recommend adding javascript email validation to the input field then on the callback if the email is valid run the function to see if the email exists in the database, so you’ll always be checking valid email addresses against the database minimising database connections.


  • Dominican Republic villas
    Posted on

    Dominican Republic villas Dominican Republic villas

    Reply Author

    Excellent, I tried it and it worked like a charm. I`m into a lot of programming for the company I work for.


  • Ronald
    Posted on

    Ronald Ronald

    Reply Author

    Sometimes, when you type very fast, and it switches the cross for the checkmark or vice-versa, it liked to “skip” to hide() the old icon for me, and I ended up having both icons showing next to each other. I’m assuming this can happen when the routines overlap, especially when you add in more than just hide() and fadeIn() like I did. For example, I am also filling a nice message into a notice box with html() before I call hide() and fadeIn().+1
    +1


  • tutu
    Posted on

    tutu tutu

    Reply Author

    I can’t retrieve a set usernames from my database. how can i do it with the php page?where should i change?? Can you help me Ashley??

    thanx in advance


  • tubedownloader.com
    Posted on

    tubedownloader.com tubedownloader.com

    Reply Author

    Sometimes, when you type very fast, and it switches the cross for the checkmark or vice-versa, it liked to “skip” to hide() the old icon for me, and I ended up having both icons showing next to each other. I’m assuming this can happen when the routines overlap, especially when you add in more than just hide() and fadeIn() like I did. For example, I am also filling a nice message into a notice box with html() before I call hide() and fadeIn().
    +1


  • MP
    Posted on

    MP MP

    Reply Author

    How do you check the usernames? What if I want to fill an array with names and check against it?


  • Nomi
    Posted on

    Nomi Nomi

    Reply Author

    hey Ashley, good stuff man.. i just came across your site and its very useful … keep it up


  • bobsoap
    Posted on

    bobsoap bobsoap

    Reply Author

    Great write-up, thanks for sharing. I already put it to good use and I’d like to add a tiny performance issue that I came across – and fixed:

    Sometimes, when you type very fast, and it switches the cross for the checkmark or vice-versa, it liked to “skip” to hide() the old icon for me, and I ended up having both icons showing next to each other. I’m assuming this can happen when the routines overlap, especially when you add in more than just hide() and fadeIn() like I did. For example, I am also filling a nice message into a notice box with html() before I call hide() and fadeIn().

    To fix this, I simply added the fadeIn() inside of a function of hide(). My logic was that when it ges reduced to a single function, it’s less likely that it gets skipped when you type fast. It works so far for me.

    On line 25 of your above code, you have


    $('#tick').hide();
    $('#cross').fadeIn();

    …and I changed that to


    $('#tick').hide(100, function() {
    $(this).parent().find('#cross').fadeIn(400);
    });

    Same for the ELSE part.

    It’s only a minor thing but I hope it helps. Thanks again Ashley for your great post!


  • Test
    Posted on

    Test Test

    Reply Author

    Hi Ashley,

    Nice article!!!

    How can i use other events like onblur instead of keyup


  • parveen chauhan
    Posted on

    parveen chauhan parveen chauhan

    Reply Author

    nice tutorial! sir thanks!!!!!1


  • gimsot
    Posted on

    gimsot gimsot

    Reply Author

    Hi Ashley, thanks for this tutorial, i’ts work on my project :)..
    But how about with the submit button? If the username unavailabe, I was able to process the form. Does anyone can help?


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @gimsot when you submit the form you will need to do an additional check against the db in your PHP code that adds the user to the database. Let me know if that makes sense.


  • factoringcompare.com
    Posted on

    factoringcompare.com factoringcompare.com

    Reply Author

    Hi Ashley,

    Done it! ASP code to connect to DB.


    <%
    Dim rsUser__MMColParam
    rsUser__MMColParam = "1"
    If (Request.Form("username") “”) Then
    rsUser__MMColParam = Request.Form(“username”)
    End If
    %>

    1


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      Good stuff! thanks for posting it also. Sorry i couldn’t have been more helpful, im a PHP programmer.


  • factoringcompare.com
    Posted on

    factoringcompare.com factoringcompare.com

    Reply Author

    The work around could be to use PHP. Can you show me how to connect to an Access database using check.php?


  • factoringcompare.com
    Posted on

    factoringcompare.com factoringcompare.com

    Reply Author

    Great work. Could you show how to use with asp?


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @factoringcompare I wish i could show you how to use it with ASP however i don’t have any experience using ASP – If anyone know how to use it with ASP,feel free to post a link here, or show us some code.


  • Crystal
    Posted on

    Crystal Crystal

    Reply Author

    Great tutorial! This complete newbie was able to implement it on a new project, thank you so much!


  • Webdeveloper
    Posted on

    Webdeveloper Webdeveloper

    Reply Author

    I’m definitely gonna use this one! Thanks!


  • arnold
    Posted on

    arnold arnold

    Reply Author

    Thanks might be helpful in my future projects :)


  • Ben Scobie
    Posted on

    Ben Scobie Ben Scobie

    Reply Author

    Thank you very much, much better than my current way of checking :)


  • Juarez P. A. Filho
    Posted on

    Juarez P. A. Filho Juarez P. A. Filho

    Reply Author

    Wow… I liked it so much. Simple and easy to use, but I think make Ajax requests in each letter typed by user is not so good, even after 4 letters, I prefer use in onblur event or even in submit form.
    mysql_escape_string is a nice tip and we can’t forget about use it. Another cool thing I didn’t know is omit the PHP end tag is a best practice.
    Thanks a lot! Cheers!


  • Henry
    Posted on

    Henry Henry

    Reply Author

    It’s always a pleasure coming to this site and learn something. Keep it up Ashley.


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Henry thanks for the kind words!