Working With Database in HTML5


In this article, we will discuss how to create a database and how to easily create a table in it and how to insert data in this table. Here we create a database (mydb) and create a table (Emp) and insert data like (First name, last name, and Age) in this table using TextBoxes like this
The output will be
Step 1: First we create some controls like TextBox and Button to enter our data like this:
  1. First name: <input type="text" id="xtfname" /><br />  
  2. Last Name: <input type="text" id="xtlname" /><br />  
  3. Age: <input type="text" id="xtAge" />  
  4. <input type="button" onclick="Show()" value="Submit" /><br/><br/><br/>  
  5. <div id="result" name="result"><b>Result:</b></div> 
Step 2: Now we will write the code for the JavaScript function Show(). But first we create a database like this
var db = openDatabase('mydb', '1.0', 'Test DB', 4 * 1024 * 1024);
Here we create a database using the openDatabase method to determine whether the database exists, if the database is not available then it will create the new database. In my case, it creates the database (db). Now we will look at this method
Now we will write the function Show(). Here we use the values of the TextBox and store it in the variables like this:
  1. var a=document.getElementById('xtfname').value;  
  2. var b=document.getElementById('xtlname').value;  
  3. var c=document.getElementById('xtAge').value; 
Now we will write the transaction method (to control the transaction and for commit and rollback depending on the situation) like this:
  1. db.transaction(function (x) {  
  2. x.executeSql('Create table if not exists Emp (fname,lname,age)');   
  3. x.executeSql('insert into Emp (fname,lname,age) VALUES ("'+a+'","'+b+'","'+c+'")');   
  4. }); 
Here we create a Table Emp and insert values in this table. We insert the values of the a, b and c variables.
  1. db.transaction(function(x) {  
  2.  x.executeSql('select * from Emp', [], function(x, totals) {  
  3.   var len = totals.rows.length,  
  4.    i;  
  5.   msg = "<p><b>Total Number Of Staff: " + len + "</b></p>";  
  6.   document.querySelector('#result').innerHTML += msg;  
  7.   for (i = 0; i < len; i++) {  
  8.    msg = "<p>" + "First Name: " + totals.rows.item(i).fname + "</p>";  
  9.    msg1 = "<p>" + "Last Name: " + totals.rows.item(i).lname + "</p>";  
  10.    msg2 = "<p>" + "Age: " + totals.rows.item(i).age + "</p>";  
  11.    document.querySelector('#result').innerHTML += msg + msg1 + msg2;  
  12.    document.getElementById('xtfname').value = " ";  
  13.    document.getElementById('xtlname').value = " ";  
  14.    document.getElementById('xtAge').value = " ";  
  15.   }  
  16.  }, null);  
  17. }); 
In this code, first, we execute the select query, that will be helpful to show us all the records and then we will set the values of fname, lname, and Age in the msg, msg1, and msg2 that will show the output like this