SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!


    Code:
    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");
    	});
    });

  2. #2
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,716
    Mentioned
    102 Post(s)
    Tagged
    4 Thread(s)
    Is there a way for us to run a test version of your page in our own web browser?
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Attached is a zip file that should work. This will only work in an HTML5 browser. I've been using Chrome. THANKS FOR LOOKING!!
    Attached Files Attached Files

  4. #4
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,716
    Mentioned
    102 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by erich93063 View Post
    Attached is a zip file that should work.
    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?
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  5. #5
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,716
    Mentioned
    102 Post(s)
    Tagged
    4 Thread(s)
    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:

    Code SQL:
    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:

    Code javascript:
    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/Jav...ical_Operators

    Our second helper function gets the restaurants:

    Code javascript:
    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:

    Code javascript:
     
    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:

    Code javascript:
     
    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");
        });
    });
    Last edited by paul_wilkins; Mar 26, 2011 at 05:50.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  7. #7
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW THANK YOU SO MUCH. That makes perfect sense. Thanks for taking the time on this one. I appreciate it.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •