Check if username exists in database with AJAX

Question

I want to add a feature to my registration form which I will check if that username already exists in the database.

I have a few questions about AJAX -

1. I want to create an AJAX request on_change function, so something like this -

   $('#username').change(function() {
     $.ajax({
     url: "validation.php"
     });
   });
   

   So, as far as I understood, I must have all validations made in PHP inside the validation.php file, correct? Is there any special validation needed or can it be just simple validation with a sql statement - SELECT * FROM 'users' WHERE 'username' = '. $_POST['username'];

2. So as I understood I must pass the POST values via $.ajax too, correct? If yes, how will I be able to access them via the validation.php file?

3. After I get the results in validation.php file, how can I pass them back (true or false -- exists or doesn't exist)? I will need to pass them back, and then do an if check, if it's true - show an error that the username already exists, otherwise, don't show anything?

Answer 1

Before continuing, SELECT * FROM 'users' WHERE 'username' = '. $_POST['username']; is just ASKING for a SQL Injection. I suggest you use PHP Data objects.

So as I understood I must pass the POST values via $.ajax too, correct? If yes, how I will be able to access them via validation.php file?

Because this is a simple request, I suggest you use JQuery's method $.post(). Here's a sample based off of what you're trying to do.

$.post('validation.php',{username: $('#username').val()}, function(data){
    if(data.exists){
        //tell user that the username already exists
    }else{
        //username doesn't exist, do what you need to do
    }
 }, 'JSON');

jQuery's post method takes 4 parameters $.post(url, data, callback, datatype). In the example above, we will be posting the username with $('#username').val() to validation.php and expect a JSON response. When the request is finished, the callback function will be executed with data being the response from the request. Because we specified that that response will be JSON, we can access it just like a native object in javascript. Now let's move to validation.php

Like I stated above, I suggested you use PDO for your database driver. So in this example, I will show you a basic usage of it.

//set the headers to be a json string
header('content-type: text/json');

//no need to continue if there is no value in the POST username
if (!isset($_POST['username'])) {
    exit;
}

//initialize our PDO class. You will need to replace your database credentials respectively
$db = new PDO('mysql:host=DATABASE_HOST;dbname=DATABASE_NAME;charset=utf8mb4', 'DATABASE_USERNAME', 'DATABASE_PASSWORD');

//prepare our query.
$query = $db->prepare('SELECT COUNT(*) FROM users WHERE username = :name');
//let PDO bind the username into the query, and prevent any SQL injection attempts.
$query->bindParam(':name', $_POST['username']);
//execute the query
$query->execute();

//return the JSON object containing the result of if the username exists or not. The $.post in our jquery will access it.
echo json_encode(array('exists' => $query->fetchColumn() > 0));

Now to recap, our jQuery script will post to validation.php where it selects a username from the database. It will return a JSON object that has a key of exists that is a boolean indicating if the username already exists as a row in your database. When the request is complete via jQuery, you can do what you need based off the result of the query.

Answer 2

With reading tutorials on the internet, you can learn lots of things. I recommend you to follow the instructions on the following page: http://blog.webwizo.com/2011/05/04/simple-login-with-php-and-jquery-ajax/

You send the username via post to the specified php file, which searches for the username you have provided.

Please, use the mysql_real_escape_string function on the input string, so hackers will not be able to use a sql injection attack on your website. It works like this:

$query = "SELECT * FROM users WHERE username = '".mysql_real_escape_string($_POST['username'])."'";
if (mysql_num_rows(mysql_query($query)) > 1)
{ 
    print "inuse";
} 

Then you can check the response value in your ajax jquery function. If the website returns the value "inuse", show an error message that the username is already in use. If not, the username is available.

But as I've said, please check the tutorial and the most important thing: Use mysql_real_escape_string to prevent sql injection attacks