Insert Data Into MySQL: jQuery + AJAX + PHP

Advertisement:

Video tutorial illustrates insertion of data into MySQL database using jQuery and PHP, using AJAX method i.e., $.post() method of jQuery.

There are 5 shortcuts for AJAX in jQuery, ultimately calling ajax method, and by default configured to have different parameters.
$.get
$.post
$.getJSON
$.getScript
$.load

Tutorial Theme: Insert Data Into Database Without Refreshing Webpage

In this tutorial we’ll illustrate $.post method of jQuery.

HTML code
index.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<html>
<head><title>Insert Data Into MySQL: jQuery + AJAX + PHP</title></head>
<body>
 
<form id="myForm" action="userInfo.php" method="post">
Name: <input type="text" name="name" /><br />
Age : <input type="text" name="age" /><br />
<button id="sub">Save</button>
</form>
 
<span id="result"></span>
 
<script src="script/jquery-1.8.1.min.js" type="text/javascript"></script>
<script src="script/my_script.js" type="text/javascript"></script>
</body>
</html>

Here we have a form with an id called myForm, which has 2 input box.
A button with id sub. And a span tag with an id of result.

Once the user enters his/her name, age and hits Save button, the information will be sent to userInfo.php file present in the action field, via post method.

Using AJAX technique, we would make sure the form doesn’t redirect to userInfo.php file after the submission, and we’ll display appropriate message to the user on index.html file itself after the user entered values has been processed by userInfo.php file.

Database Connection code: PHP
db.php

1
2
3
4
< ?php
	  $conn = mysql_connect('localhost', 'root', '');
	  $db   = mysql_select_db('test');
?>

We connect our application to mysql server using the user name root( and we leave password field empty, as we haven’t set any password to it on our machine ).
Also select the database called test.

Create Database called test.
A table user inside the database test.
user table has 2 fields called name and age.

Processing Page code: PHP
userInfo.php

1
2
3
4
5
6
7
8
9
10
11
< ?php
 		include_once('db.php');
 
		$name = $_POST['name'];
		$age = $_POST['age'];
 
		if(mysql_query("INSERT INTO user VALUES('$name', '$age')"))
		  echo "Successfully Inserted";
		else
		  echo "Insertion Failed";
?>

Once the user hits the Save button of our form in index.html, the information is being sent to userInfo.php

Where the user entered values are being copied to local variables $name and $age.
Next a simple MySQL query is formed to insert the data into the table user.

Using mysql_query() standard php function, the MySQL query is being processed.
Using if else conditional statements, we check if the process was successful or failed. Based on which we echo the result.

jQuery code: AJAXing
my_script.js

1
2
3
4
5
6
$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); 
  });
});

Once the user clicks on the button with the id sub, $.post() method is invoked.
$.post() is a shortcut to $.ajax() method.

General Syntax:
$.post( ‘pass_data_to_this_url’, ‘data’, ‘callback_function’ );

We fetch the URL from myForm form, from its action attribute.
Serialize all the user entered input’s.
SerializeArray makes the input into property: value pair.
After the data is passed to the url and is being processed, the result will be returned back to the callback function and is caught in info variable, and is inserted inside the span tag using html() method – which is similar to innerHTML() method of JavaScript.

jQuery code: Disable Form Redirection
my_script.js

1
2
3
$("#myForm").submit( function() {
  return false;	
});

Select the form and make sure to return false upon submission.

jQuery code: Clear Input Fields
my_script.js

1
2
3
4
5
function clearInput() {
	$("#myForm :input").each( function() {
	   $(this).val('');
	});
}

input-fields-form

Write a custom function. Select the input fields and set each of its value to none or empty.
Call this function once the user clicks the submit button.

Full jQuery code
my_script.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); 
   });
 clearInput();
});
 
$("#myForm").submit( function() {
  return false;	
});
 
function clearInput() {
	$("#myForm :input").each( function() {
	   $(this).val('');
	});
}

Note that, we have called clearInput() function inside the click event of #sub.

Video Tutorial: Insert Data Into MySQL: jQuery + AJAX + PHP


You need to a flashplayer enabled browser to view this YouTube video

YouTube Link: http://www.youtube.com/watch?v=gvGb5Z0yMFY [Watch the Video In Full Screen.]



Note:
You have to implement addslash and stripslash or any other methods into your PHP code, to secure your script from SQL Injection.

If the PHP returns json encoded data, then we need to use json in $.post() method as follows:

