How to Retrieve Data Asynchronously from a MySQL Database Using AJAX and PHP

So first let's go over what I did in my MySQL and it's very simple.

So in MySQL, I created (actually used an existing) database and created a table called Usernames.

This table is composed of 3 columns, ID, username, and password.

The ID column is of type INT(11), username VARCHAR(50), and password VARCHAR(50).

The structure of the table can be seen below.

The entries listed in the Usernames table is shown below.

These are all the usernames that are currently in the MySQL table.

So this is all that has to be done in the MySQL side of the code. I did this using phpMyAdmin.

You can do the same or something similar.

HTML and Javascript Code

The HTML and Javascript code to create the username and password text boxes as well as to call an asynchronous request when the user clicks away from the text box is shown below.

So this is all the Javascript and HTML code needed to create asynchronous requests for our username registration form.

We'll now go through each line of code so that you understand what each does.

So we add <script> tag to our code to indicate that we're going to add Javascript to the page. Any type of Javascript entry requires an opening and closing script tag.

We then create a function called checkUsername(). This function, as it's named, is designed to check the username that a person enters to see whether the username has been registered already or not. If it has, the user cannot take it. If it hasn't, the username is available. If this text box is left empty, a message will be shown telling the user that a username must be entered into this field. This is how many real-world registration forms work for many of the biggest companies and websites in the world.

The first thing we do in this function is create a variable called request. This variable will represent a request object, which is absolutely imperative in order for the browser to request communication with the server. Otherwise, AJAX is impossible. The request object is pretty much the heart of AJAX.

The most common way to create a request object is through the line, request= new XMLHttpRequest(); Through this line, most browsers can create a request object, which then can be called so that a browser can communicate with the server. However, this line may not work for all browsers. Therefore, we use try catch statements to create a request object by other means in the event that the browser cannot do so through the first way. So we try also the tryMicrosoft function and the otherMicrosoft function. These are more specific to Microsoft browsers such as Internet Explorer for creating a request object. If all the methods fail, the last method failed is run, which makes the request object equal to null; if this happens, asynchronous communication cannot occur because the browser has no way of contacting the server. However, if you are using any type of major browser, this shouldn't happen. You should be able to create a request object.

You'll see how this request object is used later in the code. But if you want a full in-depth article on request objects, see How to Create a Request Object in AJAX.

So after we've created a request object, we then create a variable called url, which contains the name of the PHP file that processes our data. I named my file, process.php.

Next, a variable named user has been created which stores the value that the person enters into the username text box.

We then create a variable called pw, which stores the value of the password that the user enters into the password text box.

We then create a variable called vars. This variable will stores the values of the username and the password. This variable is written as a name=value pair. The name=value pair is the username. Since the username equals the variable user, the total statement "username="+url. In between each name=value pair has to be an ampersand (&). This separates the different name=value pairs. The second name=value pair that we send is the "password="+pw.

This variable vars is very important and in fact can be seen as the heart of the PHP file, process.php, that processes our request. Using this vars variable, we can have PHP parse this variable and extract the data from it. Thus, from this vars variable, we can know what a user enter into these username and password text boxes. We'll show how to do this in PHP when we get to the PHP code.

Next we take the request object and use the open method on it. This open method decides whether we use the POST or GET method. It takes the URL that we want parsing the data that we submit to. And it determines whether the request should be asynchronous or not. By making the third parameter true, the request becomes an asynchronous request which is what we desire.

The line, request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");, is just for character encoding of the variable vars we are sending. For the vars variable, it is organized in name1=value1&name2=value2&name3=value3 order. Now say for instance, that the value had the ampersand symbol (&), this could throw off the vars variable and cause an error. To protect against this, we put this line in the code.

