It is very usual that when you sign up on some big website, submit your data and data will be store in database without refreshing the page. We wonder how they did it.! We can’t directly do it with core PHP, that’s where jQuery and Ajax comes. I’m not going in detail about jQuery here, but in-short it helps us to perform some code on some event with the help of Ajax without refreshing the page, just like JavaScript which performs task at client side i.e with the help of your browser.

This blog will help you to insert data in MySQL using jQuery and Ajax with the help of PHP. I’m assuming that you know basics of jQuery.

To do so we will need two files one which contains form (you can use .html or .php file) and other which contains query to insert data into database (file with .php extension). Please follow my steps to do it.

Step 1. Create our first page index.html. Write below code to design a form and script to perform Ajax task to insert data.

index.html

<html>

<head>

    <title>Insert data onclick</title>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

    <script>

        $(document).ready(function () {

            $("#btnAdd").click(function (e) {

                /* Retrieving value from textboxes */

                var name = $("#name").val();

                var email = $("#email").val();

                $.post("insert.php", { postName: name, postEmail: email }, function (data) {

                    var insertedData = $("<p></p>").text("Inserted " + name + ", " + email);

                    $("#btnAdd").after(insertedData);

                    $("form")[0].reset();

                });

                return false;

            });

        });

    </script>

</head>

<body>

    <form method="POST">

        <table>

            <tr>

                <td>Name:

                </td>

                <td>

                    <input type="text" name="name" id="name">

                </td>

            </tr>

            <tr>

                <td>Email:

                </td>

                <td>

                    <input type="text" name="email" id="email">

                </td>

            </tr>

            <tr>

                <td>

                    <button id="btnAdd">Add</button>

                </td>

            </tr>

        </table>

    </form>

</body>

</html>

Step 2. Now create insert.php page in the same folder where you kept index.html. This page contains code for inserting data into database. I’ve already created database named dbAjaxConnectivity and it has only one table name tblUser. tblUser table has two columns one for name and other for email.

insert.php

<?php

    /* Retrieving data passed from post() method */

    $name = $_POST['postName'];

    $email = $_POST['postEmail'];

    $conn = mysqli_connect("localhost", "root", "", "dbAjaxConnectivity"); /* Connecting to database */

    mysqli_query($conn, "INSERT INTO tblUser(name,email) VALUES('$name','$email')")); /* Insert query */

?>

How it works?

So, here what happening is post() done our work to insert data. post() has three parameters.

  • First parameter is the page where you written database connectivity code (insert.php).
  • Second parameter is for, data which is send with the insert.php page using post() method. Here I have used two fields so I’m passing two fields name and email. In index.html page to get user’s name and email which is then passed to post() method I’m using name and email variables.
  • Last parameter is for, which function will be call when successfully event happened. In this I have written code to show data inserted by user.

To prevent page by refreshing write "return false" at last. The last thing is, form data will not reset until we use $("form")[0].reset(). Make sure to run this file on local host server like XAMPP or WAMP. Put all your pages into same folder.

Hope you like my blog. If you have any query comment it blow. Thank you.!