CODEWITHSUNDEEP
htmlsqlite
Don
Don

Reputation: 39

SQLite Database on Host Server

I'm trying to build a web page with a SQLite database. Everything works except the tables are being stored on the local machine, not the host server. So each user sees only the data they entered, which is pretty useless! How do I point SQLite to the server?

I assume the chages would be made here;

var createStatement = "CREATE TABLE IF NOT EXISTS  Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, phone TEXT)";
     var selectAllStatement = "SELECT * FROM  Contacts";
     var insertStatement = "INSERT INTO  Contacts (firstName, lastName, phone) VALUES (?, ?, ?)";
     var updateStatement = "UPDATE  Contacts SET firstName = ?, lastName = ?, phone = ? WHERE id = ?";
     var deleteStatement = "DELETE FROM  Contacts WHERE id=?";
     var dropStatement = "DROP TABLE  Contacts";

But all the code is listed below............... 

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Best Test</title>
</head>
<body>


<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
$uploads_dir = 'uploads';
$allowedExts = array("gif", "jpeg", "jpg", "png");
$temp = explode(".", $_FILES["file"]["name"]);
$extension = end($temp);
  if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/jpg")
|| ($_FILES["file"]["type"] == "image/pjpeg")
|| ($_FILES["file"]["type"] == "image/x-png")
|| ($_FILES["file"]["type"] == "image/png"))
&& ($_FILES["file"]["size"] < 20000)
&& in_array($extension, $allowedExts))
  {
  if ($_FILES["file"]["error"] > 0)
    {
  echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
    }
  else
    {
  echo "Upload: " . $_FILES["file"]["name"] . "<br>";
  echo "Type: " . $_FILES["file"]["type"] . "<br>";
  echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
  echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";
  if (file_exists("upload/" . $_FILES["file"]["name"]))
      {
    echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],"uploads/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "upload/" . $_FILES["file"]["name"]."<br>";
      $fname = $_FILES["file"]["name"];
      echo "fname = " . $fname;      


      }
    }
  }
else
  {
  echo "Invalid file";
  }

?>

    <div align="center">
     <input type="hidden" id="id"/>
     First name:<input type="text" id="firstName"/><br/>
     Last name:<input type="text" id="lastName"/><br/>

     <button onClick="resetForm()">Reset Form</button>
     <button onClick="updateRecord()">Update </button>
     <button onClick="preset()">Insert & Procede to Picture Selection</button>
     <button onClick="insertRecord()">Insert</button>
     <button onClick="dropTable()">Drop Table</button>
     <div id="results"></div>


    </div>



  </body>

    <script> 
 var results = document.getElementById('results'); 
 var id = document.getElementById('id');
 var firstName = document.getElementById('firstName');  
 var lastName = document.getElementById('lastName');  
 var phone = 6;

 var createStatement = "CREATE TABLE IF NOT EXISTS  Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, phone TEXT)";
 var selectAllStatement = "SELECT * FROM  Contacts";
 var insertStatement = "INSERT INTO  Contacts (firstName, lastName, phone) VALUES (?, ?, ?)";
 var updateStatement = "UPDATE  Contacts SET firstName = ?, lastName = ?, phone = ? WHERE id = ?";
 var deleteStatement = "DELETE FROM  Contacts WHERE id=?";
 var dropStatement = "DROP TABLE  Contacts";

 var db = openDatabase("AddressBook", "1.0", "Address Book", 200000);
 var dataset;
 createTable();

      function onError(tx, error) {
        alert(error.message);
      }

      function showRecords() {
        results.innerHTML = '';
        db.transaction(function(tx) {
          tx.executeSql(selectAllStatement, [], function(tx, result) {
            dataset = result.rows;
            for (var i = 0, item = null; i < dataset.length; i++) {
              item = dataset.item(i);
              results.innerHTML += 
                   '<li>' + item['lastName'] + ' , ' + item['firstName'] + ' , ' + item['phone'] +' <a href="#" onclick="loadRecord('+i+')">edit</a>  ' +  
 '<a href="#" onclick="deleteRecord('+item['id']+')">delete</a></li>';

}
          });
        });
      }

      function createTable() { 
        db.transaction(function(tx) {
          tx.executeSql(createStatement, [], showRecords, onError);
        });
      }

      function insertRecord() {
        db.transaction(function(tx) {
        tx.executeSql(insertStatement, [firstName.value, lastName.value, phone.value], loadAndReset, onError);
        });
      }

      function preset(){
         what ="/uploads/" + "<?php echo $fname; ?>";   
        alert("what = " + what);
        insertRecord2();
              }


      function insertRecord2() {
                alert("Function 2 Started");
                db.transaction(function(tx) {
                tx.executeSql(insertStatement, [firstName.value, lastName.value, what], loadAndReset, onError);
                alert("Function 2 Finished");
        });
      }
    function loadRecord(i) {
    var item = dataset.item(i); 
        firstName.value = item['firstName'];
        lastName.value = item['lastName'];
        phone.value = item['phone'];
        id.value = item['id']; 
      }

      function updateRecord() {
        db.transaction(function(tx) {
        tx.executeSql(updateStatement, [firstName.value, lastName.value, phone.value, id.value], loadAndReset, onError);
        }); 
      }

      function updateRecord2() {
        db.transaction(function(tx) {

          tx.executeSql(updateStatement, [firstName.value, lastName.value, phone.value, id.value], loadAndReset, onError);
        }); 
        fnPass();
      }

      function deleteRecord(id) {
        db.transaction(function(tx) {
          tx.executeSql(deleteStatement, [id], showRecords, onError);
        });
resetForm();
      }

      function dropTable() {
        db.transaction(function(tx) {
          tx.executeSql(dropStatement, [], showRecords, onError);
        });
resetForm();
      }

 function loadAndReset(){
 resetForm();
 showRecords();
 }

 function resetForm(){
firstName.value = '';
lastName.value = '';
phone.value = '';
id.value = ''; 
 }
    </script>



</body>


 Text to Pass: <input type='text' id='idTextToPass'/><br/>
    <input type='button' value='Pass' onclick='fnPass();'/>
</html>

Thanks

Don

Upvotes: 2

Views: 4354

Answers (1)

Aaron Miller
Aaron Miller

Reputation: 3780

You're not actually using SQLite directly; you are using HTML5 LocalStorage, whose backing store is browser-dependent. If you want to use a server-side database, then your database interaction needs to happen on the server side, i.e., in PHP code, rather than in client-side Javascript.

Upvotes: 1

Related Questions