B4J Tutorial [BANano] MySQL CRUD with PHP - Part 3.1 (A look an inline PHP)

Ola

NB: The code here explains the use of parameter based queries for CRUD

I have been exploring MySQL with BANano, this was previously done using external PHP files and thus using .CallAjax BANano Calls.

This was explained in

[BANano] MySQL CRUD with PHP - Part 1
[BANano] MySQL CRUD with PHP - Part 2

The final thread on this topic is BANanoMySQL

Part 3.1 is a beginning of an exercise to reach CRUD functionality, as an intro to how I'm currently managing a little bit of database administration using inline php, thanks to BANano.

I guess after exploring more of inline php with my other thread here, I have like fallen inlove at how simple it is. Off course, not being an expert on PHP is rather posing a challenge. I've spent a lot of time just researching, debugging, curly brackets are a pain yho!

This is what we will do here...

1. Test a connection to MySQL
2. Create a database in MySQL
3. Drop the MySQL database
4. Create a table in the MySQL server
5. Drop the table in the MySQL database
6. Drop the database

How is this done?

A class module to build generic SQL commands exists, this is used to build any SQL queries that we need to pass to out inline PHP, specifically for CRUD functionality. The existence of a single PHP script to handle all the commands for CRUD ensures that any CRUD specific project can easily use the PHP script and the class module to perform such functions. The dependency will just be the data and nothing else.

This is all done with inline php and just calling a single function from it, MySQL_ExecuteOnly Wow! Here is it.

#if PHP
function MySQL_ExecuteOnly($servername,$username,$password,$db,$sql,$command) {
    //set the header
    header('content-type: application/json; charset=utf-8');
    //connect to MySQL
    $conn = new mysqli($servername, $username, $password);
    //we cannot connect return an error
    if ($conn->connect_error) {
        $response = $conn->connect_error;
        $output = json_encode(array("response" => $response));
        die($soutput);
    }
    if ($command == "connection"){
        // we were just testing the connection
        $response = "OK";
    } else {
        //if we are creating/dropping the db, just execute sql
        $someCommands = array('createdb', 'dropdb');
        $sql = mysqli_real_escape_string($conn, $sql);
        $db = mysqli_real_escape_string($conn, $db);    
        if(in_array($command,$someCommands)) {
                if ($conn->query($sql) === TRUE) {
                    $response = "OK";
                } else {
                    $response = $conn->error;
                }
        } else {
            //select db and execute sql if db exist
            $selected = mysqli_select_db($conn,$db);
            if (!$selected) {
                $response = $conn->error;
                $output = json_encode(array("response" => $response));
                die($output);
            }
            if ($conn->query($sql) === TRUE) {
                $response = "OK";
            } else {
                $response = $conn->error;
            }
        }
    }
    $output = json_encode(array("response" => $response));
    echo($output);
    $conn->close();
}
#End If

Assumptions:

• I am testing this with XAMP with MySQL installed.
• I will assume that you can create UX with BANano as only the logic of the code will be discussed here. This includes using .GetValue, .SetValue, .GetChecked, .SetChecked.
• The focus here is rather on using the inline php.

Let's start

I am creating a mocking app which has to create a backend MySQL db, create the needed table, generate dummy data as a start. How to generate dummy data using and extending an existing code module was discussed on my post here.

On AppStart, we need to tell BANano the details of the PHP file we are using...

BANano.PHP_NAME = "uoemock.php"
    BANano.PHPHost = "http://localhost/" & ShortName & "/"
    BANano.PHPAddHeader("Access-Control-Allow-Origin: *")

The contents of the PHP file on your build folder, will not be generated until you call CallInlinePHP - kinda cool.

Designing UOEMock - An internal app for the TGIF Zone Inc.

The app I'm working on features a treeview and a grid. I did a discussion about the treeview here and the grid has been featured here. I had created a UX lib that I'm using for my BANano apps, this was also featured here.

How the EasyHints class was used for the hints was also featured here.

Let's watch something...

 

[Mashiane]

1. Test a connection to MySQL

From the video above, you saw how a button was clicked to test the connection to mysql. The form content is read and this is passed to the inline php call to check for such a connection. This is how I have done my implementation with UOENow code base.

