Insert Data Into MySQL: jQuery + AJAX + PHP


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


[youtube https://www.youtube.com/watch?v=gvGb5Z0yMFY]

YouTube Link: https://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.

      

Related posts:

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!

    Reply

  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.

    Reply

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

    Reply

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

      Working now. Thanks a lot

      Reply

    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.

      Reply

    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.

      Reply

  14. 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? ;)

    Reply

    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 ..

      Reply

  15. 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!!

    Reply

    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

      Reply

      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!

        Reply

    1. Give id to the textbox you want to clear, and then instead of $(this).val(”); put $(‘textBoxId’).val(”);

      That’s it. Hope that helps

      Reply

  18. 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

    Reply

      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

        Reply

  19. 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

    Reply

Leave a Reply

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