Database + Javascript

JavaScript
1

I can place database results on-screen using PHP and MySQL, but I’m at a loss figuring out how to do the same when using Javascript.

On this page, we have instructions on using a database: SQLResultSetList
http://docs.phonegap.com/en/2.0.0/cordova_storage_storage.md.html#Storage

However, in this example, the output goes to the console:

function populateDB(tx) {
        tx.executeSql('DROP TABLE IF EXISTS DEMO');
        tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
        tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
        tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
    }

function queryDB(tx) {
* * tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);
}

function querySuccess(tx, results) {
console.log("Returned rows = " + results.rows.length);
// this will be true since it was a select statement and so rowsAffected was 0
if (!resultSet.rowsAffected) {
* console.log('No rows affected!');
* return false;
}
// for an insert statement, this property will return the ID of the last inserted row
console.log("Last inserted row ID = " + results.insertId);
}

How do I use Javascript get the output to go to between <div><p></p></div> tags so I could format the text for each column in the rows? For instance, in the format of the following, with the id value in one div and the data value in the second div, continuing until all the data has been displayed:

<div class=a><p>Title: 1 </p></div>
<div class=a><p>Name: First row</p></div>

This does not take place on a server, but in a mobile device, so PHP is not used.

2

This example is just a demo to show you how you can do what you want however I don’t recommend writing HTML as a string but instead use the document.createElement() method but for the purpose of the demo this works fine.

function querySuccess(tx, results) {
    var rows     = results.rows,
        affected = rows.length;
    
    if (affected > 0) {
        for (var i = 0; i < affected; i++) {
            document.body.innerHTML += '<div class="a">' +
                '    <p>Title: ' + rows.item(i).id + '</p>' +
                '</div>' +
                '<div class="a">' +
                '    <p>Data: ' + rows.item(i).data + '</p>' +
                '</div>';
        }
    } else {
        // Do something else here!
        // console.log('No affected rows!');
    }
}
3

Hi StevenHu. I had a look at your profile and you know PHP and mySQL. I would do this using PHP and mySQL but outputting javascript rather than HTML. You’ll find it a lot easier.

4

I don’t know JS, so it is not easy for me. Thanks!

5

Thank you, Chris. Now what do I put in the body to display the results? I tried the following, and no results on screen:

<script>
[other stuff here]

 if (affected > 0) {
        for (var i = 0; i < affected; i++) {
            document.getElementById('output').innerHTML += '<div class="a">' +
                '    <p>Title: ' + rows.item(i).id + '</p>' +
                '</div>' +
                '<div class="a">' +
                '    <p>Data: ' + rows.item(i).data + '</p>' +
                '</div>';
        }
    }
[other stuff here]
</script>
  <body>

    <input type="button" onClick="querySuccess(tx, results)" value="Get Data">
     <p id="output"></p>

  </body>
6

I get this error in Chrome Tools:
Uncaught TypeError: Cannot read property ‘rows’ of undefined

for this line:
var rows = results.rows,

7

As mentioned in the opening post, PHP is not possible, as the code resides on a mobile phone, not a server.

8

The data resides on a server though, or are you using the phones data?

If it’s on a server these do more or less the same to “resultDiv” depending on a querystring. I use live=1

// get live variable from querystring

if((isset($_GET["live"])) && ( (int)$_GET["live"] === 1)){
$live=True;
}

Then I use a small function to strip out newlines and escape quotes when echoing strings. I call it write as it’s easy to remember and seems to be allowed in the server languages I know.

function write($s){
 global $live;
 if($live){
  echo str_replace("\\"","\\\\\\"",str_replace("\\r\
","",$s));
 }else{
  echo $s;
 }
}

I can then output it as HTML or JavaScript like this

if($live){

echo "document.getElementById(\\"resultsDiv\\").innerHTML=\\"".write($resultsHTML)."\\";";

} else {

echo "<div id=\\"resultsDiv\\">".$resultsHTML."</div>\\r\
";

}

Rather than build up a large concated string for resultsHTML you can output peices of javascript one at a time as the file doesn’t get executed in the browser until all of it is received from the server.

if($live){
echo "document.getElementById(\\"resultsDiv\\").innerHTML=\\""; // javascript string left open
} else {
echo "<div id=\\"resultsDiv\\">\\r\
";
}

then output your HTML as normal but strip all the newlines and escape the quotes using the write function instead of echo

write("<h2>".$resultsHeader."</h2>\\r\
");
write("<p>".$resultsDescription."</p>\\r\
");

I then close the string in javascript or the div in HTML


