Introduction

Whatever we see on our website, it is either saved within HTML or being fetched from a table. The HTML has a script file attached with it which calls backend, where SQL codes are written. These SQL codes interact with database. In this article we will learn how to update an entry in SQL using PHP.

Syntax

UPDATE <table name> SET <field name 1> = <value 1> , <field name 2> = <value 2> WHERE <key 1> = '{<local variable 1>}' and <key 2> = '{<local variable 2>}'

How to update an entry in SQL using PHP

In PHP, we need to follow given mandatory steps before we start with any SQL operation:

1. Define database details
   We need to initialize a database connection and for that we will need its detail. The prerequisite for this will include setup of a database in myphpadmin (if we are using local server). There we will have to create a database and tables.
   In case you have done setup of database and tables in local server: https://localhost/phpmyadmin/

Then, following will be the database details written in PHP

$servername = "localhost";
$username = "root";
$dbname = "<your_database_name>";

2. Create connection
   Once the required connection details are mentioned, we will try to create a connection to database using below query:

   $conn = new mysqli($servername, $username,"", $dbname);

3. Check connection
   Now the connection setup is done, we need to perform a check to confirm the same using below query:

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

4. Perform Query
   This is the part where we write SQL query to update data in PHP. In the below query, firstly we get the data coming from frontend in a local variable $obj, and then separate each of the fields according to the column in table. Once we have all the data, then we create a SQL query in a local variable $sql, and pass the same in a function mysqli_query as arguments with connection variable. If the resulted query is successful then we echo (i.e. send back to frontend) the success message, else we return a message ‘Update failed’:

   $obj = json_decode($_POST["data"]);
       $Date = $obj->Date;
       $Type = $obj->Type;
       $Reference = $obj->Reference;
       $Received = $obj->Received;
       $ReceivedDate = $obj->ReceivedDate;
       $Mode = $obj->Mode;
       $Staff = $obj->Staff;
       if(isset($obj->ChequeNo)){
           $ChequeNo = $obj->ChequeNo;
       }else{
           $ChequeNo = '';
       }
       $guid = $obj->guid;
       $sql = "UPDATE transactions SET Type = '{$Type}', Received = '{$Received}', Mode = '{$Mode}', Staff = '{$Staff}', ChequeNo = '{$ChequeNo}' WHERE Date = '{$Date}' and guid = '{$guid}' and Reference = '{$Reference}' and ReceivedDate = '{$ReceivedDate}'";
       if(mysqli_query($conn,$sql)){
       $dataArray[0] = 'Update Successful';
       $dataArray[1] = '200';
       echo json_encode($dataArray);
       }
       else{
       $dataArray[0] = 'Update failed';
       $dataArray[1] = '401';
       echo json_encode($dataArray);
       }

5. Close Connection
   Once we are done with our operation, then it is a good practise to close the database connection using this simple query:
   

   $conn->close();

Full code:

function onUpdateTransaction(){
$servername = "localhost";
$username = "root";
$dbname = "arrowDB";
// Create connection
$conn = new mysqli($servername, $username,"", $dbname);
// Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    $obj = json_decode($_POST["data"]);
    $Date = $obj->Date;
    $Type = $obj->Type;
    $Reference = $obj->Reference;
    $Received = $obj->Received;
    $ReceivedDate = $obj->ReceivedDate;
    $Mode = $obj->Mode;
    $Staff = $obj->Staff;
    if(isset($obj->ChequeNo)){
        $ChequeNo = $obj->ChequeNo;
    }else{
        $ChequeNo = '';
    }
    $guid = $obj->guid;
    $sql = "UPDATE transactions SET Type = '{$Type}', Received = '{$Received}', Mode = '{$Mode}', Staff = '{$Staff}', ChequeNo = '{$ChequeNo}' WHERE Date = '{$Date}' and guid = '{$guid}' and Reference = '{$Reference}' and ReceivedDate = '{$ReceivedDate}'";
    if(mysqli_query($conn,$sql)){
    $dataArray[0] = 'Update Successful';
    $dataArray[1] = '200';
    echo json_encode($dataArray);
    }
    else{
    $dataArray[0] = 'Update failed';
    $dataArray[1] = '401';
    echo json_encode($dataArray);
    }
}

Calling PHP to update an entry in SQL from frontend

In above steps we have connected to SQL, fetched data and sent in response. The above function is only triggered once the UI calls it. In UI, we write given code to perform the required operation:

// Update Transaction Table		
onLogDataUpdate: function (oEvent) {
var http = "http://";
var uri = http + "localhost/<location_of_your_php_file/name_of_your_php_file>";
            var that = this;
            var obj = oEvent.getSource().getBindingContext("logOvpModel").getObject();
            var data = JSON.stringify({
                "Date": obj.Date,
                "Type": obj.Type,
                "Reference": obj.Reference,
                "Received": obj.Received,
                "ReceivedDate": obj.ReceivedDate,
                "Mode": obj.Mode,
                "ChequeNo": obj.ChequeNo,
                "Staff": obj.Staff,
                "guid": obj.guid
            });
            // read msg from i18n model
            var sMsg = "Do you want to Update data?";
            MessageBox.confirm(sMsg, function (rValue) {
                if (rValue === "OK") {
                    $.ajax({
                        url: that.uri,
                        type: "POST",
                        data: {
                            method: "onUpdateTransaction",
                            data: data,
                        },
                        dataType: "json",
                        success: function (response) {
                            if (response[1] === "401") {
                                MessageBox.error(response[0]);
                            } else {
                                MessageBox.success(response[0]);
                            }
                        },
                        error: function (XMLHttpRequest, textStatus, errorThrown) {
                            MessageBox.error("Unable to Delete Log Data")
                        }
                    });
                }
            });
        }