How to insert data to MySql from PHP using jQuery AJAX

What Will I Learn?

You will learn how to get data from html element using jQuery selector
You will learn how to send data using jquery ajax
You will learn how to insert data to mysql

Requirements

You have basic about HTML
You have basic about PHP
You have basic about MySQL
You have basic about JavaScript
You have to install or host the jQuery file, You can add jQuery CDN if you don't want to install or host it
To practice this tutorial you should have a webserver, text editor and a browser. In this tutorial I use XAMPP for webserver, Visual Studio Code for text editor and Google Crome for browser.

Difficulty

Intermediate

Tutorial Contents

Ajax is used to perform actions on the application via the browser and forwarded to the webserver. Usually after clicking the submit button on the form will refresh the browser, AJAX can submit to the webserver without refresh the browser, even ajax can access the database in one event. For more detail, Let's pay attention steps bellow :

ReActive your webserver. create new folder in Xampp/htdocs/ if you use XAMPP. While you use Linux create it at var/www.
Open your text editor software, Create new file and save as index.html in the previous folder that created by you.

Creating Form

Add the HTML element as usual.

<html>
<head>
<title>Insert</title>
</head>
<body>
</body>
</html>

Create a simple form to insert data, For Example I just create two textfield to insert name and email.

<label>Name</label>
    <input type="text" id="name"> 
    <label>Email</label>
    <input type="text" id="email">
<button type="submit" id="button">SAVE</button>

Adding jQuery AJAX

As we know, to use jQuery we need to install or host it. Or we can also add jQuery CDN if we don't want to install or host it. we also have many choices CDN that we can use, either Google CDN or Microsoft CDN. For more Detail you can visit jquery official web. In this tutorial I use Google CDN. Add the CDN in <head> element.

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

To write the jQuery Script we should open <script> tag. You can put it in <head> element or in` element.

<script></script>

Before add more event in jQuery. There is an event that we should add for the first. It is ready() event. this event to make sure that all html elements are loaded. if it is, then it will load jQuery script. So, all jQuery Script must write in this function event.

$(document).ready(function(){
});

Select the button element, then add click event function

$("#button").click(function(){
 });

Add the variable with the value is the data from textfield element that you will insert into database.

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

Start to add AJAX function

$.ajax({
                    url:'insert.php',
                    method:'POST',
                    data:{
                        name:name,
                        email:email
                    },
                   success:function(data){
                       alert(data);
                   }
                });

code explanation : url:'insert.php', url that we will send the data then send to mysql. method:'POST', the method to send data you can use POST or GET. data:{name:name,email:email }, the data that you will send. success:function(data){alert(data);} the statement that will execute if insert data proses is success.

Creating DATABASE

Open phpmyadmin if you use XAMPP to create a new database. Or open your sql then create new database

CREATE DATABASE INSERT;

Create table for sample .

CREATE TABLE `insert`.`data` ( `id` SMALLINT NOT NULL AUTO_INCREMENT , `name` VARCHAR(30) NOT NULL , `email` VARCHAR(30) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

If you don't create it manually you can DOWNLOAD slq file here and import it into your sql

Creating PHP file

Create new file on your text editor application and save as insert.php [the url that you mention on AJAX function].
Open MySql connection.

<?php
$conn = new mysqli('localhost', 'root', '', 'insert');

Add the variable to accommodate data that send from the FORM by method POST

$name=$_POST['name'];
$email=$_POST['email'];

Add the SQL Query to insert data to database.

$sql="INSERT INTO `data` (`id`, `name`, `email`) VALUES (NULL, '$name', '$email')";

Execute the MySQL Query

if ($conn->query($sql) === TRUE) {
    echo "data inserted";
}
else 
{
    echo "failed";
}

Save all file and run index.html on your browser. Try to input and submit data.
Data will submit without reloading page as usuall. Now try to see on your database, If data was inserted that mean you are success to add jQuery Ajax.
Finish, You can copy full code bellow :
index.html

<html>
<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <title>Insert</title>
</head>
<body>
    <label>Name</label>
    <input type="text" id="name"> 
    <label>Email</label>
    <input type="text" id="email">
    <button type="submit" id="button">SAVE</button>    
    <script>
        $(document).ready(function(){
            $("#button").click(function(){
                var name=$("#name").val();
                var email=$("#email").val();
                $.ajax({
                    url:'insert.php',
                    method:'POST',
                    data:{
                        name:name,
                        email:email
                    },
                   success:function(data){
                       alert(data);
                   }
                });
            });
        });
    </script>
</body>
</html>

insert.php

<?php
$conn = new mysqli('localhost', 'root', '', 'insert');
$name=$_POST['name'];
$email=$_POST['email'];
$sql="INSERT INTO `data` (`id`, `name`, `email`) VALUES (NULL, '$name', '$email')";
if ($conn->query($sql) === TRUE) {
    echo "data inserted";
}
else 
{
    echo "failed";
}
?>

DOWNLOAD FULL CODE HERE

Curriculum

Posted on Utopian.io - Rewarding Open Source Contributors