Insert, Update and Delete Data in HTML5

Introduction

Here we first create a table and insert some data into it. The data will be shown as output and here we will find an edit button and delete button by which we can edit or delete the data like this.

Now we will write the code. In order to create the form, use the following procedure.

Step 1. First, we will create the controls, like TextBox and Button, to insert the data.

<div>
    <h1>Insert Data, Update Data and Delete Data In HTML5</h1>
    <input type="hidden" id="id" />
    <b>First name:</b><input type="text" id="firstName" /><br />
    <b>Last name:</b><input type="text" id="lastName" /><br />
    <b>City:</b><input type="text" id="city" /><br />
    <b>State:</b><input type="text" id="state" /><br />
    <b>Phone:</b>
    <input type="text" id="phone" /><br />
    <b>Email:</b><input type="text" id="email" /><br />
    <button onclick="InsertData()">Insert Data</button>
    <button onclick="UpdateData()">Update Data</button>
    <button onclick="ClearData()">Clear</button>
    <br />
    <br />
    <br />
    <div id="details"></div>
</div>

Here we will use our ID field as a hidden field since in this example it will be incremented automatically.

<script>
    var id = document.getElementById('id');
    var firstName = document.getElementById('firstName');
    var city = document.getElementById('city');
    var state = document.getElementById('state');
    var email = document.getElementById('email');
    var lastName = document.getElementById('lastName');
    var phone = document.getElementById('phone');
    var results = document.getElementById('details');
</script>

Step 2. Now we will write the following code in the <script> tag like this.

Here we will create the variables for each field. Now we will create the database like this.

var db = openDatabase("MYDATABASE", "1.0", "MYDB", 4 * 1024 * 1024);
var mydata;
CreateTable();
function CreateTable() {
    db.transaction(function (tx) {
        tx.executeSql("create table if not exists Emp (id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, city TEXT, state TEXT, phone TEXT, email TEXT)");
    });
}

Here we create an Emp table in which we declare id as our primary key, it is also useful in order to delete and update data in the database. Now we write the ShowData() function by which we will show our data.

function ShowData() {
    results.innerHTML = '';
    db.transaction(function (tx) {
        tx.executeSql("SELECT * FROM Emp", [], function (tx, result) {
            mydata = result.rows;
            for (var i = 0, item = null; i < mydata.length; i++) {
                item = mydata.item(i);
                results.innerHTML +=
                    '<b>First Name : </b>' + item['firstName'] + ', ' + '<b>Last Name :</b> ' + item['lastName'] + ', ' + '<b>City : </b>' + item['city'] + ', ' + '<b>State :</b>' + item['state'] + ', ' + '<b>Phone: </b>' + item['phone'] + ', ' + '<b>Email : </b>' + item['email'] + '      <img height="20" width="20" src="Edit.jpg" onclick="LoadMyData(' + i + ')" />' +
                    '<img height="20" width="20" src="delete.jpg" onclick="deleteRecord(' + item['id'] + '")/><br/>';
            }
        });
    });
}

Here we first get the data from the Emp table and then we will show it. Here we will use two images, one for editing and the other for deleting. Here we will specify the two functions, one is LoadMyData() and the other one is deleteData().

First, we will write the LoadMyData() function that will be helpful to load the data into the TextBoxes.

function LoadMyData(i) {
    var item = mydata.item(i);
    firstName.value = item['firstName'];
    lastName.value = item['lastName'];
    city.value = item['city'];
    state.value = item['state'];
    phone.value = item['phone'];
    email.value = item['email'];
    id.value = item['id'];
}

Now we will write the code for deletData(), so when we click on that the particular record will be deleted from the database.

function deleteData(id) {
    var deleteStatement = "delete from Emp where id=?";
    db.transaction(function (tx) {
        tx.executeSql(deleteStatement, [id], ShowData(), ShowErrorMessage);
    });
    ClearData();
}
function ClearData() {
    firstName.value = '';
    lastName.value = '';
    city.value = '';
    state.value = '';
    email.value = '';
    phone.value = '';
    id.value = '';
}

Step 3. Now we will write the code to insert the data into the database.

<button onClick="InsertData()">Insert Data</button>
function InsertData() {
    var insertStatement = "insert into Emp (firstName, lastName, city, state, phone, email) values (?, ?, ?, ?, ?, ?)";
    db.transaction(function (tx) {
        tx.executeSql(insertStatement, [firstName.value, lastName.value, city.value, state.value, phone.value, email.value], showdatacleardata, ShowErrorMessage);
    });
}

In this way, we will insert the data into the database.

Step 4. Now we will write the code to update the data.

function UpdateData() {
    var updateStatement = "UPDATE Emp SET firstName = ?, lastName = ?, city = ?, state = ?, phone = ?, email = ? WHERE id = ?";
    db.transaction(function (tx) {
        tx.executeSql(updateStatement, [firstName.value, lastName.value, city.value, state.value, phone.value, email.value, id.value], showdatacleardata, ShowErrorMessage);
    });
}

By which we will easily update the data in the database. So when we click on the Edit Button the data will be shown in the TextBoxes like this, now we will update the data by clicking on the update button.