SitePoint Sponsor

User Tag List

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

    Writing a row to a DB using JS

    I'm basing my code on the PhoneGap Storage API, in which one uses JS to populate a page (innerHTML) from a DB. That works fine! However, I'm at a loss as to how to add a row to that DB from a different HTML page if I move all the DB to an external JS page.

    I've had experience with PHP and MySQL, having written an entire web site based on it, but I'm confounded with the use of JS to write to a DB. Can you help steer me in the right direction?

    The following code works fine, but it's all on the same page. If I moved the JS to an external page, how would I code the JS/DB query to add a new row to the DB, specifically from a button onClick event?

    HTML Code:
    <!DOCTYPE html>
    <html>
    <head>
    <title>Prepopulated DB (PG Storage Example)</title>
    
    <script type=”text/javascript” charset=”utf-8″ src=”cordova-2.0.0.js”></script>
    <script type=”text/javascript” charset=”utf-8″>
    
    // Wait for Cordova to load
    
    document.addEventListener(“deviceready”, onDeviceReady, false);
    
    // Populate the database
    
    function populateDB(tx) {
    tx.executeSql(‘DROP TABLE IF EXISTS BIRDS’);
    tx.executeSql(‘CREATE TABLE IF NOT EXISTS BIRDS (id unique, bodyType TEXT NOT NULL, category TEXT NOT NULL, name TEXT NULL, photo TEXT NULL, resource TEXT NULL, caption TEXT NULL)’);
    tx.executeSql(‘INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (1, “Short Course”, “SC8.2e”, “SC8.2e Ready-To-Run”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC8.2e/SC8.2e_RTR_2560x2048_sm.jpg”, “http://www.teamassociated.com/cars_and_trucks/SC8.2e/RTR/”,”Modeled after the short-course race trucks that compete in the Lucas Oil Off Road Racing Series, the SC8.2e RTR takes the next evolutionary step in Team Associated\’s 1:8 scale short-course line by adding the new performance suspension developed on our R.O.A.R. National Championship-winning RC8.2 buggy.”)’);
    tx.executeSql(‘INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (2, “Short Course”, “SC10GT”, “SC10GT Ready-To-Run”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC10GT/SC10GT_RTR_2560x2048_sm.jpg”, “http://www.teamassociated.com/cars_and_trucks/SC10GT/RTR/”,”For many people in the RC world, nothing beats a 2-stroke nitro-breathing engine. From the sound, to the smoke, to the brutal power, nitro delivers an experience in a RC truck like nothing else can. Now you can experience that awesome nitro power in the short-course class with the SC10GT!”)’);
    tx.executeSql(‘INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (3, “Short Course”, “SC10 4×4″, “SC10 4×4 RTR Combo”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC10_4x4/SC10_4x4_Kit_2560x2048_sm.jpg”, “http://www.teamassociated.com/cars_and_trucks/SC10_4x4/RTR_Combo/”,”The SC10 4×4 Ready-To-Runs are RC replicas of the 800+ horsepower short course trucks driven in the Lucas Oil Off Road Racing Series.”)’);
    tx.executeSql(‘INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (4, “Short Course”, “SC10″, “SC10 RS RTR Combo”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC10/SC10_RTR_Procomp_2560x2048_sm.jpg”, “http://www.teamassociated.com/cars_and_trucks/SC10/RS_Combo/”,”The SC10RS (Race-Spec) RTR Combo is a ready-to-run replica of the trucks driven in the Lucas Oil Off Road Racing Series.”)’);
    tx.executeSql(‘INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (5, “Buggy”, “RC8.2e”, “RC8.2e Factory Team”, “http://www.teamassociated.com/pictures/cars_and_trucks/RC8.2e/RC8.2e_2560x2048_sm.jpg”, “http://www.teamassociated.com/cars_and_trucks/RC8.2e/Factory_Team/”,”Team Associated has taken all of the refinements from the RC8.2 and have applied them to our electric-power platform. The RC8.2e has already been proven as a winner after TQ\’ing and winning the 2011 Sidewinder Nitro Explosion in the capable hands of Ryan Cavalieri.”)’);
    }
    // Query the database.
    
    function queryDB(tx) {
    tx.executeSql(“SELECT * FROM BIRDS”, [], querySuccess, errorCB);
    }
    
    // Query the success callback
    
    function querySuccess(tx, results) {
    var len = results.rows.length;
    console.log(“BIRDS table: ” + len + ” rows found.”);
    for (var i=0; i<len; i++){
    document.getElementById(“output”).innerHTML +=
    
    “<div class=’even’><p class=’title’>” + results.rows.item(i).name + “</p>” +
    “<img src=’” + results.rows.item(i).photo + “‘ width=’100px’ height=’100px’>” +
    “<p class=’caption’>” + results.rows.item(i).caption + “</p>” +
    “<p class=’more’><a href=’” + results.rows.item(i).resource + “‘>More</a></p>”;
    
    }
    
    }
    
    // Transaction error callback
    
    function errorCB(err) {
    console.log(“Error processing SQL: “+err.code);
    }
    
    // Transaction success callback
    
    function successCB() {
    var db = window.openDatabase(“Database”, “1.0″, “Birds”, 2000000);
    db.transaction(queryDB, errorCB);
    }
    
    // Cordova is ready
    
    function onDeviceReady() {
    var db = window.openDatabase(“Database”, “1.0″, “Birds”, 200000);
    db.transaction(populateDB, errorCB, successCB);
    }
    
    </script>
    <style type=”text/css”>
    body {margin:0; padding:0; background-color: #aaa;}
    div#wrapper{width:100%;padding:.2em;}
    div.even{max-width:100%;background-color:#fff;font-family:Helvetica, Arial, sans-serif; color: #333; text-align:center;margin:1em;padding:1em;
    -webkit-border-radius: 5px;
    -moz-border-radius: 5px;
    border-radius: 5px;
    -webkit-box-shadow: 5px 5px 4px 0px #888; /* Safari 3-4, iOS 4.0.2 – 4.2, Android 2.3+ */
    box-shadow: 5px 5px 4px 0px #888; /* Opera 10.5, IE9, Firefox 4+, Chrome 6+, iOS 5 */
    border:1px #666 solid;
    }
    p{text-align:left;}
    p.title {font-weight:bold;font-size:1em;text-align:center; color:#000}
    p.more {font-size:.8em;text-align:center;font-style:italic;}
    a {color:#666}
    img {width:150px;-webkit-border-radius: 5px;
    -moz-border-radius: 5px;
    border-radius: 5px;
    -webkit-box-shadow: 0px 0px 6px 6px #ddd; /* Safari 3-4, iOS 4.0.2 – 4.2, Android 2.3+ */
    box-shadow: 0px 0px 6px 6px #ddd; /* Opera 10.5, IE9, Firefox 4+, Chrome 6+, iOS 5 */
    }
    </style>
    
    </head>
    <body onload=”onDeviceReady()”>
    
    <div id=”wrapper”><span id=”output”></span></div>
    
    </body>
    </html>

  2. #2
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,376
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Not sure if this is the right approach:

    in js page:
    function populateDB() {
    tx.executeSql(‘INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES ("valueId", “valueBodyType”, “valueCategory”, “valueName”, “valuePhoto”, “valueResource”,”valueCaption”)’);
    }

    in HTML page:
    <button onClick="populateDB(valueId='100', valueBodyType='something', valueCategory='something', valueName='something', valuePhoto='something', valueResource='something',valueCaption='something')"></button>

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,376
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If I have this in the js file:

    HTML Code:
    function populateDB(tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS BIRDS (id unique, filename TEXT NOT NULL, title TEXT NULL)');
    tx.executeSql('INSERT INTO BIRDS (id, filename,title) VALUES (1, "storage3.html","Title 1")');
    tx.executeSql('INSERT INTO BIRDS (id, filename,title) VALUES (2, "storage3.html","Title 2")');
    tx.executeSql('INSERT INTO BIRDS (id, filename,title) VALUES (3, "storage3.html","Title 3")');
    tx.executeSql('INSERT INTO BIRDS (id, filename,title) VALUES (4, "storage3.html","Title 4")');
    tx.executeSql('INSERT INTO BIRDS (id, filename,title) VALUES (5, "storage3.html","Title 5")');
    }
    
    
    function SQL(query) {
    tx.executeSql('INSERT INTO BIRDS (id, filename, title) VALUES ($valueId, $valueFilename, $valueTitle)');
    }
    ...and this in the html file:

    HTML Code:
    <button value="bookmark" onclick="SQL($valueId='100', $valuefilename='storage3', $valueTitle='Nice Try')">Add Bookmark</button><a href="storage2.html"><button>Refresh</button></a>
    ... that doesn't work either; the row is not inserted. Error is:
    Uncaught ReferenceError: tx is not defined

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,376
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think I need to put the "function populateDB(tx)" inside the "function SQL(query)" to make it work, right? Not sure how to do that.


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
  •