SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Database + Javascript

    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/co...d.html#Storage

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

    Code:
    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. #2
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,197
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

    Code JavaScript:
    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. #3
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Markdidj View Post
    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.
    I don't know JS, so it is not easy for me. Thanks!

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you, Chris. Now what do I put in the body to display the results? I tried the following, and no results on screen:

    Code:
    <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. #6
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I get this error in Chrome Tools:
    Uncaught TypeError: Cannot read property 'rows' of undefined

    for this line:
    var rows = results.rows,

  7. #7
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Markdidj View Post
    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.

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

  8. #8
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    As mentioned in the opening post, PHP is not possible, as the code resides on a mobile phone, not a server.
    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

    PHP Code:
    // 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.
    PHP Code:
    function write($s){
     global 
    $live;
     if(
    $live){
      echo 
    str_replace("\"","\\\"",str_replace("\r\n","",$s));
     }else{
      echo 
    $s;
     }

    I can then output it as HTML or JavaScript like this
    PHP Code:
    if($live){

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

    } else {

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


    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.
    PHP Code:
    if($live){
    echo 
    "document.getElementById(\"resultsDiv\").innerHTML=\""// javascript string left open
    } else {
    echo 
    "<div id=\"resultsDiv\">\r\n";

    then output your HTML as normal but strip all the newlines and escape the quotes using the write function instead of echo
    PHP Code:
    write("<h2>".$resultsHeader."</h2>\r\n");
    write("<p>".$resultsDescription."</p>\r\n"); 
    I then close the string in javascript or the div in HTML
    PHP Code:
    if($live){
    echo 
    "\";"// closes javascript string
    } else {
    echo 
    "</div>\r\n";

    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/show...script-Refresh Although it's a bit bulked out there

    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.
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  9. #9
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  10. #10
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  11. #11
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    <!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. #12
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks 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.
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  13. #13
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Duh! Just realised why it's pointless having a pssword! It's local.....lol
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  14. #14
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #15
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    Regarding PHP/MySQL and mobile phones: One would use Ajax and JSON to make them work together.
    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
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if

  16. #16
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Markdidj View Post
    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
    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. #17
    ♪♪ ♪ ♪ ♪ ♪♪ ♪ ♪♪ Markdidj's Avatar
    Join Date
    Sep 2002
    Location
    Bournemouth, South UK
    Posts
    1,551
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by centered effect View Post
    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:
    Code:
    <!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)');
                changeScript("http://www.example.com/getdata.php?uid="+form.uid.value);  // outputs the missing bit using live javascript from a server. Or multidimensional array to a function. Or class for those that prefer.              
    
                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>
    I've changed a couple of lines. Do you see where I'm going with this?
    LiveScript: Putting the "Live" Back into JavaScript
    if live output_as_javascript else output_as_html end if


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
  •