Sub btnTestConnection_click(e As BANanoEvent)
    Dim bRequired As Boolean = App.Required(Array("host","dbname","username"))
    If bRequired Then
        App.ToastError("Some fields for the database connection are outstanding, please fix!")
        Return
    End If
    Dim apprec As Map = App.GetValues(Array("host","port","dbname","username","password"))
    Dim shost As String = apprec.Get("host")
    Dim sdbname As String = apprec.Get("dbname")
    Dim susername As String = apprec.Get("username")
    Dim spassword As String = apprec.Get("password")
    'call inlinephp to test the connection
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":"","command":"connection")
    banano.CallInlinePHP("MySQL_ExecuteOnly",tc,"connection")
End Sub

The host, dbname and username are required values. MySQL dbs can have a blank root password (especially after you install xamp) so I left it not to be required. I can combine .GetValues and .Required so that I dont re-read the values again or even better use a "form" element in my code and then serialize the form (havent done so), thus reading the inputs like this.

My App.GetValues is a function that is passed the element id existing on the page. What it does is to loop through the provided list and then create a map and then run BANano.GetValue and assigns it to the map... (I had to ensure that my elements have a 'type' attribute for this to work).

'get values from elements and save as map
Sub GetValues(flds As List) As Map
    Dim rec As Map
    rec.Initialize
    For Each strfld As String In flds
        strfld = strfld.tolowercase
        Dim el As BANanoElement = BANano.GetElement($"#${strfld}"$)
        Dim elType As String = el.GetAttr("type")
        elType = elType.tolowercase
        Select Case elType
            Case "checkbox"
                Dim bV As Boolean = el.GetChecked
                rec.Put(strfld,bV)
            Case "radio"
                Dim strV As String = GetRadio(strfld)
                rec.Put(strfld,strV)
            Case "date","email","hidden","month","number","password","range","search","tel","text","time","url","week", "textarea","select"
                Dim strV As String = el.GetValue
                rec.Put(strfld,strV)
            Case "file"
            Case "datetime-local","color"
                Dim strV As String = el.GetValue
                rec.Put(strfld,strV)
            Case "image"
        End Select
    Next
    Return rec
End Sub

So after I read the values I need and assign to a map, I extract the ones I need and then feed these to the BANano.CallInlinePHP method.

