Possible issue with closure

I am using SQLite as part of my Android development. I am using jQuery Mobile and PhoneGap so this is all JavaScript based. What I am trying to do is create a nested list with my data. The database at this point is very simple. There is an “Area” table and a “Restaurant” table. The Area table only consists of an AreaID and and AreaName and the Restaurants table consists of a RestaurantID, ResaurantName and AreaID. So I want to create a nested list of areas and in each area display the restaurants. Like so:

<ul>
<li>Area 1
<ul>
<li>Restaurant 1</li>
<li>Restaurant 2</li>
</ul>
</li>
<li>Area 2
<ul>
<li>Restaurant 3</li>
<li>Restaurant 4</li>
</ul>
</li>
</ul>

My code is below. I am constructing the HTML that then gets passed to a ul on the page via jQuery. So the outer list is being created, but the inner list is not being populated correctly as I’m sure I’m doing it wrong because the inner list cannot talk to the outer list because it is in another function. This is where my JavaScript skills suck. Any help would be greatly appreciated. I’m also open to a completely better way to do this whole thing if you can think of one.

THANKS!

var db;
var shortName = 'restaurants';
var version = '1.0';
var displayName = 'restaurants';
var maxSize = 65535;

db = openDatabase(shortName, version, displayName,maxSize);

db.transaction(function(tx) {
	tx.executeSql('SELECT * FROM Areas;', [],
			function(transaction, areaResult) {
				
				var theHtml = '';
				
				if (areaResult != null && areaResult.rows != null) {
					
					for (var i = 0; i < areaResult.rows.length; i++) {
						var areaRow = areaResult.rows.item(i);
						
						theHtml += '<li>' + areaRow.AreaName;
						
						tx.executeSql('SELECT * FROM Restaurants WHERE AreaID = ?;', [areaRow.AreaID],
								function(transaction, restaurantsResult) {
											
									theHtml = '<ul>';
									
									for (var i2 = 0; i2 < restaurantsResult.rows.length; i2++) {
										
										var restaurantsRow = restaurantsResult.rows.item(i2);
										theHtml += '<li>' + restaurantsRow.RestaurantName + '</li>';
										
									};
							
									theHtml += '</ul>';
										
								}
						
						);
						
						theHtml += '</li>';							
						
					}
						
				}
				
				$('#areas').html(theHtml);
				$('#areas').listview("refresh");
	});
});
	

Is there a way for us to run a test version of your page in our own web browser?

Attached is a zip file that should work. This will only work in an HTML5 browser. I’ve been using Chrome. THANKS FOR LOOKING!!

No, it doesn’t work, due to having no information from your database connection. We cannot help much with your problem when we are unable to experience the problem.

Can you simplify your code to the point where we can experience the problem without such difficulties getting in the way?

Did you run it in the latest version of Chrome? I just downloaded the same zip file and opened the index.html file in Chrome and it worked fine. Are you getting an error?

Okay, I’ve taken another look at this and realized that I need to learn something about using openDatabase

Instead of nesting the database calls, get all of your data in the one result set.
That can be easily achieved with the following SQL statement:


SELECT Restaurants.RestaurantName, Areas.AreaName
FROM Areas
    INNER JOIN Restaurants
        ON Areas.AreaID = Restaurants.AreaID
ORDER BY Restaurants.AreaID ASC, Restaurants.RestaurantName ASC

Ordering the results makes not only the processing easier, but also the presentation because the restaurants will then be displayed in alphabetical order.

Your result set will now contain four rows. The first two rows are the restaurants for areas 1, and the other two rows for area 2.

When converting the results in to a list, one way is to loop through all of the results, procedurally creating the list while remembering if the area name is the same so that you don’t create multiple lists for the area. That’s messy though.

Another way is to retrieve the areas from the results, and then to use that array of areas to retrieve just the restaurants that apply to them.

Our first helper function gets those areas:


function getAreas(results) {
    var areas = [],
        resultsLength = results.rows.length || 0,
        i,
        areaName,
        oldAreaName;
    for (i = 0; i < resultsLength; i += 1) {
        areaName = results.rows.item(i).AreaName;
        if (oldAreaName !== areaName) {
            areas.push(areaName);
            oldAreaName = areaName;
        }
    }
    return areas;
}

You may have noticed the following line:
resultsLength = results.rows.length || 0,

The OR operator acts in JavaScript as a default operator. If the results.rows.length provides a falsy value (null, undefined, 0, ‘’) then 0 will be assigned instead.
See: https://developer.mozilla.org/en/JavaScript/Reference/Operators/Logical_Operators

Our second helper function gets the restaurants:


function getRestaurantsInArea(results, areaName) {
    var restaurants = [],
        resultsLength = results.rows.length || 0,
        i,
        row;
    for (i = 0; i < resultsLength; i += 1) {
        row = results.rows.item(i);
        if (row.AreaName === areaName) {
            restaurants.push(row.RestaurantName);
        }
    }
    return restaurants;
}

Now the processing of the results becomes nice and easy:



var theHtml = '',
    areas = getAreas(results),
    restaurants = [],
    i;
for (i = 0; i < areas.length; i += 1) {
    restaurants = getRestaurantsInArea(results, areas[i]);
    theHtml += '<li>' + areas[i] +
        '<ul><li>' + restaurants.join('</li><li>') + '</li></ul>' +
        '</li>';
}
$('#areas').html(theHtml);
$('#areas').listview("refresh");

The complete working transaction in total is:



var sql = 'SELECT Restaurants.RestaurantName, Areas.AreaName FROM Areas INNER JOIN Restaurants ON Areas.AreaID = Restaurants.AreaID ORDER BY Restaurants.AreaID ASC, Restaurants.RestaurantName ASC';
db.transaction(function(tx) {
    tx.executeSql(sql, [], function(transaction, results) {
        function getAreas(results) {
            var areas = [],
                resultsLength = results.rows.length || 0,
                i,
                areaName,
                oldAreaName;
            for (i = 0; i < resultsLength; i += 1) {
                areaName = results.rows.item(i).AreaName;
                if (oldAreaName !== areaName) {
                    areas.push(areaName);
                    oldAreaName = areaName;
                }
            }
            return areas;
        }
        function getRestaurantsInArea(results, areaName) {
            var restaurants = [],
                resultsLength = results.rows.length || 0,
                i,
                row;
            for (i = 0; i < resultsLength; i += 1) {
                row = results.rows.item(i);
                if (row.AreaName === areaName) {
                    restaurants.push(row.RestaurantName);
                }
            }
            return restaurants;
        }

        var theHtml = '',
            areas = getAreas(results),
            restaurants = [],
            i;
        for (i = 0; i < areas.length; i += 1) {
            restaurants = getRestaurantsInArea(results, areas[i]);
            theHtml += '<li>' + areas[i] +
                '<ul><li>' + restaurants.join('</li><li>') + '</li></ul>' +
                '</li>';
        }
        $('#areas').html(theHtml);
        $('#areas').listview("refresh");
    });
});

WOW THANK YOU SO MUCH. That makes perfect sense. Thanks for taking the time on this one. I appreciate it.