Overview and Objective
Today we shall see how to insert data into MySQL database using jQuery and PHP. jQuery has an Ajax method. We will be using the shortcut that is the post method of jQuery to implement the Ajax. Addifices HTML form and the insertion of user entered values into the MySQL database.
I will take a form and give it an ID called my form. See here we have taken post as the method. I'll take an input box and name it as name itself because the user would be entering his or her name in this input box which is a text box. The second input box will hold age of the person. Now the button to submit the data I'll give it an ID called Su meaning submit. Also let me take a span here so that I could display some information like the record was inserted or it wasn't inserted. So this is our HTML form.
Connecting to MySQL (db.php) and Creating the Table
Now before that I'll connect our form, that is HTML, to the database. If you don't know PHP please visit the PHP category on our blog that is techno.com. This is a simple tutorial; you need not know much about PHP here, just the connection. The first line of code is connecting to the SQL server with the username root. We do not have any password so I left it empty. Let me run the server first, Wamp server. I'll open PHP MyAdmin. Here we have a database called test. I'll create a table called user with two fields in it. First one being the name which is varchar; I'll give the length as 15. Next age which is an integer, a length of three is okay for me right now. Okay we have a database called test inside which we have a table called user. Now select the database test and get connected to it. I'll save it as db.php.
Writing PHP to Insert User Data (user_info.php)
Now the actual code to save the user entered data into database goes here. Before that let me connect to the database by including db.php file. So we got connected to the database test. Once the user hits the save button the name and the age gets passed to this file using POST method which we will store inside a local variable called name, similarly for the age. Now write a simple SQL statement that is insertion: insert into user is the table name, so insert into user values. Our user table has two values, two fields in it namely name and age which we would pass here the user entered name and age. Well I'll wrap this inside a PHP function called mysqli_query which processes this MySQL statement. I'll also check if it was successful. If the execution or the processing of this MySQL statement was successful I'll say successfully inserted. If not I'll say maybe like something like insertion fail. So these two things, either successfully inserted or insertion failed, will be returned to my script that is the script we would write next.
Implementing AJAX with jQuery.post and Serialization
User hits submit, the user entered values that is name and age would be passed to user_info.php which we would collect inside the local variables name and then age and by forming a simple MySQL statement we would execute it and insert inside the user table. Now before proceeding let's see how it works on our browser. Post inside post folder and write technotip or company has been running for 2 years now. Save. See, inserted successfully. Inserted message comes but the page gets redirected to user_info.php which we don't want. We want to aify that which means it shouldn't get redirected; all the things should appear on the same page that is index.php instantly without reloading the page. Once the user hits the submit button that is the button which has an ID called Su we will call an anonymous function. Now before inserting user entered values we want to serialize these data, so I'll serialize the data and store it inside a local variable called data. So get select our form and its input fields, then we have a method called serializeArray which formats the input data into property and value. Then select the file to which we need to pass the user entered data. We select the form and its attribute action which has the file name to which we need to pass the user entered data. Next the serialized data. Once this data has been sent to this file it returns something which would be stored as info here, the output from user_info.php after processing. So this is user_info.php which has these codings. After execution it either tells successfully inserted or failed. It should be stored inside info. So using this output we need to echo or else okay we would append it to the span tag which has an ID called result. So HTML I'll append this info variable which has the output of user_info.php. Instead of writing this data I'll just cut this out and paste it here to make it look compact. So ultimately you have only one line of code to learn here. And an important thing that is once the user hits the submit button it shouldn't get redirected to user_info.php file. So I'll select my form that is the ID of the form inside our index.html and the submit I'll write the function and tell return zero which means don't get redirected to any other pages once the user hits this button which has an ID sub. That's it.
Now open index.php file. Satish my name 25 years save. Successfully inserted. Look at the database here. Okay it has been inserted successfully and this file didn't get redirected to some other pages. Now the fault is name my name and age is still here which doesn't make any sense once the user hits the submit button. Let us clear those fields. I'll write a custom function called clearInput. Select the form that is my form and its input fields, all the input fields. So loop through each of the input field using each, write an anonymous function and then set its value. The input box being looped that is being iterated now which is being selected its value should be nothing which makes it clear that is empty. Now once you write it call this clearInput function inside this submit or inside here click. So for the click event of the button called clearInput let's look at it. I'll say sha 24 hour years page save successfully inserted and the box has been cleared.
Next Steps and Wrap-Up
Okay practice this. For next videos we will show how to retrieve the data from the database using JSON, that is JavaScript Object Notation. It would be more interesting and learning insertion and extraction of data from the database would make all the fun and make your applications like desktop application. So please share this video with your friends on Facebook, Google+, LinkedIn, Twitter, etc. Also email your friends about techno.com. Please keep subscribed to techno.com and to our YouTube channel. Also like this video on YouTube.
