B4J Library [BANanoSQLite] SQLiteDB PHP CRUD Class for BANano

Ola

UPDATE 2020-05-19: Please use this library instead

Update: BANanoSQLite1 Example

If you are opting for MySQL connectivity, BANanoMySQL is available here.

My journey finally has led me to complete a basic wrapper that one can use for PHP SQLite access.

Initially, we tried...

Distributing and Accessing SQLite Databases

However, the parent of this class here is Exploring and Using PHP and SQLite for your BANano WebApp as this continues on this note.

For reference, one can also look at BANanoMySQL, a class and PHP script for MySQL PHP CRUD functionality.

So in the same light, we explore, INSERT, SELECT, UPDATE and DELETE for SQLite dbs located in the server via PHP. This works just like BANanoMySQL as one needs to indicate the field types that they are processing when running queries.

I am using XAMPP for testing my developments, so first things first.

Open PHP.INI and edit the line to activate sqlite3 functionality...

extension=sqlite3

You will also need BANanoSQLite attached in this demo. Let's look at the CRUD statements to complete this task...

Again, we look at parameter based statements to work with our database. We will create a database, create a table, insert some records, select them and delete some.

PHP Prepared Statements

function prepareStatement($db, $sql, $types, $values) {
        /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
        $stmt = $db->prepare($sql);
        $n = count($types);
        for($i = 0; $i < $n; $i++) {
            $param_type = $types[$i];
            $param_value = $values[$i];
            $loc = $i + 1;
            switch($param_type){
                case "s":
                    $stmt->bindValue($loc, '$param_value', SQLITE3_TEXT);
                    break;
                case "i":
                    $stmt->bindValue($loc, $param_value, SQLITE3_INTEGER);
                    break;
                case "d":
                    $stmt->bindValue($loc, $param_value, SQLITE3_FLOAT);
                    break;
                case "b":
                    $stmt->bindValue($loc, $param_value, SQLITE3_BLOB);
                    break;
            }
        }
        return $stmt;
}

This is the function that builds up our prepared statement for SQLite...

The Engine that does everything...

function BANanoSQLite($dbname,$data) {
       $db;
    //set the header
    header('content-type: application/json; charset=utf-8');
       $db = new SQLite3($dbname);
    if(!$db) {
          $response = $db->lastErrorMsg();
          $output = json_encode(Array("response" => $response));
          die($output);
    }
    //data Is json, set it As a php variable
    $data = json_decode($data, True);
    //get the command To execute
    $command = $data["command"];
    $sql = $data["sql"];
    $values = $data["args"];
    $types = $data["types"];
    $fields = $data["fields"];
    switch($command){
        Case "select":
            $res = $db->query($sql);
            $rows = Array();
            while($row = $res->fetchArray()) {
                $rows[] = $row;
            }
            $output = json_encode(array("response" => "OK", "data" => $rows));
              echo $output;
            break;
        case "deletewhere":
            //build the prepared statement
            $stmt = prepareStatement($db, $sql, $types, $values);
            $res = $stmt->execute();
            $affRows = $db->changes();
            $output = json_encode(array("response" => "OK", "data" => $affRows));
              echo $output;
            break;
.....
.....
.....

As noted above, when we call BANanoSQLite, the db, if it does not exist is created, if it does, it gets opened...

Let's look at the usage...

We define a few parameters..

Dim sqlite As BANanoSQLite
    sqlite.Initialize
    dbName = $"${AppName}.db"$

Let's look at some other things...

How to use this class?

In Process Globals I have defined a variable..

Public AppName As String = "BANanoSQLiteDemo"

In AppStart of your BANanoApp, define your php paramaters for your SQLite database connection..

'set php settings
    BANano.PHP_NAME = "bananosqlite.php"
    BANano.PHPHost = $"http://localhost/${AppName}/"$
    BANano.PHPAddHeader("Access-Control-Allow-Origin: *")

In BANano_Ready, let's created the needed tables for our SQLite db, the database will be created on the root of your server for the app.

Dim sqlite As BANanoSQLite
    sqlite.Initialize
    dbName = $"${AppName}.db"$

NB: The lib will be loaded soon for us to enjoy...

 

[Johan Hormaza]

Is there any particular error that its giving you? The contacts example that you have provided me has 0% SQLite in it, can you send me something that at least shows that you tried to implement something on your code based on your understanding of this tutorial?

ok here!

 

[Mashiane]

Noted, please be advised I'm doing this during my spare time and also dependent on my internet connectivity and whether I have internet on my side. This depends on a lot of factors. Thus the delays.

I will take a look at your code and revert back.

 

[Mashiane]

@Johan Hormaza , I have created a full implement of SQLite backend here for the Expense Tracker example that I started with BANAnoSQL as a tutorial that's specific to BANanoVueMaterial.

Find it here.

Enjoy!