function MySQL_ExecuteOnly($servername,$username,$password,$db,$sql,$command) {

The exact sequence of the variables I want to pass is / should match.

Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":"","command":"connection")
    banano.CallInlinePHP("MySQL_ExecuteOnly",tc,"connection")

From this sub, we are executing the "connection", this being the uniqueId to be used inside BANano to know about the call. We later trap the result of the call. As noted in the PHP code, at times we return an "OK" or else the error generated by MySQL.

The CallBack for the inline php has been nicely defined. We are just telling the end user of the results, whether these are errors or success.

Sub BANano_CallInlinePHPResult(Success As Boolean, UniqueID As String, Result As String)
    If Success Then
        'define commands
        Dim commands As Map
        commands.Initialize
        commands.Put("droptable","MySQL table dropped successfully!")
        commands.Put("createtable","MySQL table created successfully!")
        commands.Put("connection","Connection to MySQL successful!")
        commands.Put("createdb","MySQL Database created successful!")
        commands.Put("dropdb","MySQL Database dropped successfully!")
        Select Case UniqueID
            Case "droptable","createtable","connection","createdb","dropdb"
                Dim resp As Map = App.Json2Map(Result)
                Dim strresp As String = resp.Get("response")
                Select Case strresp
                Case "OK"
                    Dim msg As String = commands.Get(UniqueID)
                    App.ToastSuccess(msg)
                    TableModal.hide   
                Case Else
                    App.ToastError(strresp)
                End Select
        End Select
    Else
        App.ToastSuccess("An error was experienced executing your MySQL command, please try again!")
    End If
End Sub

So on a connection made, the uniqueid will be "connection", then we Toast a message "Connection to MySQL successful!" as seen in the video. There you have it.

 

[Mashiane]

2. Create a database in MySQL

To create the database for MySQL, we need to run the CREATE DATABASE method. So, we just need to pass this command to our MySQL_ExecuteOnly php method.

Just like the code in post #2 above, we read our form contents and then build the SQL command to create the db and pass this to our PHP.

Sub btnCreateDB_click(e As BANanoEvent)
    Dim bRequired As Boolean = App.Required(Array("host","dbname","username"))
    If bRequired Then
        App.ToastError("Some fields for the database connection are outstanding, please fix!")
        Return
    End If
    Dim apprec As Map = App.GetValues(Array("host","port","dbname","username","password"))
    Dim shost As String = apprec.Get("host")
    Dim sdbname As String = apprec.Get("dbname")
    Dim susername As String = apprec.Get("username")
    Dim spassword As String = apprec.Get("password")
    'define the sql command
    Dim sql As String = UOENowMySQL.SQL_CreateDatabase(sdbname)
    'call inlinephp to create the database
    Dim icmd As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":sql,"command":"createdb")
    banano.CallInlinePHP("MySQL_ExecuteOnly", icmd, "createdb")
End Sub

The MySQL_ExecuteOnly function in php works like this.

The code runs and creates a connection to MySQL. This is the default functionality. If our "command", passed as $command, is "connect", we just return "OK" if everything is ok, or else return the connection error, i.e. die().

Now, if our command is not connect but createdb / dropdb, there is no reason for us to select the database, we just execute the command to drop / create the db. However if our command is either of those, we select the db and then execute the commands. This method so far just deals with an execute ONLY mode and does not return any results. In part 3.2 we will look at inserts, deletes, updates and reads specific to crud functionality.

 

[Mashiane]

3. Drop the MySQL database

This also works like #3 above. The command to drop the database is created and this is passed to the php call.

Sub btnDeleteDB_click(e As BANanoEvent)
    Dim bRequired As Boolean = App.Required(Array("host","dbname","username"))
    If bRequired Then
        App.ToastError("Some fields for the database connection are outstanding, please fix!")
        Return
    End If
    Dim apprec As Map = App.GetValues(Array("host","port","dbname","username","password"))
    Dim shost As String = apprec.Get("host")
    Dim sdbname As String = apprec.Get("dbname")
    Dim susername As String = apprec.Get("username")
    Dim spassword As String = apprec.Get("password")
    'define the sql command
    Dim sql As String = UOENowMySQL.SQL_DropDataBase(sdbname)
    'call inlinephp to create the database
    Dim icmd As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":sql,"command":"dropdb")
    banano.CallInlinePHP("MySQL_ExecuteOnly", icmd, "dropdb")
End Sub

As you will note the important things here are the command to execute and then the command to pass to the inline php call, in this case "dropdb". When executed, the db wont be selected but the code to drop the db will be executed directly and then control back to banano to trap the result of the ajax call.

 

[Mashiane]

4. Create a table in the MySQL server

Our table definition expects the indication of the table name, the primary key, whether its going to be auto incremented and the number of dummy records to create. As soon as a user selectes Create MySQL Table, the code to create the table is executed, only for the primary key field to be added.

Same with the code to delete the table, one needs to select Delete MySQL Table and the table will be deleted.

Sub btnTableCreate_click(e As BANanoEvent)
    'check required fields
    Dim bRequired As Boolean = App.Required(Array("tablename","tableprimarykey","tablerecords"))
    If bRequired Then
        App.ToastError("There are some details that are not completed, please correct!")
        Return
    End If
    'read the form details
    Dim uTable As Map = App.GetValues(Array("tablename","tableprimarykey","tableautoincrement"))
    Dim stblName As String = uTable.Get("tablename")
    Dim stableprimarykey As String = uTable.Get("tableprimarykey")
    Dim stableautoincrement As Boolean = uTable.Get("tableautoincrement")
    Dim auto As String = ""
    If stableautoincrement Then auto = stableprimarykey
    'createmap does not play well with variable keys
    Dim fld As Map
    fld.Initialize
    fld.Put(stableprimarykey,App.EnumFieldTypes.DB_INT)
    'generate command to create the table
    Dim sql As String = UOENowMySQL.SQL_CreateTable(stblName,fld,stableprimarykey,auto)
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":sql,"command":"createtable")
    banano.CallInlinePHP("MySQL_ExecuteOnly",tc,"createtable")
End Sub

The crux of this is the SQL_CreateTable code that is needed to create the table. This is code originally sourced from DBUtils.

'return a sql command to create the table
public Sub SQL_CreateTable(tblName As String, tblFields As Map, PK As String, Auto As String) As String
    If tblFields.Size = 0 Then
        Log($"SQL_CreateTable: ${tblName} - tblFields map empty!"$)
        Return Null
    End If
    Dim fldName As String
    Dim fldType As String
    Dim fldTot As Int
    Dim fldCnt As Int
    fldTot = tblFields.Size - 1
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("(")
    For fldCnt = 0 To fldTot
        fldName = tblFields.GetKeyAt(fldCnt)
        fldType = tblFields.Get(fldName)
        If fldCnt > 0 Then
            sb.Append(", ")
        End If
        sb.Append(EscapeField(fldName))
        sb.Append(" ")
        sb.Append(fldType)
        If fldName.EqualsIgnoreCase(PK) Then
            sb.Append(" NOT NULL PRIMARY KEY")
        End If
        If fldName.EqualsIgnoreCase(Auto) Then
            sb.Append(" AUTO_INCREMENT")
        End If
    Next
    sb.Append(")")
    'define the qry to execute
    Dim query As String = "CREATE TABLE IF NOT EXISTS " & EscapeField(tblName) & " " & sb.ToString
    Return query
End Sub

Our EscapeField method has been tweaked to work with MySQL.

Private Sub EscapeField(f As String) As String
    Return $"`${f}`"$
End Sub

Well, I still dont understand why they opted for `{}`, well it works. Our "createtable" command is passed for the inline PHP and then the table is created. We saw it in phpAdmin.

 

[Mashiane]

5. Drop the table in the MySQL database

private Sub DeleteMySQLTable(stablename As String)
    'generate command to create the table
    Dim sql As String = UOENowMySQL.SQL_DropTable(stablename)
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":sql,"command":"droptable")
    banano.CallInlinePHP("MySQL_ExecuteOnly",tc,"droptable")
End Sub

Also here, we read the details of the table to delete from the database and then execute the call to PHP. You will note that each time we pass the database connection settings. This is because each time we run a call to the db, a connection to the db is made and then closed.

$conn->close();

just before the end of the PHP file.

 

[Mashiane]

6. Drop the database

Sub btnDeleteDB_click(e As BANanoEvent)
    Dim bRequired As Boolean = App.Required(Array("host","dbname","username"))
    If bRequired Then
        App.ToastError("Some fields for the database connection are outstanding, please fix!")
        Return
    End If
    Dim apprec As Map = App.GetValues(Array("host","port","dbname","username","password"))
    Dim shost As String = apprec.Get("host")
    Dim sdbname As String = apprec.Get("dbname")
    Dim susername As String = apprec.Get("username")
    Dim spassword As String = apprec.Get("password")
    'define the sql command
    Dim sql As String = UOENowMySQL.SQL_DropDataBase(sdbname)
    'call inlinephp to create the database
    Dim icmd As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"sql":sql,"command":"dropdb")
    banano.CallInlinePHP("MySQL_ExecuteOnly", icmd, "dropdb")
End Sub

When mistakes happen as they usually do, one would like to delete the database, the "dropdb" command can be executed to directly delete the db from MySQL.

Ta!

#HelpingOthers2Succeed.

 

[Mashiane]

Beginning CRUD - let's look at C - creating / INSERTs.

For a while I have been thinking about how I was going to do this, because basically I need to use prepared statements. Finally through searching the interweb, I found a solution that could guide me achieve what I needed. Basically I intend to use 1 sub for the CRUD functionality. So here is the begginning of it.

First I defined a test case to insert a record, this is continuation from the posts above.

Sub TestInsert
    'generate command to create the table
    Dim rec As Map = CreateMap("firstname":"Anele","lastname":"Mbanga", "dob": "1973-04-15","isalive":True,"pob":"East London","age":45)
    Dim rect As Map = CreateMap("firstname":"STRING","lastname":"STRING", "dob": "DATE","isalive":"INT","pob":"STRING","age":"INT")
    'build the insert stuff
    Dim result As Map = UOENowMySQL.SQL_Insert("myfamily",rec,rect)
    'sql command
    Dim sql As String = result.Get("sql")
    'values
    Dim args As List = result.Get("args")
    'types for prepared statement
    Dim types As List = result.Get("types")
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    'define stuff to feed to MySQL
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword, _
    "db":sdbname,"sql":sql,"types":types,"values":args,"command":"insert")
    'execute mysql statement
    banano.CallInlinePHP("MySQL_Insert",tc,"insert")
End Sub

Basically, the record to be inserted to the database is defined with rec and then the data types of the records to insert. Parameter queries expect a definition of

s - string
b - blob
i - integer
d - double.

I could easily used "sssisi" in my case, but I wanted this to be controlled by my sub to insert records, which is..

'return a sql insert statement
Sub SQL_Insert(tblName As String, tblFields As Map, tblTypes As Map) As Map
    ' Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
    If tblFields.Size = 0 Then
        Log($"SQL_Insert: ${tblName} - tblFields map empty!"$)
        Return Null
    End If
    Dim sb As StringBuilder
    Dim columns As StringBuilder
    Dim values As StringBuilder
    Dim listOfValues As List
    Dim listOfTypes As List
    Dim iCnt As Int
    Dim iTot As Int
    sb.Initialize
    columns.Initialize
    values.Initialize
    listOfValues.Initialize
    listOfTypes.Initialize
    sb.Append($"INSERT INTO ${EscapeField(tblName)} ("$)
    iTot = tblFields.Size - 1
    For iCnt = 0 To iTot
        Dim col As String = tblFields.GetKeyAt(iCnt)
        Dim value As String = tblFields.Get(col)
        Dim colType As String = tblTypes.Get(col)
        Select Case colType
        Case "STRING","VARCHAR","TEXT","DATE","DATETIME"
            listOfTypes.add("s")
        Case "INTEGER", "INT", "BOOL"
            listOfTypes.add("i")
        Case "REAL","FLOAT"
            listOfTypes.add("d")
        Case "BLOB"
            listOfTypes.Add("b")
        End Select   
        If iCnt > 0 Then
            columns.Append(", ")
            values.Append(", ")
        End If
        columns.Append(EscapeField(col))
        values.Append("?")
        listOfValues.Add(value)
    Next
    sb.Append(columns.ToString)
    sb.Append(") VALUES (")
    sb.Append(values.ToString)
    sb.Append(")")
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.ToString)
    m.Put("args", listOfValues)
    m.Put("types", listOfTypes)
    Return m
End Sub

This sub returns a map containing the sql query with ? for all parameters, the args (values, a list variable) and also the types [s,s,s,i,s,i], these are passed to my CallInlinePHP method

A look at the MySQL_Insert PHP code... [NB: Data Validation (sanitation) needs to happen on the data you need to insert]

function MySQL_Insert($servername,$username,$password,$db,$sql,$types,$values,$command) {
    //Reference: https://www.pontikis.net/blog/dynamically-bind_param-array-mysqli
    //set the header
    header('content-type: application/json; charset=utf-8');
    //connect to MySQL
    $conn = new mysqli($servername, $username, $password, $db);
    //we cannot connect return an error
    if ($conn->connect_error) {
        $response = $conn->connect_error;
        $output = json_encode(array("response" => $response));
        die($output);
    }
    mysqli_query($conn,"SET CHARACTER SET utf8");
    mysqli_query($conn,"SET NAMES 'utf8'");
    $sql = mysqli_real_escape_string($conn, $sql);
    //paramater types to execute
    /* Bind parameters. Types: s = string, i = integer, d = double,  b = blob */
    $a_params = array();
    $param_type = '';
    $n = count($types);
    for($i = 0; $i < $n; $i++) {
        $param_type .= $types[$i];
    }
    $a_params[] = & $param_type;
    //values to execute
    for($i = 0; $i < $n; $i++) {
        $a_params[] = & $values[$i];
    }
    /* Prepare statement */
    $stmt = $conn->prepare($sql);
    if($stmt === false) {
        $response = $conn->error;
        $output = json_encode(array("response" => $response));
        die($output);
    }
    /* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
    call_user_func_array(array($stmt, 'bind_param'), $a_params);
    /* Execute statement */
    $result = $stmt->execute();
    if ($result) {
        switch($command){
        case "insert":
            //get last insert id
            $lastId = $conn->insert_id;
            //get affected rows
            $affRows = $conn->affected_rows;
            //the response should be ok
            $response = "OK";
            $output = json_encode(array("response" => $response, "lastid" => $lastId, "affectedRows" => $affRows));
            break;
        }
    } else {
        $response = $conn->error;
        $output = json_encode(array("response" => $response));
    }
    echo($output);
    $conn->close();
}

Results in this output for the echo on success:

{"response":"OK","lastid":10,"affectedRows":1}

Now tweaking this to work for Updates, Deleted and Reads should be easy.

WatchThisSpace!

 

[Mashiane]

Updates

To perform such operations, we need to build the SQL commands to pass to the PHP file. Our PHP file is based on parameter based queries that help avoid sql injections. However, the data that is fed to those parameters must be cleaned / sanitized first.

To update we need to pass a where clause because we need to update the records using some field and its value. As my example is based on an app that creates the db etc, passing the connection variables on your application should be avoided. In my next thread on this, we will have a fully functional CRUD example using inline php.

Our code to execute the php call for my app is based on this...

Sub TestUpdateWhere
    Dim mysql As UOENowMySQL
    mysql.Initialize
    Dim lastid As String = banano.GetSessionStorage("lastid")
    'build the select command with an order clause
    Dim rec As Map
    rec.Initialize
    rec.Put("firstname", nd.rand_FirstName)
    rec.Put("lastname", nd.rand_lastname)
    rec.put("dob", nd.Rand_Date)
    rec.Put("isalive", nd.Rand_Boolean_Value)
    rec.Put("pob", nd.Rand_Capital_City)
    rec.Put("age", nd.Rand_Age(CreateMap("type":"adult")))
    Dim data As String = mysql.SQL_UpdateWhere("myfamily", rec, CreateMap("id":lastid))
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    'execute mysql statement
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"data":data)
    banano.CallInlinePHP("MySQL_Execute",tc,"updatewhere")
End Sub

For test, we have stored the last insert id from MySQL, we get the database connection details, build the SQL_UpdateWhere clause and then call inlinePhp to execute it.

For parameter based queries, the data types should also be passed to the query.

'return a sql to update records of table where one exists
Sub SQL_UpdateWhere(tblName As String, tblfields As Map, tblWhere As Map) As String
    Dim listOfTypes As List = GetMapTypes(tblfields)
    Dim listOfTypes1 As List = GetMapTypes(tblWhere)
    listOfTypes.AddAll(listOfTypes1)
    Dim listOfValues As List = GetMapValues(tblfields)
    Dim listOfValues1 As List = GetMapValues(tblWhere)
    listOfValues.AddAll(listOfValues1)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append($"UPDATE ${EscapeField(tblName)} SET "$)
    Dim i As Int
    Dim iTot As Int = tblfields.Size - 1
    For i = 0 To iTot
        Dim col As String = tblfields.GetKeyAt(i)
        sb.Append(EscapeField(col))
        If i <> iTot Then
            sb.Append("= ?,")
        Else
            sb.Append("= ?")
        End If
    Next
    sb.Append($" WHERE "$)
    Dim iWhere As Int = tblWhere.Size - 1
    For i = 0 To iWhere
        If i > 0 Then
            sb.Append(" AND ")
        End If
        Dim col As String = tblWhere.GetKeyAt(i)
        sb.Append(EscapeField(col))
        sb.Append(" = ?")
    Next
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("args", listOfValues)
    m.Put("types", listOfTypes)
    m.Put("command", "updatewhere")
    Dim res As String = Map2Json(m)
    Return res
End Sub

For consistency, all our SQL_Commands return a map object based on "sql","args","types" and "command", this is inline with what the inline PHP expects (hard coded).

 

[Mashiane]

Deletes

The delete commands are based on a delete where clause too, where a record is deleted based on a field and its value.

Sub TestDeleteWhere
    Dim mysql As UOENowMySQL
    mysql.Initialize
    Dim lastid As String = banano.GetSessionStorage("lastid")
    'build the select command with an order clause
    Dim data As String = mysql.SQL_DeleteWhere("myfamily", CreateMap("id":lastid))
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    'execute mysql statement
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"data":data)
    banano.CallInlinePHP("MySQL_Execute",tc,"deletewhere")
End Sub

The SQL_DeleteWhere command structure is built with

'return a sql to delete record of table where one exists
Sub SQL_DeleteWhere(tblName As String, tblWhere As Map) As String
    Dim listOfTypes As List = GetMapTypes(tblWhere)
    Dim listOfValues As List = GetMapValues(tblWhere)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append($"DELETE FROM ${EscapeField(tblName)} WHERE "$)
    Dim i As Int
    Dim iWhere As Int = tblWhere.Size - 1
    For i = 0 To iWhere
        If i > 0 Then
            sb.Append(" AND ")
        End If
        Dim col As String = tblWhere.GetKeyAt(i)
        sb.Append(col)
        sb.Append(" = ?")
    Next
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("args", listOfValues)
    m.Put("types", listOfTypes)
    m.Put("command", "deletewhere")
    Dim res As String = Map2Json(m)
    Return res
End Sub

You specify the table name and the field to use to delete the record by.

 

[Mashiane]

Reads (select where)

Select where uses the table name and the fields to select by. These are assumed to be $eq (equal to)

Sub TestSelectWhere
    Dim mysql As UOENowMySQL
    mysql.Initialize
    Dim lastid As String = banano.GetSessionStorage("lastid")
    'build the select command with an order clause
    Dim data As String = mysql.SQL_SelectWhere("myfamily", Array As String("*"), CreateMap("id":lastid), Array("firstname","lastname"))
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    'execute mysql statement
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"data":data)
    banano.CallInlinePHP("MySQL_Execute",tc,"selectwhere")
End Sub

Also a sort order is being specified here as firstname and lastname.

The SQL_SelectWhere stament is build up from...

'return a sql to select record of table where one exists
Sub SQL_SelectWhere(tblName As String, tblfields As List, tblWhere As Map, orderBy As List) As String
    Dim listOfTypes As List = GetMapTypes(tblWhere)
    Dim listOfValues As List = GetMapValues(tblWhere)
    'are we selecting all fields or just some
    Dim fld1 As String = tblfields.Get(0)
    Dim selFIelds As String = ""
    Select Case fld1
        Case "*"
            selFIelds = "*"
        Case Else
            selFIelds = JoinFields(",", tblfields)
    End Select
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append($"SELECT ${selFIelds} FROM ${EscapeField(tblName)} WHERE "$)
    Dim i As Int
    Dim iWhere As Int = tblWhere.Size - 1
    For i = 0 To iWhere
        If i > 0 Then
            sb.Append(" AND ")
        End If
        Dim col As String = tblWhere.GetKeyAt(i)
        sb.Append(col)
        sb.Append(" = ?")
    Next
    If orderBy <> Null Then
        'order by
        Dim stro As String = JoinFields(",", orderBy)
        If stro.Length > 0 Then
            sb.Append(" ORDER BY ").Append(stro)
        End If
    End If
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("args", listOfValues)
    m.Put("types", listOfTypes)
    m.Put("command", "selectwhere")
    Dim res As String = Map2Json(m)
    Return res
End Sub

One is also able to select all records from a table without specifying a where clause. Let's look at the example..

Sub TestSelect
    Dim mysql As UOENowMySQL
    mysql.Initialize
    'build the select command with an order clause
    Dim data As String = mysql.SQL_Select("myfamily",Array("*"),Array("firstname","lastname"))
    'get database connection settings
    Dim prj As Map = banano.GetSessionStorage(ActiveDB)
    Dim sdbname As String = prj.Get("dbname")
    Dim shost As String = prj.Get("host")
    Dim spassword As String = prj.Get("password")
    Dim susername As String = prj.Get("username")
    'execute mysql statement
    Dim tc As Map = CreateMap("servername":shost,"username":susername,"password":spassword,"db":sdbname,"data":data)
    banano.CallInlinePHP("MySQL_Execute",tc,"select")
End Sub

'return a sql to select record of table where one exists
Sub SQL_Select(tblName As String, tblfields As List, orderBy As List) As String
    'are we selecting all fields or just some
    Dim fld1 As String = tblfields.Get(0)
    Dim selFIelds As String = ""
    Select Case fld1
        Case "*"
            selFIelds = "*"
        Case Else
            selFIelds = JoinFields(",", tblfields)
    End Select
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append($"SELECT ${selFIelds} FROM ${EscapeField(tblName)}"$)
    If orderBy <> Null Then
        'order by
        Dim stro As String = JoinFields(",", orderBy)
        If stro.Length > 0 Then
            sb.Append(" ORDER BY ").Append(stro)
        End If
    End If
    Dim m As Map
    m.Initialize
    m.Put("sql", sb.tostring)
    m.Put("args", Null)
    m.Put("types", Null)
    m.Put("command", "select")
    Dim res As String = Map2Json(m)
    Return res
End Sub