Building Android Apps: Web SQL Database (part 3) - Selecting Rows and Handling Result Sets

3. Selecting Rows and Handling Result Sets

The next step is to expand the refreshEntries() function to do more than just set the title bar to the selected date. Specifically, we’ll query the database for entries on the selected date and append them to the #date ul element using the hidden entryTemplate HTML for structure. It’s been a while since we looked at that code, so here’s the Date panel again (it’s already in index.html, so you don’t need to add it again):

<div id="date">
    <div class="toolbar">
        <h1>Date</h1>
        <a class="button back" href="#">Back</a>
        <a class="button slideup" href="#createEntry">+</a>
    </div>
    <ul class="edgetoedge">
        <li id="entryTemplate" class="entry" style="display:none">
            <span class="label">Label</span>
            <span class="calories">000</span>
            <span class="delete">Delete</span>
        </li>
    </ul>
</div>

Here’s the complete refreshEntries() function; you must replace the existing refreshEntries() function in kilo.js with this:

function refreshEntries() {
    var currentDate = sessionStorage.currentDate;
    $('#date h1').text(currentDate);
    $('#date ul li:gt(0)').remove();
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'SELECT * FROM entries WHERE date = ? ORDER BY food;', 
                [currentDate], 
                function (transaction, result) {
                    for (var i=0; i < result.rows.length; i++) {
                        var row = result.rows.item(i);
                        var newEntryRow = $('#entryTemplate').clone();
                        newEntryRow.removeAttr('id');
                        newEntryRow.removeAttr('style');
                        newEntryRow.data('entryId', row.id);
                        newEntryRow.appendTo('#date ul');
                        newEntryRow.find('.label').text(row.food);
                        newEntryRow.find('.calories').text(row.calories);
                    }
                }, 
                errorHandler
            );
        }
    );
}

					  

With all this out of the way, our Date panel will display an li for each row in the database that corresponds to the selected date. Each row will have a label, calories, and a Delete button. Once we create a few rows, you can see that we need to add a bit of CSS to style things up nicely (Figure 5).

Figure 5. The entries are showing up now, but they still need to be fancied up with some CSS

Save the following CSS into a file named kilo.css (save this in the same directory as the HTML file):

#date ul li {
    position: relative;
}
#date ul li span {
    color: #FFFFFF;
    text-shadow: 0 1px 2px rgba(0,0,0,.7);
}
#date ul li .delete {
    position: absolute;
    top: 5px;
    right: 6px;
    font-size: 12px;
    line-height: 30px;
    padding: 0 3px;
    border-width: 0 5px;
    -webkit-border-image: url(themes/jqt/img/button.png) 0 5 0 5;
}

Now, link to kilo.css by adding the following line to the head section of index.html:

<link type="text/css" rel="stylesheet" media="screen" href="kilo.css">

Although the Delete buttons now look like buttons (see Figure 6), they won’t do anything when tapped at this point. This is because we set them up using the span tag, which is not an interactive element in an HTML page.

Figure 6. The entries with CSS applied