1
2
3
4
5
$("#sub").click( function() {
 $.post( $("#myForm").attr("action"), 
         $("#myForm :input").serializeArray(), 
         function(info){ $("#result").html(info); },
         "json" );

But in our example, we are not returning json encoded data from userInfo.php file, so we do not specify encoding information in $.post() method.

Advertisements

38 thoughts on “Insert Data Into MySQL: jQuery + AJAX + PHP”

  1. Nice tutorial. I coded your example using PostgreSQL instead MySQL. It gave me some problems for some hours ’cause I used the same table name and the field name “name”. Those are reserved words and you need to escape them in order to use them.

    Anyway, I see you put the script elements after the form instead inside the head element. Playing around with it I saw that moving around just the element that include my_script.js the form is redirected to userInfo.php. To avoid that just put the first jQuery blocks inside a ready function like this:

    $(document).ready(function() {
    $(‘#sub’).click(function() {
    $.post(
    $(‘#myForm’).attr(‘action’),
    $(‘#myForm :input’).serializeArray(),
    function(info) {
    $(‘#result’).html(info)
    }
    );

    clearInput();
    });

    $(‘#myForm’).submit(function() {
    return false;
    });
    });

    The function clearInput() can be outside. Then you can put all the script elements where they ought to be:

    Insert Data Into MySQL: jQuery + AJAX + PHP

    And that’s it!

  2. @Nelson, Thanks for sharing this with us.

    But writing $(‘#myForm’).submit(function() { return false; }); outside $(document).ready(); won’t have any problem, as .submit will be invoked once the user clicks on the submit button of #myForm form.

    Look at the video. I’ll shown it working.

    Nevertheless what you’re telling is also right and it also works.

    Thanks for sharing it with us..am sure, it’ll help someone.

  3. your good satish, youve done a simple but verry helpfull tutorial it helps alot dude, cool..! keep ti up, thank you! very much!

    1. sorry, when I was copying the code from here I pasted “< ?php" and the space ruined my code 😀

      Working now. Thanks a lot

    1. @Lawko, Check with the database fields. Make sure your script is properly/correctly connected with the database.

      And do not copy paste from above article. Watch the video and work along with me. It must work.

      Check with the SQL syntax, the quotation marks etc ..these are the common places for errors.

    1. @Emrah, Mistake is in your eraysoft…..js file
      Make sure the return false is inside click event of your button.

      Orelse write everything inside $(document).ready( function() { //all your code; });

      This will solve your problem.

  4. Hello there, I came on your site because of I saw your YouTube for the first time.

    First I liked the tutorial, or how it will be called… I am new to JQuery, JSON, AJAX etc

    But second, when I clicked on more items to view and got a list of them, my browser got jammed, because of all your video’s are set on autoplay. Isn’t it? They all started anyhow…

    Maybe a good idea to NOT set them autoplay? 😉

    1. Thanks for feedback ..

      I had noticed the autoplay before, but it was a fault with the wordpress plugin I was using. I had raised ticket with the author, but had no reply ..so I fixed it myself few minutes back.

      thanks again ..

  5. Hi, I liked your tutorial, but I have run into an issue which is my form keeps redirecting to the php file. The data is saved in the db, but I cannot stop the redirection, plz any help would be greatly appreciated! I post my code:

    <input type="text" id="placemarkName" name="placemarkName" placeholder="Name:"/

    Save

    Cancel

    And the my_script.js

    $(“#savePlacemark”).click( function() {
    $.post( $(“#savePlacemarkForm”).attr(“action”),
    $(“#savePlacemarkForm :input”).serializeArray();
    });
    clearInput();
    });

    $(“#savePlacemarkForm”).submit( function() {
    return false;
    });

    function clearInput() {
    $(“#savePlacemarkForm :input”).each( function() {
    $(this).val(”);
    });
    }

    Thx!!

    1. $(“#placemarkName”).submit(function(){
      //some statements
      return false;
      });

      This should stop your form from redirecting.

      ..and you can not do $(“#placemarkName”) since placemarkName isn’t id in your case. It’s name of the form. # is used for selecting id. Dot (.) is used for selecting class.

      Please follow jQuery Tutorials is it’s order. Hope this helps

      1. Thx for your reply! I see that the html code again failed to post, sorry about that! this is another try:

        Save

        Cancel

        I made sure its the id I am using, but when clicking the savePlacemark button, I am still redirected to createPlacemark.php… Hope you can help!! Thx!

  6. Great Video and documenation man !

    i still have a question:

    is is possible to first search for a name.

    the result displays the name and age with a additional field where i could add
    a 2 for exampel which adds the 2 years to the exististing 25, so the result ist 27
    which must be stored in den db?

    many thanks in advanced

    greetings from germany
    mike

      1. @Satish, do you have time to develop that for me ? I would pay for it.
        The idea ist to make a stock inventory. i already programmed a solution in ms access but i want to get it work over a webbrowser.

        to do:
        – scan a articlenumber
        – the Article must show with actual stock or alert if not found
        – beyond a field where i could add the actual lot
        – next article

        greeting
        mike

  7. satish,

    best tutorial on using ajax to post data to mysql. i have looked all over the internet to learn how to do this. i plan on mentioning your blog and this post in an upcoming entry in my blog.

    one thing that i would like to see, a link to download the files (code) in a .zip format. i got a little confused at first as you progressed with each file.

    again, great job!!!

    cheers,

    -e

  8. hi satish your video is really simple to understand php and ajax, now can you help me to delete record (row of records) which are fetched from the database using jquery. i mean first display the records using jquery and php and then delete record from that table. thank you.

  9. I am very new to Jquery. Not sure what I am doing wrong but I followed along a half a dozen times and I still have the same problem. It is as if the browser (Chrome, EI7+) does not recognize the My_Scripts.js file. Pressing button redirects to userInfo.php. I am using XAMPP server. All files are int the htdocs folder. Is there something that needs to be configured in order for My_Scripts.js to be recognized?

      1. Nice to know that you could fix it yourself ..sorry, I was not able to help early on – this is a old tutorial and I’ve moved to angular from long back. I don’t do jQuery these days. Anyway, happy to know that my old tutorials could serve a bit too.

Leave a Reply to ejh Cancel reply

Your email address will not be published. Required fields are marked *