Here we continue following through a tutorial on HTML5. Here's the url for part 2:
Tutorial: Your First Mobile HTML5 App - Offline Storage / The Local
SQL Database (Part 2)
Continuing along with our tutorial series about creating your first mobile HTML5 app, today we’ll talk about the local SQL database available. Part 1 discussed general HTML5 setup, new form elements, and geolocation. We started building a golf score keeper app. Today we’ll continue building that, ripping out some features (to keep our eyes focused on today’s features) and adding some others.
Let’s review with what we ended up with in Part 1, but go ahead and remove the geolocation features.
Of course, when you have a form, you want to capture the form submission somehow. In our case, we’ll use the new HTML5 local SQL database. We don’t need any servers or HTTP requests or anything except a compatible browser (iPhone’s Safari does the trick). Here’s how we initialize the database:
<!DOCTYPE html>
<html>
<head>
<title>Golf score keeper</title>
<script src="http://www.google.com/jsapi"></script>
<script>
google.load("jquery", "1.4.1");
</script>
<script>
var db = window.openDatabase("scores", "", "Previous Scores", 1024*1000);
$(document).ready(function() {
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Courses(id INTEGER PRIMARY KEY, name TEXT, latitude FLOAT, longitude FLOAT)', []);
tx.executeSql('CREATE TABLE IF NOT EXISTS Strokes(id INTEGER PRIMARY KEY, course_id INTEGER, hole_num INTEGER, num_strokes INTEGER, email TEXT)', []);
});
});
</script>
</head>
<body>
<form method="get" id="score_form">
<div>
<label for="1">Hole 1</label>
<input type="number" min="1" value="4" id="hole1" name="hole1" size="2" step="1" />
</div>
<div>
<label for="2">Hole 2</label>
<input type="number" min="1" value="4" id="hole1" name="hole2" size="2" step="1" />
</div>
<div>
<input type="email" id="email" placeholder="Enter your email address" size="40"/>
</div>
<div>
<input type="submit" value="Upload Score" />
</div>
</form>
</body>
</html>
You can see all we did here was add an
openDatabasecall and some SQL statements to create tables. These are just standard SQL statements (the reference SQL is from SQLite). Here are the official docs for the API calls.Now we’ll write a couple functions to help us insert scores into this table:
It's good that it's Sqllite. That's what we've worked with before on mobile. Html5 apparently supports that.
Here's the html with the inserts:
<!DOCTYPE html>
<html>
<head>
<title>Golf score keeper</title>
<script src="http://www.google.com/jsapi"></script>
<script>
google.load("jquery", "1.4.1");
</script>
<script>
var db = window.openDatabase("scores", "", "Previous Scores", 1024*1000);
function insertScore(hole_num, num_strokes, course_id, email) {
db.transaction(function(tx) {
tx.executeSql('INSERT INTO Strokes (course_id, hole_num, num_strokes, email) VALUES (?, ?, ?, ?)', [course_id, hole_num, num_strokes, email]);
});
}
$(document).ready(function() {
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Courses(id INTEGER PRIMARY KEY, name TEXT, latitude FLOAT, longitude FLOAT)', []);
tx.executeSql('CREATE TABLE IF NOT EXISTS Strokes(id INTEGER PRIMARY KEY, course_id INTEGER, hole_num INTEGER, num_strokes INTEGER, email TEXT)', []);
});
$('#score_form').submit(function() {
strokes = { 1: $('#hole1').val(), 2: $('#hole2').val() };
for (var hole_num in strokes) {
insertScore(hole_num, strokes[hole_num], 1, $('#email').val());
}
return false;
});
});
</script>
</head>
<body>
<form method="get" id="score_form">
<div>
<label for="1">Hole 1</label>
<input type="number" min="1" value="4" id="hole1" name="hole1" size="2" step="1" />
</div>
<div>
<label for="2">Hole 2</label>
<input type="number" min="1" value="4" id="hole1" name="hole2" size="2" step="1" />
</div>
<div>
<input type="email" id="email" placeholder="Enter your email address" size="40"/>
</div>
<div>
<input type="submit" value="Upload Score" />
</div>
</form>
</body>
</html>
Ok - so far so good. I'm re-familiarizing with Java script, for one thing.
Here we added the insertScore function, which performs our SQL INSERT statements, and we add a submit function for the #score_form. When you click the “Upload Score” button, the JavaScript will insert these scores into our new database.
Getting scores inserted into our database was trivial, but now we want to show previously submitted scores, right? This is easy, too. We’ll start with two changes: (1) show ALL previously submitted scores upon loading the page, and (2) update this list whenever new scores are submitted.
Let's check it out. I've added some comments to aid my own understanding:
<!DOCTYPE html>
<html>
<head>
<title>Golf score keeper</title>
<script src="http://www.google.com/jsapi"></script>
<script>
google.load("jquery", "1.4.1");
</script>
<script>
var db = window.openDatabase("scores", "", "Previous Scores", 1024*1000);
// This is pretty straightforward - the parmameters fill in the question marks, and the array is the data name
function insertScore(hole_num, num_strokes, course_id, email) {
db.transaction(function(tx) {
tx.executeSql('INSERT INTO Strokes (course_id, hole_num, num_strokes, email) VALUES (?, ?, ?, ?)', [course_id, hole_num, num_strokes, email]);
});
}
// This is a helper to renderScores. The tx and rs are the transaction objects and rowset returned by
// the sql select statement.
// The "#previous scores" refers to the name of the empty div where the scores are to be displayed.
// e.html updates the html is that section.
function renderResults(tx, rs) {
e = $('#previous_scores');
e.html("");
for(var i=0; i < rs.rows.length; i++) {
r = rs.rows.item(i);
e.html(e.html() + 'id: ' + r['id'] + ', hole_num: ' + r['hole_num'] + ', num_strokes: ' + r['num_strokes'] + ', email: ' + r['email'] + '<br />');
}
}
// Selects data from the strokes table and invokes the above renderResults
// The email will be used as the parameter if it's defined
function renderScores(email) {
db.transaction(function(tx) {
if (!(email === undefined)) {
tx.executeSql('SELECT * FROM Strokes WHERE email = ?', [email], renderResults);
} else {
tx.executeSql('SELECT * FROM Strokes', [], renderResults);
}
});
}
// create the tables; not sure of the purpose of the empty array
$(document).ready(function() {
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Courses(id INTEGER PRIMARY KEY, name TEXT, latitude FLOAT, longitude FLOAT)', []);
tx.executeSql('CREATE TABLE IF NOT EXISTS Strokes(id INTEGER PRIMARY KEY, course_id INTEGER, hole_num INTEGER, num_strokes INTEGER, email TEXT)', []);
});
// the submit fuction for the score form
// It seems as if you can refer to anything with an "id" on the page
// with the hash, enclosed by $('$xxx'), adding ".val" to get the value
// Iterates through the strokes array and inserts the data
// Note to get the value, you need to use the syntex #()
$('#score_form').submit(function() {
strokes = { 1: $('#hole1').val(), 2: $('#hole2').val() };
for (var hole_num in strokes) {
insertScore(hole_num, strokes[hole_num], 1, $('#email').val());
}
renderScores();
return false;
});
renderScores();
});
</script>
</head>
<body>
<form method="get" id="score_form">
<div>
<label for="1">Hole 1</label>
<input type="number" min="1" value="4" id="hole1" name="hole1" size="2" step="1" />
</div>
<div>
<label for="2">Hole 2</label>
// errata - id here should be "hole2"
<input type="number" min="1" value="4" id="hole1" name="hole2" size="2" step="1" />
</div>
<div>
<input type="email" id="email" placeholder="Enter your email address" size="40"/>
</div>
<div>
<input type="submit" value="Upload Score" />
</div>
</form>
<div>
<h2>Previous Scores</h2>
</div>
<div id="previous_scores">
</div>
</body>
</html>
All we did here was add the
renderScores(and supportingrenderResults) function, call it from our form submission and upon page load, and then add some HTML elements to support therenderScoresfunction. You can see therenderScoresfunction again just uses normal SQL to select rows. TherenderResultsfunction updates our HTML elements with the proper rows.
Let's try this one out.
Ok, the first thing I noticed is it loads very slowly. But it's no problem once the page is loaded. There is an issue, the number of strokes from hole 2 is showing up as null, an errata which I identified in the comments - a misnamed id.
We'll pick up the rest of this in the next posting.
No comments:
Post a Comment