SQLite Select Statement

Question

can someone help me with what I think is probably a simple problem please. I think the problem is probably with my SQL select statement, but maybe not.

I have a table named tableOne with two columns, name and total, the function editRecord is called and the id of the row in question is passed to it so I can select this row, then editRecords2 function is called. This second function generates a form in the html and adds the name and total values from the particular row in the table as into the form boxes.

The problem is that the values from the desired row never appear in the form, always the values from the last row go into the form. The code is below, any help would be great, thanks!

 function editRecord(id) {
 db.transaction(function(tx) {
 tx.executeSql('SELECT * FROM tableOne WHERE id=?', [id], editRecords2);
 });
 }
function editRecords2() {
 f = $('#edit');
 f.html("");
 f.html(f.html() + ' <form method="get" id="edit_form"><div><input type="name" id="editname" value="' + r['name']+'" size="30"/><input type="number" current id="editamount" value="' + r['total']+'" name="amount" size="15" /><input type="submit" value="Save" /></div></form> ');
 } 

Answer 1

I haven't tested the code yet but this will help you to get your output

function editRecord(id) {
 db.transaction(function(tx) {
 tx.executeSql('SELECT * FROM tableOne WHERE id=?', [id], function(tx,results){
 for (var i=0; i < results.rows.length; i++){
 row = results.rows.item(i);
 editRecords2(row.name,row.total);
 }
 });
 });
 }
function editRecords2(name,total) {
 f = $('#edit');
 f.html("");
 f.html(f.html() + ' <form method="get" id="edit_form"><div><input type="name" id="editname" value="' + name+'" size="30"/><input type="number" current id="editamount" value="' + total+'" name="amount" size="15" /><input type="submit" value="Save" /></div></form> ');
 }

if any problem occure contact me.

• mao: awesome, i still don't know why it needs a loop but it works. i obviously need to read up or practice some sqlite. thankyou!
• mao: i don't mean to be a pain in the rear @Sujeet , but do you have any idea how i can expand upon this to update a record? i can pass the value from this form to another function to update the table but i think i'm doing it wrong again. function updateRecord(name, total) { db.transaction(function(tx) { tx.executeSql("UPDATE tableOne SET name = ?, total = ?", [name, total]); }); }

Answer 2

I think what you need to look at is SQLite's User Defined Functions. You want SQL to call your function for each record in the table. This must be done at the SQL level, and not the JavaScript level. In C++, it's a piece of cake, SQLite has a sqlite_create_function() that will allow you to do this. I'm not sure if there's one for JavaScript, but that's what you'll need to look for.

• mao: i have a delete function working correctly though using the exact same approach, the only difference being that i'm selecting instead of deleting? function deleteRecord(id) { db.transaction(function(tx) { tx.executeSql('DELETE FROM tableOne WHERE id=?', [id], renderRecords); }); }

Answer 3

SQLite looks a little tricky. But it seems that if try to access it without a server side language you won't have success

JavaScript sqlite