Next, we take the request object and find its onreadystatechange property and set it equal to function(). The onreadystatechange property is a property that changes as the server processes a request by a browser. It has 4 states, 1 is the first inital state and 4 means it's absolutely ready. If request.readyState is equal to 4, this means that the server has obtained the request object from the browser and is ready to process the data sent. There is also another property named status that we can check to determine if everything is in place. If ready.status is equal to 200, this means that all is OK. Once these 2 parameters are in place, we create a variable called return_data and set it equal to request.responseText. The responseText variable is the data which the PHP file returns. Let's say you have a PHP file processing this request and in turn you set so that the PHP file echos a certain statement such as, "Everything has been processed well." This statement then goes into the responseText, which receive back after everything has been processed on the server side. So the return_data is very important. It's the output of the PHP file that processes the request on the server side.

We then take the Javascript document.getElementById() function to get the element on our HTML page that has the Id of status. We place this right next to the username text box that has the HTML label tag. This label tag has the Id of status. We access the contents of this label tag through .innerHTML. This property lets us write data to the HTML label tag that has the Id of status. We then set it equal to return_data, which is the data that the PHP file echoes out.

The last statement, request.send(vars);, is the statement that actually executes the sending of the vars variable, so that the PHP file can process it. So it isn't actually sent until this statement.

So this is all that is required on the HTML and Javascript.

The last thing we have is the PHP code.

PHP Code

So the last thing we have is the PHP code, which is the server side language that can actually speak to the server.

Javascript cannot manipulate server information. A server side language is needed, and that's PHP.

So we need a server side language to ge the data from the server, so that we can process this data and output a result based on this data.

The PHP code that we use is shown below.

So this is our PHP code above.

The first line uses a PHP variable called $username_entered. It gets the value of the username that the person has entered in the form. Remember that the vars variable contained, var vars= "username="+user+"&password="+pw; PHP is able to extract the data that the person has entered into the username text box through the name=value pair. So all we have to do is create a PHP variable and set it equal to the value of the name of the pair. So we set the $username_entered variable to $_POST['username']; PHP will then get the value that the person has entered into the username form. The same is true for password. We create a variable named $password_entered and set it equal to $_POST['password']. Password is the name attribute of the password entered into the password text box.

Since we are retrieving data using PHP from a MySQL database, we have to set up connection to the database. This requires a username, password, hosting name, database, and table name (of the table we want to retrieve data from).

We then connect to this database via the mysql_connect() function and @mysql_select_db() function.

We then create a variable $result which is a query to the table we selected. We use the mysql_query() function to query this table. We select the column username on our table and use the WHERE clause to find out if the username column has a username that the person has entered into the form. So this is the query. We then count the number of rows that the table has returned based on this query using the mysql_num_rows() function. If the number of rows is 0, we know that this username doesn't exist in the table and, thus, the user can selct it, since it's available. Else if the count isn't 0, this means that this username exists already and cannot be taken.

So now we use a series of if statements to determine what our PHP file will output.

The first we want to do is cover whether the username is empty or not. This covers the case that the user clicks on the username text box, inputs nothing, and clicks away from it. In the event that the user does this, we echo out the statement, "A username must be entered into this field". Else if the username text box isn't empty and the count is equal to 0, we echo out that the username is available. The $count variable is equal to 0, because the username doesn't exist. The query function went through the table and didn't return an instance of that username which was entered. The next statement, an else statement, echos out that the username is already taken if the count isn't equal to 0. In this case, we echo out that the username is already taken and to please select another username.

So this is all that is required to retrieve data asychronously from a MySQL database using PHP and AJAX.

This form has been greatly simplified. It doesn't have any functionality, because it's just made to demonstrate AJAX. It doesn't process the data and logs you in. I also added no functionality to the password text box, so it won't work. It was just done to demonstrate the power of AJAX with the username text box. So keep that in mind.

But this code shows the huge power of AJAX in applications for the web to retrieve data asynchronously, for all asynchronous requests.

Related Resources

How to Insert Data into a MySQL Database Using AJAX and PHP