How to Insert Data into MySQL Database using jQuery / AJAX / PHP


In this tutorial we will learn how to insert data into mysql database using PHP as server-side programming language,jQuery and Ajax. jQuery has an ajax method. In this tutorial we will be use jQuery method i.e., $.post() in form for inserting the records in to database.

We will use CodeLobster as text editor or you can use any text editor i.e. Dreamweaver etc.

Prerequisites:

Let’s get started.

  1. Open a text editor for example “CodeLobster”. Create a new file from” File > New > HTML” or press short key (Ctrl+N) and save this file as “index.html”. Go to “File menu > Save As” or press short key(Ctrl+Alt+S).
    codelobster text editor
  2. Add some basic HTML tags and make a form in “index.html” page that the user will be viewing it.
    adding html to a file

    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>

    In this page we will make a form with an id “myForm”, which has two input fields i)”name”ii)”age”, a save button iii) id “sub”, and  iii) span tag id ”result”.

    The text fields we use for insert data and submit button will use to submit this form and span tag “<span>”tag with an id “result” using to display information for success fully and unsuccessful form submission.

  3. Run your “Wamp server” from Start menu and open “phpMyAdmin” in browser window.
    URL:http://localhost:8081/phpmyadmin/
    getting into localhost
  4. Create a new database called “test” using phpMyAdmin. Create a new table name “user” and gives the number of columns value “2”.
    creating a new database
  5. Select “user” table and create two fields respectively i) “name” and ii) “age”. Select type “VARCHAR” for “name” and “INT” for “age” fields. Insert length “15” for name and “3” for age.
    creating fields in user table
  6. Create a new file from File > New > PHP or press short key (Ctrl+N) and save this file as db.php. Go toFile menu > Save As or press short key (Ctrl+Alt+S).
    creating the connection string

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

    Now we will make connection with data base ”test”, with using the server name “localhost”, user name“root” (and we leave password field empty, as we have not set password).

  7. Create a new file from File > New > PHP or press short key (Ctrl+N) and save this file as userInfo.php.Go to File menu > Save As… or press short key (Ctrl+Alt+S).
    saving a file with php code

    1
    2
    3
    4
    5
    6
    7
    8
    9
    <?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";
    ?>

    This file includes all the server-sided code for inserting user data into mysql database “test” Once a user hits the save button on “index.html” page, the information will send to “userInfo.php”.

  8. Create a new file from File > New > JS or press short key (Ctrl+N) and save this file as “my_script.js”.
    let's save our file

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

    Place this code in “my_script.js” file. Once the user clicks “save” button from “myForm” form on index.html page. The $.post() method will invoke.

  9. In this step we will add scripts to disable form redirection and clear input fields upon form submission.
    script to disable redirection

    1
    2
    3
    4
    5
    6
    7
    8
    9
    $("#myForm").submit( function() {
      return false;
    });
    function clearInput() {
        $("#myForm :input").each( function() {
           $(this).val('');
        });
    }

    Add this code in “my_script.js” file. The first block selects the form “#myForm” and make sure to return false upon submission and the other second block “function clearInput()” runs once the user click on submit button. It selects the input fields and set each of its value to clear or empty upon form submission.

  10. Full jQuery code “my_script.js” file.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    $("#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('');
        });
    }

Like this:

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s