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 class="toolbar"> <hl>Date</hl>

<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>

O Recall that we had set the style attribute of the li to display: none, which makes it not show up on the page. We did this so we could use that HTML snippet as a template for the database rows.

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 hl').text(currentDate); $('#date ul li:gt(0)').remove(); db.transaction(0

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');'entryId',; newEntryRow.appendTo('#date ul'); newEntryRow.find('.label').text(; newEntryRow.find('.calories').text(row.calories);

}, errorHandler

O These two lines set the toolbar title of the Date panel to the contents of the current Date value saved in sessionStorage.

© This line uses jQuery's gt() function (gt stands for "greater than") to select and remove any li elements with an index greater than 0. The first time through, this will do nothing because the only li will be the one with the ID of entryTemplate, which has an index of 0 and is hidden anyhow. However, on subsequent visits to the page, we need to remove any other lis before appending rows from the database again. Otherwise, items would end up appearing multiple times in the list because we'd be adding the same items over and over again.

© These three lines set up a database transaction and the executeSql statement.

O This line contains the first parameter for the executeSql statement. It's a simple SELECT statement with a question mark acting as a data placeholder.

© This is a single-element array that contains the currently selected date. This will replace the question mark in the SQL query.

© This anonymous function will be called in the event of a successful query. It accepts two parameters: transaction and result.

The transaction object can be used within the success handler to send new queries to the database, as we saw with the error handler previously. However, there is no need to do that in this case, so we won't be using it.

The result object is what we are most interested in here. It has three read-only properties: rowsAffected, which you can use to determine the number of rows affected by an insert, update, or delete query; insertId, which returns the primary key of the last row created in an insert operation; and rows, which has the records that were found.

The rows object will contain 0 or more row objects and has a length property that appears in the for loop on the next line.

O This line uses the item() method of the rows object to set the row variable to the contents of the current row.

© On this line, we clone() the template li and remove its id and style attributes on the next two lines. Removing the style will make the row visible, and removing the id is important because otherwise we would end up with multiple items on the page with the same id.

© This line stores the value of the row's id property as data on the li itself (we'll need that later in case the user decides to delete the entry).

© This code appends the li element to the parent ul. The next two lines update the label and calories span child elements of the li with the corresponding data from the row object.

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-5).

SffijQ 12:06/

H j Q

Back 8/3/2010

Bagel 400 Delete Beer 150 Delete Soup 350 Delete

Figure 5-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):

position: relative;

#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 5-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.

□ 069« ÜfiDti 12:07 am



Back 8/3/2010


Bagel 400


Beer 150


Soup 350


Figure 5-6. The entries with CSS applied

0 0

Post a comment