SitePoint Sponsor

User Tag List

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

    DROP TABLE IF EXISTS doesn't clear table

    I am using the Storage API outlined in: http://docs.phonegap.com/en/3.0.0/co...torage.md.html Coding is all in JavaScript on an HTML5 page, not PHP.

    In addition, I created a couple of buttons with column values so the DB query will pull only rows with those column values. However, the results are being appended to the old results. Is there a way to remove the old results and show just the new results?

    The following line should do the trick, shouldn't it? It doesn't:

    HTML Code:
    tx.executeSql('DROP TABLE IF EXISTS CARS');
    I thought I would use a refresh script, but it either refreshed to infinity, or halted the script.

    The entire code is below. You can save it to desktop, double-click, click on the buttons, and see the appended results yourself. (You don't need the cordova.js file for this to work.)

    HTML Code:
    <!DOCTYPE html>
    <html>
    <head>
    <title>Prepopulated DB (PG Storage Example)</title>
    <meta name="viewport" content="width=device-width, initial-scale = 1.0, user-scalable = no">
    <script type="text/javascript" charset="utf-8" src="cordova.js"></script>
    <script type="text/javascript" charset="utf-8">
    
    /* from:
    http://docs.phonegap.com/en/3.0.0/cordova_storage_storage.md.html */
    
    // Wait for Cordova to load
    document.addEventListener("deviceready", onDeviceReady, false);
    // Cordova is ready
    	var type = "Buggy"; // on page load, will show only this bodyType
    
    function onDeviceReady() {
    	console.log("1. function onDeviceReady( ) begins");
    	persistBodyStyle(type);
    }
    
    /* UNRESOLVED ISSUE: New results are appended instead of replacing old results. */
    function refresh() {
    	top.location.reload(); // NOT USED. Either page reloads to infinity or stops script.
    }
    
    // save to memory button's value for later insertion into query
    function persistBodyStyle(type) {
    	var formBodyStyle = type;
    	console.log("2. persistBodyStyle(type): type = " + type); 
    	console.log("3. persistBodyStyle(type): formBodyStyle = " + formBodyStyle);
        localStorage.setItem('formBodyStyleSet', formBodyStyle); 
    	startDB();
    }
    
    function startDB() {
    	var db = window.openDatabase("Database", "1.0", "CARS", 200000);
    	console.log( "4. startDB( ) begins");
    	db.transaction(populateDB, errorCB, successCB);
    }
    
    function populateDB(tx) {
    	console.log("5. populateDB(tx) begins");
    	tx.executeSql('DROP TABLE IF EXISTS CARS');
    	tx.executeSql('CREATE TABLE IF NOT EXISTS CARS (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 CARS (id, bodyType, category, name, photo, resource, caption) VALUES (1, "Short Course", "SC18", "SC18 Ready-To-Run", "http://www.teamassociated.com/pictures/cars_and_trucks/SC18/RTR/20120.ps_md.jpg", "http://www.teamassociated.com/cars_and_trucks/SC18/RTR/","Now you can enjoy Team Associated\'s world-class performance with true scale authenticity in a 1:18 scale electric truck! The SC18 features a newly designed chassis with a fully enclosed, 2-belt drive train system that is capable of handling the extreme amount of power that today\s brushless motors and LiPo batteries can dish out. Along with durability, the drive train is sealed to help keep rocks and dirt away from the gears and pulleys.")');
    	tx.executeSql('INSERT INTO CARS (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 CARS (id, bodyType, category, name, photo, resource, caption) VALUES (3, "Short Course", "SC10 4x4", "SC10 4x4 RTR Combo", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10_4x4/RTR_Combo/lucas-body-left-7_7661_md.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10_4x4/RTR_Combo/","The SC10 4x4 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 CARS (id, bodyType, category, name, photo, resource, caption) VALUES (4, "Buggy", "RC8", "RC8RS RTR", "http://www.teamassociated.com/pictures/cars_and_trucks/RC8/RTR/80905_md.jpg", "http://www.teamassociated.com/cars_and_trucks/RC8/RTR/","The RC8RS Race Spec RTR is based on the Factory Team RC8, making it the only 1:8 buggy with the high-performance qualities that can live up to Team Associated\'s toughest standards. The RC8RS is a winner right out of the box, having been designed for the highest level of performance and off-road fun, with more suspension travel than any other buggy in its class.")');
    	tx.executeSql('INSERT INTO CARS (id, bodyType, category, name, photo, resource, caption) VALUES (5, "Buggy", "RC10 Classic", "RC10 Classic - Limited Release", "http://www.teamassociated.com/pictures/cars_and_trucks/RC10_Classic/RC10_Classic_Kit/6001_md.jpg", "http://www.teamassociated.com/cars_and_trucks/RC10_Classic/RC10_Classic_Kit/","The RC10 started as the vision of Team Associated\'s founder Roger Curtis (RC) and became one of the most iconic RC cars in history. In celebration of the 30th anniversary of the original RC10 we are proud to bring to you the RC10 Classic Kit, a faithful reproduction of Roger\'s world championship-winning design that changed the world of RC off-road racing forever.")');
    	queryDB(tx);
    }
    
    // Form the query
    
    function queryDB(tx) {
    	console.log( "6. queryDB(tx) begins");
        var formBodyStyleGet = localStorage.getItem('formBodyStyleSet');  
        console.log( "7. queryDB(tx): formBodyStyleGet = " + formBodyStyleGet);
    	tx.executeSql("SELECT * FROM 'CARS' WHERE bodyType == '" + formBodyStyleGet + "'", [], querySuccess, errorCB);
    }
    
    // Display the results
    
    function querySuccess(tx, results) {
    console.log("8. querySuccess(tx, results) begins");
    	var len = results.rows.length;
    	console.log("CARS table: " + len + " rows found.");
    	for (var i=0; i<len; i++){
    		document.getElementById("output").innerHTML +=
    			"<div class='segment'><p class='title'>" + results.rows.item(i).bodyType + ": " + results.rows.item(i).name + "</p>" +
    			"<img class='pic' src='" + results.rows.item(i).photo + "' width='150px'>" +
    			"<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() {
    console.log("9. successCB() begins");
    	
    }
    
    </script>
    
    <style type="text/css">
    .buttonClass, h2, p {
    	font-family: "Helvetica"; color: #000; font-size:1em;
    }
    .buttonClass {
    	border-radius:8px; background-color:#fff;
    	border:#878787 solid 1px; padding:0 1em;margin:.5em;
    	height: 3em; width: 46%;
    	text-align:center;
    	-webkit-appearance:none;
    } 
    .segment {
    	display:block; border-radius:8px; background-color:#eee;
    	border:#878787 solid 1px; padding:1em; margin:.5em;
    	-webkit-appearance:none; 
    	height: auto; 
    }
    h2 {
    	font-size:1.3em; font-weight: bold;
    }
    
    @media screen and (max-width:800px) {
    	.buttonClass { width: 100%;}
    }
    
    /* style the query output */
    p { 
    	text-align:left; margin-left: 170px; 
    }
    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 */
    	float: left;
    }
    </style>
    
    </head>
    <body>
            
    	<input type="button" class="buttonClass" id="buggy" value="Buggy" onclick='persistBodyStyle("Buggy");'> 
    	<input type="button" class="buttonClass" id="shortCourse" value="Short Course" onclick="persistBodyStyle('Short Course');"> 
    
    	<span id="output"></span>
    
    </body>
    </html>
    Steve Husting

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    DROP TABLE IF EXISTS works just fine -- the table will be gone, you can rest assured

    try it outside of your javascript to confirm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    DROP TABLE IF EXISTS works just fine -- the table will be gone, you can rest assured

    try it outside of your javascript to confirm
    It doesn't work in Google Chrome by double-clicking on the file, nor when compiled via PhoneGap and tried in my Nexus 7 device.

    So it worked for you?
    Steve Husting

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,147
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    If you don't believe me, believe Rudy. That query will drop the table if it exists. He didn't say "try other browsers" he meant from the command line or a tool like Workbench.

    http://cordova.apache.org/
    Cordova is available for the following platforms: iOS, Android, Blackberry, Windows Phone, Palm WebOS, Bada, and Symbian.
    Are you testing using one of those platforms?

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    If you don't believe me, believe Rudy. That query will drop the table if it exists. He didn't say "try other browsers" he meant from the command line or a tool like Workbench.

    http://cordova.apache.org/

    Are you testing using one of those platforms?
    Yes; my Nexus 7 is an Android device. I'm puzzled because tapping on the buttons keep appending the new data under the old instead of dropping the table (in Google and device). I know it SHOULD drop the table. Something is missing here.
    Steve Husting

  6. #6
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,147
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    Isn't that what the += is supposed to to?
    Code JavaScript:
    		document.getElementById("output").innerHTML +=
    			"<div class='segment'><p class='title'>" + results.rows.item(i).bodyType + ": " + results.rows.item(i).name + "</p>" +
    			"<img class='pic' src='" + results.rows.item(i).photo + "' width='150px'>" +
    			"<p class='caption'>" + results.rows.item(i).caption + "</p>" +
    			"<p class='more'><a href='" + results.rows.item(i).resource + "'>More</a></p>";
    i.e. the table is getting dropped but the old results aren't cleared from memory.

    If you close the browser and go to it again, are all of them still there?

    If you put
    Code JavaScript:
    document.getElementById("output").innerHTML = "";
    at the beginning of the populateDB(tx) function (or simply remove the +) does it behave like you expect?

  7. #7
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The page is always cleared when doing a refresh.

    When changing from += to =, then only one table row is displayed on tapping the button -- the final one. The previous rows are all overwritten by the later rows.
    Steve Husting

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Solved!

    Quote Originally Posted by StevenHu View Post
    The page is always cleared when doing a refresh.

    When changing from += to =, then only one table row is displayed on tapping the button -- the final one. The previous rows are all overwritten by the later rows.
    Your response gave me the solution Just preface the innerHTML with = "":

    HTML Code:
    document.getElementById("output").innerHTML = "";
    like this:

    HTML Code:
    function querySuccess(tx, results) {
    console.log("8. querySuccess(tx, results) begins");
    	var len = results.rows.length;
    	console.log("CARS table: " + len + " rows found.");
    	document.getElementById("output").innerHTML = ""; // REMOVE THE PREVIOUS CONTENT
    	for (var i=0; i<len; i++){
    		document.getElementById("output").innerHTML +=
    			"<div class='segment'><p class='title'>" + results.rows.item(i).bodyType + ": " + results.rows.item(i).name + "</p>" +
    			"<img class='pic' src='" + results.rows.item(i).photo + "' width='150px'>" +
    			"<p class='caption'>" + results.rows.item(i).caption + "</p>" +
    			"<p class='more'><a href='" + results.rows.item(i).resource + "'>More</a></p>";
    	} document.getElementById("output").innerHTML += "</div>";
    }
    Thanks!
    Steve Husting

  9. #9
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,147
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    Glad you got it sorted

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    glad it wasn't a database problem after all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •