JS function to count rows in SQLite table

Question

I'm trying to make a JS function that counts rows in an SQLite table.

function countRows(){
    db.transaction(function (tx){
        tx.executeSql('SELECT id FROM table', [], function (tx, results) {
            var len = results.rows.length;
            alert(len);
        });
    });
}

The above code displays an alert with numbers of rows in the table. However, I'd like to make a function that would return the number instead of showing the alert box.

I tried:

function countRows(){
    db.transaction(function (tx){
        tx.executeSql('SELECT id FROM table', [], function (tx, results) {
            var len = results.rows.length;
            return len;
        });
    });
}

And then:

var number = countRows();
alert (number); // returns "undefined"

The above example returns "undefined", whereas a parallel example works fine:

function count(){
    return 3;
}
var number = count();
alert (number); // returns 3

I want to assign the number to a variable, so I could then make another sql query, count rows in another table, and compare the two results.

In PHP this would be:

$sql1 = mysql_query('SELECT COUNT(*) FROM table1');
$rows1 = mysql_result($sql1, 0);
$sql2 = mysql_query('SELECT COUNT(*) FROM table2');
$rows2 = mysql_result($sql2, 0);
if ($row1>$row2){}

Daniel A. White · Accepted Answer · 2012-03-22 14:14:58Z

4

You would be served much better by changing your query.

SELECT COUNT(ID) FROM table

Plus, its an async call so that the call return is returning the callback function. You should pass your own callback function.

function countRows(callback){
    db.transaction(function (tx){
        tx.executeSql('SELECT id FROM table', [], function (tx, results) {
            var len = results.rows.length;
            callback(len);
        });
    });
}

answered Mar 22, 2012 at 14:14

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan · Accepted Answer · 2012-03-22 14:23:29Z

2

db.transaction is asynchronous. returning value isn't assigned to any variable in code above. Solution is to pass callback or create custom event, which is almost the same.

Something like this:

function countRows(cb){
    db.transaction(function (tx){
        tx.executeSql('SELECT id FROM table', [], function (tx, results) {
            var len = results.rows.length;
            cb.call(this, len);
        });
    });
}

countRows(function (num) {alert(num)});

answered Mar 22, 2012 at 14:23

Ivan

2,2621 gold badge18 silver badges16 bronze badges

3 Comments

lukeshek Over a year ago

Yours and Daniel's suggestions are very helpful, but they don't solve my problem. I see that using callback I can execute the function anywhere in the code, but that's not what I'm trying to do. I'm trying to assign the value to a variable so I could count rows in two tables, then put the results in two different variables, and then compare the results using an if statement: if([NoOfRowsIn1stTable]>[NoOfRowsIn2ndTable]){}.

Panagiotis Over a year ago

Why don't you use a global function called tblname and before you call countRows() do a tblName = "table1"; countRows(function (num) {alert(num)}); tblName="table2"; countRows(function (num) {alert(num)}); Inside the tx.executeSql would be : tx.executeSql('SELECT id FROM '+tblName, [], function (tx, results)

Ivan Over a year ago

It's little ugly solution but it's easiest to implement. Idea is to pass "query function" for callback for each query expect last one. For last one pass a function that will compare results for previous queries. In this way you know that when executing one query, previous one is finished: countRowsTbl1(countRowsTbl2(callbackAfterBothQueriesAreExecuted));

Collectives™ on Stack Overflow

JS function to count rows in SQLite table

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related