if($live){
echo "\\";"; // closes javascript string
} else {
echo "</div>\\r\
";
}

I prefer to echo my HTML. Personal preferences. Once it’s working you can change innerHTML to a more precise DOM method, but for me it depends what it’s doing. I’m starting to think of the javascript string I send back to the browser, and whether it will be smaller using innerHTML or strings, arrays and manipulating the DOM. That will probably be the deciding factor once I am a bit more profficient.

This script changes a script element http://www.sitepoint.com/forums/showthread.php?135179-Javascript-Refresh Although it’s a bit bulked out there :slight_smile:

You could use document.write as well to do it inline, but that would require a whole page change per click. Changing the script’s src would allow the results to be dynamic and need less bandwidth.

9

Sorry, should finish it off and link it up. If you can use JavaScript and the data is coming from a server you can use php to push the data to the client with javascript using the changeScript function.

Ah, sorry, just read the Cordova page, database is on the device! Duh! Sorry.

Could be a way of updating your local db from a remote one :shrugs:

10

Sorry StevenHu. I hope you don’t mind me asking as I followed the link and became quite intrigued. Is this to build an app? Can the app make a connection to a remote server with JavaScript? Can I send local javascript database commands from a remote server with PHP?

11

Markdidj, you can run SQLite on the browser using the depreciated Web Database (http://www.w3.org/TR/webdatabase/) and use Javascript to access it (Webkit browsers only). This is similar to what Phonegap uses.

Here is a wrapper for web database and for the OP’s example:

<!DOCTYPE html>
<html>
	<head>
	</head>
	<body>
		<div>
			<p id="title"></p>
			<p id="name"></p>
			<button id="populateDiv">Go</div>
		</div>	
		<script>
			var DB = function() {
				var _db = null;	
				return {
					init: function(dbname) {
						_db = openDatabase(dbname, '1.0', '', 5 * 1024 * 1024);
					},
					query: function(sql, arr) {
						_db.transaction(function(tx){
							tx.executeSql(
								sql,
								arr,
								function (tx, rs) {
									if (!rs.rowsAffected) {
										console.log('No rows affected on query: ' + sql);
										return false;
									}
								},
								function(tx, error) {
									console.log(error.message + ' on query: ' + sql);
								}
							);
						});	
					},
					resultSet: function(sql, arr, callback) {
						_db.readTransaction(function(tx){
							tx.executeSql(
								sql,
								arr,
								function(tx,rs) {
									var output = [];
									for(i = 0; i < rs.rows.length; i++) {
										output.push(rs.rows.item(i));
									}	
									if ( typeof(callback) == 'function' ) {
										callback(output);
									}
								},
								function(tx, error) {
									console.log(error.message);
								}
							)
						});
					}
				}
			};		
			
			var db = new DB,
				btn = document.getElementById('populateDiv');
			db.init('app');
			db.query('DROP TABLE IF EXISTS DEMO');
			db.query('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
			db.query('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
			db.query('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');				

			btn.onclick = function() {
				var divTitle = document.getElementById('title'),
					divName = document.getElementById('name');
				db.resultSet(
					'SELECT * FROM DEMO LIMIT 1', [],
					function(rs) {
						rs.forEach(function(el, i) {
							divTitle.innerHTML = rs[i].id;
							divName.innerHTML = rs[i].data;
						});				
					}
				);
				
			}			
		</script>
	</body>
</html>
12

Thanks :slight_smile: Is there something similar that’s not depreciated? I’ve never considered building an app because most of the things I like to work with have connections to a database. This has me intrigued though, as I could store a small db locally and import data from an external db allowing whatever I make work without an internet connection. The connection would only be required to update the local db.

I’m not sure if I like the lack of password though.

13

Duh! Just realised why it’s pointless having a pssword! It’s local…lol

14

Mark,

The app does not make any connections to an external server. It is building a database on startup that resides in the app itself. No PHP is needed or possible in this case.

Regarding PHP/MySQL and mobile phones: One would use Ajax and JSON to make them work together.

15

I don’t think “One would” is the right way to put it, you could say I would, but I would reply “I wouldn’t. Why use a JSON parser to convert variables and arrays to JavaScript when you can just output them in JavaScript, and output any commands/functions with it”

Understand the comments on the rest of it though. I just read the first few lines and made the presumptions for which I apologise :slight_smile:

16

That was the recommended way to integrate PHP. I haven’t tried this, so I don’t know the pros and cons of your way versus this way. Thanks!

17

I’ve changed a couple of lines. Do you see where I’m going with this?