Not really HTML5 related, but if you’re working on mobile web apps and streaming music, this is a big feature.
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:
<!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>
</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> There, just a simple form. 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 openDatabase call 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:
<!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> 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.
<!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]);
});
}
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 />');
}
}
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);
}
});
}
$(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());
}
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>
<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 supporting renderResults) function, call it from our form submission and upon page load, and then add some HTML elements to support the renderScores function. You can see the renderScores function again just uses normal SQL to select rows. The renderResults function updates our HTML elements with the proper rows.
Now our simple app can record scores, and display all those scores back to users without any intervention from outside servers (read: no network access necessary). This is pretty fantastic, but let’s make it easy for a user to keep scores for multiple players at once. To do this, we’ll add another form to filter our scores by email address:
<!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]);
});
}
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 />');
}
}
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);
}
});
}
$(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());
}
renderScores();
return false;
});
$('#filter_previous_scores_form').submit(function() {
e = $('#filter_by_email').val();
renderScores(e);
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>
<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>
<form id="filter_previous_scores_form">
<input type="email" placeholder="Filter scores by email" size="40" id="filter_by_email" /><br />
<input type="submit" value="Filter" />
</form>
</div>
<div id="previous_scores">
</div>
</body>
</html> We add the #filter_previous_scores_form form here (complete with placeholder text!) and the associated submit function in our document ready function. When you submit this form, users are able to SELECT just those scores related to the email address entered. Voila!
To me this is an almost fully functioning app (if a bit silly and poorly designed) for allowing one person to keep track of golf scores for multiple players. What’s best about it is that since there are no network calls, the app is (a) fast and (b) usable even without network access.
Still left to do to this app is add even more offline support, talk about background tasks, and learn about how to distribute and promote your mobile HTML5 app. We’ll tackle all these in future parts of this tutorial.
Shameless plug for my own site, but I think this is a great tool for mobile web developers. Contact me if you have any comments or would like to discuss further.
Seth Goldstein defined the term iDroid, and in doing so, articulates a big concern of mine as I observe mobile product development in USV’s portfolio companies. First the definition:
iDroid (n.) - developing for iPhone and Android platforms simultaneously, as in “let’s make sure we have iDroid versions up and running before we launch the new web service.”
I’m concerned that mobile development on multiple platforms is the new browser compatibility battle of this generation. I’m certainly not the first person to have this concern. But, instead of complaining, I want to know what developers are doing for a solution?
Is everyone just maintaining separate presentation layers for their iPhone and Android sites? Or, is there a common subset of HTML5 and Javascript calls that developers can use safely for cross-browser support? Or, best of all, is there an abstraction layer (like the ActiveRecord Pattern equivalent for Mobile UI) that developers can use to make sure that the visual layer looks good on all mobile broswers?
The most interesting thing to me about the iPad announcement a couple weeks ago has nothing to do with the device itself (at least, not directly). In my opinion, it’s that in the two or so weeks since the announcement, the amount of interest surrounding Mobile HTML5 apps has increased tremendously. Going into it, I would’ve expected the opposite to happen: people would’ve been jumping further into the native app bandwagon. But that just wasn’t the case.
Looking back on it, here’s why I think it happened. One reason is obvious, but I think it might be more subtle than just the obvious answer…
” Why did the iPad create such an interest in HTML5? - Ian SeffermanThe new Google Buzz app is all HTML5 when you’re viewing from your mobile device. Great example of the possibilities.
Linked is AppStoreHQ’s listing of the app in their app discovery service.
Over a series of posts, I’m going to guide us through our first mobile HTML5 app. It’s my first HTML5 app, too! The goal of the app will be to explain the big principles of HTML5 in as simple a manner as possible. The goal is not to make the most elegant code in the world, but just workable enough to make a point. Also, all of these examples will work, but depending on your browser and device, they might not appear. If in doubt, find an iPhone to test against — I’ll ensure all the features I talk about at least work there (or note otherwise).
The app we’re going to be building is a golf score keeper. Essentially, its goal is to replace the little scorecard and pencil at a golf course. Over the course of this multi-part tutorial, we’ll make it increasingly complex. (Note: I haven’t fully thought this example app through, so hope for the best.)
The Basics
Let’s start by setting up an HTML5 page. Doing so is actually very trivial, you only need one line at the top of your HTML file (in this case, let’s start with index.html):
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=924892bf-5c6b-4d89-a7bb-a0af3ac990e3)
<!DOCTYPE html>
<html>
</html>
That <!DOCTYPE html> line is all you need to say this is HTML5. Because we’ll be doing some JavaScript work, let’s also add jQuery to make our life a bit easier. We’ll be using the Google hosted version (so you don’t have to download anything). In addition, we’ll take care of some boilerplate code, like a title.
<!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>
</head>
<body>
</body>
</html>
Okay, that’s The Basics. Easy.
Forms
Forms are upgraded in HTML5 to make your life easier. They don’t necessarily do anything that I’d consider mind-blowing or even web-changing, but they make a lot of our current hacks easier. To start we’re only going to track the first two holes. Each of these holes are Par 4’s, which we expect each golfer to be able to make. We’ll also ask for an email address.
<!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>
</head>
<body>
<form action="upload.html" method="get">
<div>
<label for="1">Hole 1</label>
<input type="number" min="1" value="4" name="1" size="2" step="1" />
</div>
<div>
<label for="2">Hole 2</label>
<input type="number" min="1" value="4" name="2" size="2" step="1" />
</div>
<div>
<input type="email" placeholder="Enter your email address" size="40"/>
</div>
<div>
<input type="submit" value="Upload Score" />
</div>
</form>
</body>
</html> At first glance, that form looks normal. Upon further inspection, you’ll notice a few differences:
There’s our form! Nothing fancy, but it gets the job done, and in an even cleaner fashion than what was previously available to developers.
Geolocation
Easy geolocation — getting lat/long coordinates for where your user is located — is, in my opinion, one of the biggest wins for HTML5. Going back to our golf score keeper app, now that we’re able to get the email address of a user as well as the score for each hole, let’s pretend we don’t know which course they are on. We don’t want them to have to enter it manually; instead we’ll just figure it out from their location. Initially this will look hacky (just for demonstration purposes), but we’ll clean it up later.
<!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>
</head>
<body>
<form action="upload.html" method="get">
<div>
<label for="1">Hole 1</label>
<input type="number" min="1" value="4" name="1" size="2" step="1" />
</div>
<div>
<label for="2">Hole 2</label>
<input type="number" min="1" value="4" name="2" size="2" step="1" />
</div>
<div>
<input type="email" placeholder="Enter your email address" size="40"/>
</div>
<div>
<input type="text" id="lat_field" name="latitude" />
<input type="text" id="long_field" name="longitude" />
</div>
<script>
navigator.geolocation.getCurrentPosition(
function(pos) {
$("#lat_field").val(pos.coords.latitude);
$("#long_field").val(pos.coords.longitude);
}
);
</script>
<div>
<input type="submit" value="Upload Score" />
</div>
</form>
</body>
</html> All we added is a small inline JavaScript block and two text form elements for latitude and longitude. The JavaScript block calls the new navigator.geolocation.getCurrentPosition function, which gets the location of the device and passes it to a function. In our case, the function updates the form elements for each coordinate. Go ahead and try it out — you should see a dialog asking if you’d like to share your location followed by coordinates filling in your form elements!
That’s it! We’ve now successfully built an HTML5 page that captures numbers, email addresses, uses placeholder text, and even gets the GPS location of the user. In part 2 we’ll start to clean up this page and use this form data.
In the meantime, if you have any questions or comments, please let me know by leaving a comment or emailing iseff@iseff.com.
