Sqlite jquery nested loop

Question

I'm a bit of an newbie in this - I've been trying for a couple of days to modify various stackoverflow answers without any luck at all.

For my phonegap app - using sqlite, jquery I'm trying to loop through a category table, then have a nested list of 'kinds' for each category. The code below produces the outer loop, but not the inner.

Any help would be very much appreciated

db.transaction(function (tx) {
 tx.executeSql('SELECT * FROM cat;', [], function (transaction, catResult) {
 var theHtml ='';
 for (var i = 0; i < catResult.rows.length; i++) {
 // outer loop
 var catRow =catResult.rows.item(i);
 theHtml +='<li>' +catRow.Name;
 function doinner(i) {
 var theHtml2 ='';
 tx.executeSql('SELECT * FROM kind WHERE cat_id = ?;', [catRow.Id], function (transaction, kindResult) {
 theHtml2 ='<ul>';
 for (var i2 = 0; i2 < kindResult.rows.length; i2++) {
 // inner loop
 var kindRow =kindResult.rows.item(i2);
 theHtml2 +='<li>' +kindRow.kind +'</li>';
 };
 // end inner loop
 theHtml2 +='</ul>';
 });
 // end function
 theHtml +=theHtml2;
 }
 // end doinner
 doinner(i) ;
 // this function is supposed to assemble and append the inner loop
 theHtml +='</li>';
 }
 // end outer loop
 $('#catList').html(theHtml);
 });
 // end function
});
 // end transaction

Answer 1

The callback function you give to executeSql is executed asynchronously, so theHtml2 does not yet contain anything when you are using it.

An easier way to handle this is to get the data with a single query with a join and then use a single loop to construct the HTML lists:

tx.executeSql('SELECT cat.name, kind.kind ' +
 'FROM cat ' +
 'LEFT JOIN kind ON cat.id = kind.cat_id '+
 'ORDER BY 1, 2',
 [], function(tx, result) {
 var theHtml = '';
 var lastName = '';
 for (var i = 0; i < result.rows.length; i++) {
 var row = result.rows.items(i);
 if (row.name != lastName) {
 if (theHtml != '')
 theHtml += '</ul>';
 theHtml += '<li>' + row.name;
 lastName = row.name;
 theHtml += '<ul>';
 }
 if (!!row.kind)
 theHtml += '<li>' + row.kind + '</li>';
 }
 if (theHtml != '')
 theHtml += '</ul>';
 $('#catList').html(theHtml);
});

• Phil Barnett: oh thanks, I'm a bit new to this could you give me a clue how I would create the html as a nested list