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

Discussion in 'B4J Tutorials' started by Mashiane, May 24, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    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

    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.

    Code:
    #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...

    Code:
    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...

     

    Attached Files:

    Last edited: May 29, 2019
  2. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    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).

    Code:
    'get values from elements and save as map
    Sub GetValues(flds As ListAs 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.

    Code:
    function MySQL_ExecuteOnly($servername,$username,$password,$db,$sql,$command) {
    The exact sequence of the variables I want to pass is / should match.

    Code:
    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.

    Code:
    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.
     
  3. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    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.
     
  4. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    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.
     
  5. Mashiane

    Mashiane Expert Licensed User

    4. Create a table in the MySQL server

    TableM.png

    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.

    Code:
    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.

    Code:
    'return a sql command to create the table
    public Sub SQL_CreateTable(tblName As String, tblFields As Map, PK As String, Auto As StringAs 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.

    Code:
    Private Sub EscapeField(f As StringAs 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.
     
    joulongleu likes this.
  6. Mashiane

    Mashiane Expert Licensed User

    5. Drop the table in the MySQL database

    Code:
    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.

    Code:
    $conn->close();
    just before the end of the PHP file.
     
    joulongleu likes this.
  7. Mashiane

    Mashiane Expert Licensed User

    6. Drop the database

    Code:
    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.
     
    joulongleu likes this.
  8. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    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..

    Code:
    'return a sql insert statement
    Sub SQL_Insert(tblName As String, tblFields As Map, tblTypes As MapAs 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]

    Code:
    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:

    Code:
    {"response":"OK","lastid":10,"affectedRows":1}
    Now tweaking this to work for Updates, Deleted and Reads should be easy.

    WatchThisSpace!
     
    joulongleu likes this.
  9. Mashiane

    Mashiane Expert Licensed User

    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...

    Code:
    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.

    Code:
    'return a sql to update records of table where one exists
    Sub SQL_UpdateWhere(tblName As String, tblfields As Map, tblWhere As MapAs 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).
     
  10. Mashiane

    Mashiane Expert Licensed User

    Deletes

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

    Code:
    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

    Code:
    'return a sql to delete record of table where one exists
    Sub SQL_DeleteWhere(tblName As String, tblWhere As MapAs 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.
     
  11. Mashiane

    Mashiane Expert Licensed User

    Reads (select where)

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

    Code:
    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...

    Code:
    '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 ListAs 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..

    Code:
    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
    Code:
    'return a sql to select record of table where one exists
    Sub SQL_Select(tblName As String, tblfields As List, orderBy As ListAs 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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice