Local Sqlite database Mobile Applicaiton

1.Create index.html & add

<!DOCTYPE html PUBLIC>

<html>

<head>

<title>Exercise 3</title>

<link rel=”Stylesheet” href=”style.css”/>

<script type=”text/javascript” src=”http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js”></script&gt;

<body>

<h1>CONTACT FORM</h1>

<form id=”mycontact”>

<fieldset>

<legend>Your details</legend>

<ol>

<li>

<label for=”username”>Name</label>

<input id=”username” type=”text” placeholder=”First and last name” required autofocus pattern=” “>

<input type=”hidden” id=”id”/>

</li>

<li>

<label for=”useremail”>Email</label>

<input id=”useremail” type=”text” placeholder=”example@domain.com” required pattern=” “>

</li>

</ol>

</fieldset>

<button id=”btnReset” type=submit>Reset</button>

<button id=”submitButton” type=”submit”>SAVE</button>

<button id=”btnUpdate” type=submit>UPDATE</button>

<button id=”btnDrop” type=submit>DROP</button>

</form><br />

<div id=”results”></div>

</body>

<script type=”text/javascript” src=”js.js”> </script>

</html>

<span style=”font-family: Calibri; font-size: small;”>

2.Create Js:

// Declare SQL Query for SQLite

var createStatement = “CREATE TABLE IF NOT EXISTS Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, useremail TEXT)”;

var selectAllStatement = “SELECT * FROM Contacts”;

var insertStatement = “INSERT INTO Contacts (username, useremail) VALUES (?, ?)”;

var updateStatement = “UPDATE Contacts SET username = ?, useremail = ? WHERE id=?”;

var deleteStatement = “DELETE FROM Contacts WHERE id=?”;

var dropStatement = “DROP TABLE Contacts”;

var db = openDatabase(“AddressBook”, “1.0”, “Address Book”, 200000); // Open SQLite Database

var dataset;

var DataType;

function initDatabase() // Function Call When Page is ready.

{

try {

if (!window.openDatabase) // Check browser is supported SQLite or not.

{

alert(‘Databases are not supported in this browser.’);

}

else {

createTable(); // If supported then call Function for create table in SQLite

}

}

catch (e) {

if (e == 2) {

// Version number mismatch.

console.log(“Invalid database version.”);

} else {

console.log(“Unknown error ” + e + “.”);

}

return;

}

}

function createTable() // Function for Create Table in SQLite.

{

db.transaction(function (tx) { tx.executeSql(createStatement, [], showRecords, onError); });

}

function insertRecord() // Get value from Input and insert record . Function Call when Save/Submit Button Click..

{

var usernametemp = $(‘input:text[id=username]’).val();

var useremailtemp = $(‘input:text[id=useremail]’).val();
db.transaction(function (tx) { tx.executeSql(insertStatement, [usernametemp, useremailtemp], loadAndReset, onError); });

//tx.executeSql(SQL Query Statement,[ Parameters ] , Sucess Result Handler Function, Error Result Handler Function );

}

function deleteRecord(id) // Get id of record . Function Call when Delete Button Click..

{

var iddelete = id.toString();

db.transaction(function (tx) { tx.executeSql(deleteStatement, [id], showRecords, onError); alert(“Delete Sucessfully”); });

resetForm();

}

function updateRecord() // Get id of record . Function Call when Delete Button Click..

{

var usernameupdate = $(‘input:text[id=username]’).val().toString();

var useremailupdate = $(‘input:text[id=useremail]’).val().toString();

var useridupdate = $(“#id”).val();

db.transaction(function (tx) { tx.executeSql(updateStatement, [usernameupdate, useremailupdate, Number(useridupdate)], loadAndReset, onError); });

}

function dropTable() // Function Call when Drop Button Click.. Talbe will be dropped from database.

{

db.transaction(function (tx) { tx.executeSql(dropStatement, [], showRecords, onError); });

resetForm();

initDatabase();

}

function loadRecord(i) // Function for display records which are retrived from database.

{

var item = dataset.item(i);

$(“#username”).val((item[‘username’]).toString());

$(“#useremail”).val((item[‘useremail’]).toString());

$(“#id”).val((item[‘id’]).toString());

}

function resetForm() // Function for reset form input values.

{

$(“#username”).val(“”);

$(“#useremail”).val(“”);

$(“#id”).val(“”);

}

function loadAndReset() //Function for Load and Reset…

{

resetForm();

showRecords()

}

function onError(tx, error) // Function for Hendeling Error…

{

alert(error.message);

}

function showRecords() // Function For Retrive data from Database Display records as list

{

$(“#results”).html(”)

db.transaction(function (tx) {

tx.executeSql(selectAllStatement, [], function (tx, result) {

dataset = result.rows;

for (var i = 0, item = null; i < dataset.length; i++) {

item = dataset.item(i);

var linkeditdelete = ‘<li>’ + item[‘username’] + ‘ , ‘ + item[‘useremail’] + ‘ ‘ + ‘<a href=”#” onclick=”loadRecord(‘ + i + ‘);”>edit</a>’ + ‘ ‘ +

‘<a href=”#” onclick=”deleteRecord(‘ + item[‘id’] + ‘);”>delete</a></li>’;

$(“#results”).append(linkeditdelete);

}

});

});

}

$(document).ready(function () // Call function when page is ready for load..

{
;

$(“body”).fadeIn(2000); // Fede In Effect when Page Load..

initDatabase();

$(“#submitButton”).click(insertRecord); // Register Event Listener when button click.

$(“#btnUpdate”).click(updateRecord);

$(“#btnReset”).click(resetForm);

$(“#btnDrop”).click(dropTable);

});

3. style

html, body, h1, form, fieldset, legend, ol, li

{

margin: 0;

padding: 0;

}

body

{

background: #ffffff;

color: #111111;

font-family: Georgia, “Times New Roman”, Times, serif;

padding : 20px;

}

form#mycontact

{

background: #9cbc2c;

-moz-border-radius: 5px;

-webkit-border-radius: 5px;

padding: 20px;

width: 400px;

height: 150px;

}

form#mycontact fieldset

{

border: none;

margin-bottom: 10px;

}

form#mycontact fieldset:last-of-type

{

margin-bottom: 0;

}

form#mycontact legend

{

color: #384313;

font-size: 16px;

font-weight: bold;

padding-bottom: 10px;

}

form#mycontact > fieldset > legend:before

{

content: “Step ” counter(fieldsets) “: “;

counter-increment: fieldsets;

}

form#mycontact fieldset fieldset legend

{

color: #111111;

font-size: 13px;

font-weight: normal;

padding-bottom: 0;

}

form#mycontact ol li

{

background: #b9cf6a;

background: rgba(255,255,255,.3);

border-color: #e3ebc3;

border-color: rgba(255,255,255,.6);

border-style: solid;

border-width: 2px;

-moz-border-radius: 5px;

-webkit-border-radius: 5px;

line-height: 30px;

list-style: none;

padding: 5px 10px;

margin-bottom: 2px;

}

form#mycontact ol ol li

{

background: none;

border: none;

float: left;

}

form#mycontact label

{

float: left;

font-size: 13px;

width: 110px;

}

form#mycontact fieldset fieldset label

{

background:none no-repeat left 50%;

line-height: 20px;

padding: 0 0 0 30px;

width: auto;

}

form#mycontact fieldset fieldset label:hover

{

cursor: pointer;

}

form#mycontact textarea

{

background: #ffffff;

border: none;

-moz-border-radius: 3px;

-webkit-border-radius: 3px;

-khtml-border-radius: 3px;

font: italic 13px Georgia, “Times New Roman”, Times, serif;

outline: none;

padding: 5px;

width: 200px;

}

form#mycontact input:not([type=submit]):focus,

form#mycontact textarea:focus

{

background: #eaeaea;

}

form#mycontact button

{

background: #384313;

border: none;

float:left;

-moz-border-radius: 20px;

-webkit-border-radius: 20px;

-khtml-border-radius: 20px;

border-radius: 20px;

color: #ffffff;

display: block;

font: 14px Georgia, “Times New Roman”, Times, serif;

letter-spacing: 1px;

margin: 7px 0 0 5px;

padding: 7px 20px;

text-shadow: 0 1px 1px #000000;

text-transform: uppercase;

}

form#mycontact button:hover

{

background: #1e2506;

cursor: pointer;

}

save it & watch. A simple crud application  you can convert this things directly into a mobile application with phonegap.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s