How to update data mySql from PHP without reload page using jQuery Ajax

sogata (64)in #utopian-io • 8 years ago (edited)

What Will I Learn?

You will learn how to get data from PHP form using jQuery selector
You will learn how to send data using AJAX
You will learn how to execute PHP statement from AJAX.
You will learn how to recieve back data from PHP

Requirements

Basic Knowledges about HTML
Basic Knowledges about PHP
Basic Knowledges about JavaScript
Basic Knowledges about MySql
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

jQuery AJAX is a method that uses JavaScript to send and receive data from a server without having to refresh the page. In this tutorial we will try to update data in database MySql from PHP form without reloading page using jQuery AJAX method.

$activate webserver

ReActive your webserver. create new folder in Xampp/htdocs/ if you use XAMPP. While you use Linux create it at var/www.

$Creating Database

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

CREATE DATABASE update;

Create a sample table to update. Here I create moderator table

CREATE TABLE `update`.`moderator` ( `id` SMALLINT NOT NULL AUTO_INCREMENT , `name` VARCHAR(20) NOT NULL , `category` VARCHAR(20) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Insert some data to moderator table

INSERT INTO `moderator` (`id`, `name`, `category`) VALUES (NULL, 'shreyasgune', 'tutorial'), (NULL, 'podan.rj', 'graphic')

If you Don't want to create it manually you can download the slq file here

$Creating update Form

Open your text editor software, Create new file and save as index.php in the previous folder that created by you.
Add the HTML element as usual.

<html>
<head>
<title>UPDATE DATA</title>
</head>
<body>
</body>
</html>

Add title on <h3>

<h3>UPDATE DATA</h3>

Create a button to sumbit and update data.

 <button type="submit" id="update">UPDATE</button>

$Select Data from Database and display it on update form.

As example, we just select the data with the id=1. You can develop this by making the choice of id to be updated.

Open the PHP Script in <body> above the button element of index.php file.

<?php
?>

Open MySql connection.

$conn = new mysqli('localhost', 'root', '', 'update');

Add the sql Query to select data from databases

$sql = "SELECT * FROM moderator where id=1";

Execute the sql query

$result = $conn->query($sql);

Open while loop to display all data from database. Use mysqli_fetch_assoc() function to get data in array. Then print the data in input element.

while ( $row=mysqli_fetch_assoc($result)) {       
           echo  'Moderator :<input type="text" id="mod" value="'.$row['name'].'">';         
           echo  'Category :<input type="text" id="ctgr" value="'.$row['category'].'">';
        }

Save and run the file.

$Adding jQuery AJAX for updating data

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 <body> 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 and add the click event function

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

Select the update data in input text element and save it in variable.

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

Add AJAX function

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

Explanation Script : url:'update.php', url address that will send the data. method:'POST', sending method. data:{uname:uname, ctgr:ctgr}, getting data from JS variabel. success:function(response) get data from PHP file after all function success, the response is the parameter to get back data from valid.php. alert(response); display data in alert.

$Create The PHP file

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

$conn = new mysqli('localhost', 'root', '', 'update');

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

$name=$_POST["name"];
$ctgr=$_POST["ctgr"];

Add the sql Query to update data from database, for example we just update data with id=1.

$sql="UPDATE moderator set name='$name', category='$ctgr' where id=1";

Execute the sql query in if condition, if succes will be print "DATA updated"

if($conn->query($sql)===TRUE){
    echo "DATA updated";
}

$Testing

Save all file, then run index.php in your localhost.
Try to edit a field then click the update button. For example edit the category field.
See the the dtabase before update and after update

before:

after

FINISH. For the full code you can copy bellow:
index.php

<html>
    <head>
        <title>Update Data</title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    </head>
    <body>
        <center>
        <h3>UPDATE DATA</h3>
        <?php
        $conn = new mysqli('localhost', 'root', '', 'update');
        $sql = "SELECT * FROM moderator where id=1";
        $result = $conn->query($sql);
        while ( $row=mysqli_fetch_assoc($result)) {       
           echo  'Moderator :<input type="text" id="mod" value="'.$row['name'].'">';         
           echo  'Category :<input type="text" id="ctgr" value="'.$row['category'].'">';
        }?> 
        <button type="submit" id="update">UPDATE</button>
        </center>
        <script>
            $(document).ready(function(){
                $("#update").click(function(){
                    var name=$("#mod").val();
                    var ctgr=$("#ctgr").val();
                    $.ajax({
                        url:'update.php',
                        method:'POST',
                        data:{
                            name:name,
                            ctgr:ctgr
                        },
                        success:function(response){
                            alert(response);
                        }
                    });
                });
            });
        </script>
    </body>
</html>

update.php

<?php
$conn = new mysqli('localhost', 'root', '', 'update');
$name=$_POST["name"];
$ctgr=$_POST["ctgr"];
$sql="UPDATE moderator set name='$name', category='$ctgr' where id=1";
if($conn->query($sql)===TRUE){
    echo "DATA updated";
}
?>