Denuwan's BLOG: Insert Data Into MySQL Database using AJAX & PHP

Hi guys,

I’m here to share one of my favorite knowledge area with you. It’s about web based application development (WAD). I think you have some basic knowledge about WAD. Basically, most of web designers developing a web based project by separating the project into two separate segments. Following diagram represent how they do it.

When we depict above diagram, WAD project can separate into two segments. The client side is a first segment. Whole thing which is interacting with client thought web browser such as Google Chrome, Mozilla Firefox can categorize into this category. Most of web application developers developing client side programing segment using frameworks such as HTML & XML. Server side is a second segment, whole process which is running on the web server, such as database data transactions are handled by server side programing segment. I think now you have some basic knowledge about WAD project.

But today I’ll introduce another segment run in-between client side and server side. This segment sort of like a bridge between HTML & PHP. This special technology we call it as AJAX. AJAX own its own advantages and disadvantages. But when we are comparing advantages and disadvantages its advantages are more powerful than disadvantages. So in the web application developing world most of developers are using this technology because of these powerful advantages. The most important advantage of AJAX is, we can execute PHP code without refreshing web page. AJAX utilized resources because of this method. But in my personal experience, AJAX errors are a little bit hard to track. But AJAX help to maintain the HTML and PHP source code separately, it would be lead to clean and well organized approach. Because of this reason AJAX mitigate the error tracking problem.

Ok, following demonstration will be helpful you get some idea about this awesome methodology. This demonstration includes 4 separate steps. Following step clearly described how to develop a small sign up page using AJAX in-between HTML and PHP.

1. Create a MySQL database using phpMyAdmin

In order to develop a sign up page, we must need to store user details into a database. In our requirement, we develop a simple registration page. So I decide to store the username and password in userAccount table. We can simply build a database and tables in phpMyAdmin with an aid of user-friendly GUI. Otherwise, you can execute the following queries in phpMyAdmin’s SQL - ‘Run SQL query/queries on server "localhost":’ option tab.

CREATE DATABASE AJAXDatabase;
CREATE TABLE userAccounts (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userName VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL)

2. Develop a client side application using HTML [signUp.html]

According to requirement, I’ll develop a web page using an HTML framework. On this webpage I’ll provide a free text field for input user name, password and confirm password, and also a submit button.

<body>
<table width="36%" height="173" border="0" align="center">
<tr>
    <td height="37" colspan="2" bgcolor="#FFFFFF">
       <div align="center"><font color="#6E6E6E" face="Segoe UI" size="+2">Sign Up !</font></div>
    </td>
</tr>
<tr>
    <td width="50%" height="29"> </td>
    <td width="50%"><font color="#FF0000" face="Segoe UI" ><div align="justify" id="responseMessageBox"></div></font></td>
</tr>
<tr>
    <td height="23">
       <div align="justify"><font color="#6E6E6E" face="Segoe UI">User Name</font></div>
    </td>
    <td bgcolor="#F2F2F2">
       <input type="text" name="userNameTextField" id="userNameTextField" style="border:none;background:none" size="40px" onchange="nullresponseMessageBox()"/>
    </td>
</tr>
<tr>
    <td height="24">
       <div align="justify"><font color="#6E6E6E" face="Segoe UI">Password</font></div>
    </td>
    <td bgcolor="#F2F2F2">
       <input type="password" name="passwordTextField" id="passwordTextField" style="border:none;background:none" size="40px" onchange="nullresponseMessageBox()"/>
    </td>
</tr>
<tr>
    <td height="24">
       <div align="justify"><font color="#6E6E6E" face="Segoe UI">Confirm Password</font></div>
    </td>
    <td bgcolor="#F2F2F2">
       <input type="password" name="confirmPasswordTextField" id="confirmPasswordTextField" style="border:none;background:none" size="40px" onchange="nullresponseMessageBox()"/>
    </td>
</tr>
<tr>
    <td height="21"> </td>
    <td align="right">
    <input type="submit" name="signUpButton" id="signUpButton" value="Sign Up"
    style="border:#36F;background:#36F;font-size:15px;color:#FFF" onclick="insertData()"/>
    </td>
</tr>
</table>
</body>

3. Develop a server side application using PHP [insertData.php]

Following code segment include SQL query for insert data into a database, in this PHP segment you can notice two variables defined on top of the code. These two variables defined for getting user name and the password. I’ll describe how to pass these two values from a signUp.html page in the final step. The rest of the code segment might be familiar with you. Because this is a simple PHP code for insert data into database by using pre-defined method in PHP such as mysql_connect, mysql_select_db, mysql_query and mysql_close.

<?php

$userName = $_GET["userName"];
$password = $_GET["password"];

               //Insert in to userAccounts Table

                   $con = mysql_connect("localhost","root","");

                   if(!$con)
                   {
                       die('could not connect: ' . mysql_error());
                   }

                   mysql_select_db("ajaxdatabase",$con);

                   //Insert Data in to User table
                   $sql ="
                   INSERT INTO `ajaxdatabase`.`useraccounts` (`id`, `userName`, `password`) VALUES (NULL, '$userName', '$password');
                   ";

                   mysql_query($sql, $con); //Execute the query
                   mysql_close($con);   //Close the connection

                   echo("Insert data Successfully !");

               //End of inserting to userAccounts Table

?>

4. Build a connection between signUp.html & insertData.php

Above 3 steps described how to develop, database, client side segment and sever side segment. In traditional approach developers are developing project by including 2 & 3 steps in same PHP page. It might lead to lots of troubles also when we want to execute PHP segment we must need to refresh the page, it's also not a good practice. Above separate approach, very clean and well organized. If we want to make some changes in server side or else do some interface changes in client side, we can easily go through either client side segment or server side segment according to our needs. In order to build a connection between server side & client side, AJAX involved. We need to make some small changes in signUp.html page. Following 2 steps describe how to make those changes.

Add onClick event

We need to add on click event in to submit button which is named as the “SignUp”. I’ll name this onclick event as the “insertData()”.

Define insertData() method

First, we need to create an XMLHttpRequest object in insertData () method. After that I’ll pass the values to insertData.php page by using document. getElementById ("NameFieldID"). value method. You should care about the valued get by using the getElementById method assign in to correct variable which is defined on top of the insertData.php page.

Get response message (Optional)

This step is optional, you can get a response message from insertData.php page and display in signUp.html page.

var responseMessage = xamlhttp.responseText;
document.getElementById("responseMessageBox").innerHTML = responseMessage;

[Click here to download sample project file]

Best Regards,
Denuwan Himanga.

Denuwan's BLOG

Labels

Friday, May 22, 2015

Insert Data Into MySQL Database using AJAX & PHP

2 comments: