I am using the Storage API outlined in: http://docs.phonegap.com/en/3.0.0/cordova_storage_storage.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:
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.)
<!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>
Just preface the innerHTML with = “”: