B4J Library [BANanoMySQL] An inline PHP class for your MySQLI CRUD functionality

Discussion in 'B4J Libraries & Classes' started by Mashiane, Jun 18, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    Hi there

    Update: BANanoMySQL1 Example

    If you would rather use an actual sqlite db, you can use BANanoSQLite.

    NB: This uses parameter based queries and these expect S,I,D,B (String,Integer,Double,Blob) field definitions in the queries.


    IMPORTANT NOTICE: UPDATED CODE ON POST #7



    I have been exploring MySQL Php for a while now for BANano and I am glad to have finally finished a simple class that anyone can use for CRUD functionality. The basis for this has been DBUtils.

    If you recall, I explored this in post about inline php for MySQL here.

    To explain this functionality, we are continuing with the tabbed dialog to login, reset password, change password, register and forgot password as explained here.

    So this post is the consolidation of those 2 sessions and then with some tweaking, having a simple class to use.

    I am using this successfully for my projects in Things Created with BANano

    To demonstrate, the CRUD functionality we will do the following:

    1. Register - INSERT (insert)
    2. Login - READ (selectwhere)
    3. Forgot Password - READ (selectwhere)
    4. Reset Password - READ + UPDATE (selectwhere + updatewhere)
    5. Change Password - READ + UPDATE (selectwhere + updatewhere)
    6. De-Active Profile - DELETE (deletewhere)

    So how does the BANanoMySQL class work?

    The class itself helps one to generate sql queries that are later passed to CallInlinePhp(Wait). I have grown to like the ones with Wait because it makes reading the code much better.

    The BANanoMySQL class is used mainly to build up the JSON content that the BANanoMySQL php function receives to be able to run and return a result. The generated JSON result, whether for a SELECT, INSERT, DELETE, UPDATE etc, is returned in this format.

    Code:
    Dim m As Map
        m.Initialize
        m.Put(
    "sql"SQL)
        m.Put(
    "args"Null)
        m.Put(
    "types"Null)
        m.Put(
    "command""createdb")
        
    Dim res As String = Map2Json(m)
    The sql - this is the actual sql command to execute, e.g. INSERT INTO .... this will include the ? marks for your arguements.

    The args - this is a list of the arguements to process in the parameter query.

    The types - this is a list of the data types for the fields being parameterized. ONLY s-tring,i-integer,d-ouble and b-blob is acceptable here i.e sidb.

    This should be in the sequence of the parameters and the BANanoMySQL class will build these for you.

    By default all fields are assumed to be strings for this. You need to specifically indicate that particular fields are integers, doubles, blobs. Integer just relates to anything numeric and not a double.

    The command - this is the command to be executed. These are 'select', 'deletewhere', 'updatewhere', 'selectwhere' and 'insert' . Any other commands are executed and a result of 'OK' or the mysql error response. These commands are generated by BANanoMySQL class depending on the operation you need to execute for the various methods.

    BANanoMySQL Response

    The response for the BANanoMySQL php is always a JSON string with the following keys 'response' and 'data'. When things are ok, the response will always be 'OK' and the 'data' will return the contents of the sql command response, whether affectedrecords / actual list of records.

    Obvious Assumptions: You have a UX defined for your app and need inline php MySQL database functionality. This means that I will not be dwelling much on the UX here but just the functionality for CRUD for inline php. I am using my own developed UOENow library for this and will make the 'free' components version available soon. The professional version of Creative Tim's Now UI Kit Pro is not distributable for these kinds of things.
     

    Attached Files:

    Last edited: Aug 13, 2019
    omo, Erel, joulongleu and 2 others like this.
  2. Mashiane

    Mashiane Expert Licensed User

    Reproduction:

    I am using and testing this on XAMPP with MySQL, so obviously, one needs to create a DB..

    STEP 1

    Code:
    'SQL Commands to run to create the strcuture

    '***DATABASE
    'CREATE DATABASE IF NOT EXISTS `bananomysqldemo` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"

    '****USERS
    'CREATE TABLE IF NOT EXISTS `users` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    '`ssn` VARCHAR(20), `firstname` VARCHAR(50), `lastname` VARCHAR(50),
    '`email` VARCHAR(50), `telephone` VARCHAR(20), `password` VARCHAR(20),
    '`applyfor` VARCHAR(20))
    STEP 2

    Ensure that you update your mysql connection settings in the BANanoMySQL php function inside the BANanoMySQL class.

    Code:
    function BANanoMySQL($data) {
        //define these so that they cannot be changed
        DEFINE (
    'DB_HOST', 'localhost');
        DEFINE ('DB_USER', 'root');
        DEFINE ('DB_PASSWORD', '');
        DEFINE ('DB_NAME', 'bananomysqldemo');
    Under normal circumstances, on XAMPP, the root password for mysql is blank, so for production ensure you have the right parameters. All the code inside

    #If Php & #End If are written to a php file on the server you build your project to.

    STEP 3

    Update the BANano initialization settings for php to your own.

    Code:
    BANano.PHP_NAME = "bananomysqldemo.php"
        
    'for release use actual path of website and not localhost
        BANano.PHPHost = "http://localhost/" & AppName & "/"
        BANano.PHPAddHeader(
    "Access-Control-Allow-Origin: *")
     
  3. Mashiane

    Mashiane Expert Licensed User

    INSERTS

    Assumptions: You are reading html form input controls and and then injecting this record to the db.

    In our case, we have put the elements inside a form and gave the form a name. So we need to read each 'name' property of the input elements and use BANano's .GetValue to create this map. So basically, inserts expects a map that contains the field names as keys and the values per field to insert.

    Remember though, this will be created to be a parameter based query, so any field data type should be indicated as such.

    Here we are getting the form contents and saving them as a map.
    Code:
    Dim data As Map = App.Form2Map("form_register")
    Then when inserting the record to the db, one executes

    Code:
    mysql.Initialize
    'for each field name indicate its data type by... everything else is treated as string.
    'mysql.AddIntegers(Array("id"))
    'mysql.AddBlobs(Array("myblob"))
    'mysql.AddDoubles(Array("mydoubles"))

        
    Dim sqldata As String = mysql.Insert("users",data)
        
    Dim regResult As String = BANano.CallInlinePHPWait("BANanoMySQL",CreateMap("data":sqldata))
        
    Dim resp As Map = App.json2map(regResult)
        
    Dim strresp As String = resp.Get("response")
        
    If strresp = "OK" Then
            
    'create the profile for the student
            App.resume("#form_register")
            BANano.CallSub(pgindex,
    "HideSignIn"Null)
            App.SweetModalSuccess(
    "Registration",$"Welcome to the BANanoMySQL family ${fName}. You can now Log into the Online Portal using your email and password!"$)
            
    Dim reg As Map = App.Form2Map("form_register")
            
    'clear all input controls per form
            App.ClearValuesMap(reg)
            
    'ready
            'set us on the login tab
            App.ShowTab("login")
        
    Else
            App.resume(
    "#form_register")
            App.ToastError(
    "We experienced an error whilst processing your registration, please try again later!")
        
    End If
    The mysql.Insert method builds up the sql command, the args, the command and the types as indicated on post 1 and returns this as a JSON string for the php function. We pass this JSON string as a map with "data" as the key.

    To regress on our example, as we are registering users, we need to ensure that there are no duplicates, so we run a process of checking 1. the email, 2. the cellphone number and 3. the ssn # for existence before registration. If there is a match for the current registering user on any of those 3 the registration process is not continued. Thus just there we run 'selectwhere' statements.
     
    joulongleu and Alexander Stolte like this.
  4. Mashiane

    Mashiane Expert Licensed User

    READS

    Once a user is registered, he / she can to log into the portal. We use the email and password to check if the user is registered and if the entered email address and password dont match what is in the database, the login process cannot continue. Note that the passwords in this case are not hashed/protected. That is rather a risk and one should salt such data before storage.

    So again, we read the form contents entered in the input elements of the login form...

    Code:
    Dim data As Map = App.Form2Map("form_login")
    Then we run the inline php for the select using the email address and password.

    Code:
    mysql.Initialize
        App.Pause(
    "#form_login")
    Dim udata As Map = App.MapRemovePrefix(data, "login_")
        
    Dim semail As String = udata.Get("email")
        
    Dim spassword As String = udata.Get("password")
        
    Dim sqldata As String = mysql.SelectWhere("users"Array("*"), CreateMap("email":semail,"password":spassword), Array("id"))
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL",CreateMap("data":sqldata))
        
    Dim resp As Map = App.json2map(res)
        
    Dim strresp As String = resp.Get("response")
        
    Dim recs As List = resp.Get("data")
        
    If strresp = "OK" Then
            
    If recs.Size = 0 Then
                
    ' not found
                App.resume("#form_login")
                App.ToastError(
    "The login credentials you have provided could not be verified, please ensure you use a correct email address and password!")
                
    Return
            
    Else
                
    ' found
                App.Resume("#form_login")
                BANano.CallSub(pgindex,
    "HideSignIn",Null)
                
    Dim userRec As Map = recs.Get(0)
                fName = userRec.Get(
    "firstname")
                BANano.SetSessionStorage(
    "profile", userRec)
                
    'applied for
                App.SweetModalSuccess("Login",$"Welcome back ${fName}, you can now continue using our portal!"$)
            
    End If
        
    Else
            App.Resume(
    "#form_login")
            App.ToastError(
    "We experienced an error whilst processing your login request, please try again later!")
            
    Return
        
    End If
    If there is a match, then one can take the user to another screen. In our case, we just show a welcome sweet modal and on error indicate a toasterror.

    The returned data is JSON with 2 keys, 'response' and 'data', so to use it as a map to get those keys we need to convert it to a list with .Json2Map.
     
    joulongleu likes this.
  5. Mashiane

    Mashiane Expert Licensed User

    UPDATES

    Updates in this example happen when a user does the following:

    1. Reset the password - a system random password generator fires and generates a password for the user.
    2. Change password - using the provided email address and old password, a match is found, and then the new password is updated with the new specified password provided by the end user.

    We read the form contents again...
    Code:
    Dim data As Map = App.Form2Map("form_changepassword")
    We do some validations...

    Code:
    'we need to read the user details using the email address first
        Dim udata As Map = App.MapRemovePrefix(data,"change_")
        
    'the new password and confirm password should match
        Dim snewpassword As String = udata.Get("newpassword")
        
    Dim sconfirm As String = udata.Get("confirmpassword")
        
    Dim semail As String = udata.Get("email")
        
    Dim soldpassword As String = udata.Get("oldpassword")
        
    If snewpassword.EqualsIgnoreCase(soldpassword) Then
            App.ToastError(
    "The new password and the old password should not match, please change the new password!")
            App.MarkInValid(
    "change_oldpassword")
            App.MarkInValid(
    "change_newpassword")
            
    Return
        
    End If
        
    If snewpassword <> sconfirm Then
            App.ToastError(
    "The new password and its confirmation do not match, please rectify this first to continue!")
            App.MarkInValid(
    "change_newpassword")
            App.MarkInValid(
    "change_confirmpassword")
            
    Return
        
    End If
    When everything else is processed as needed, we run the update code..

    Code:
    Dim nrec As Map = CreateMap("password":snewpassword)
            
    ' generate sql json detail
            mysql.Initialize
            
    Dim sqldata As String = mysql.UpdateWhere("users", nrec, CreateMap("email":semail))
            
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL",CreateMap("data":sqldata))
            
    'get affected records
            Dim resp As Map = App.json2map(res)
            
    Dim strresp As String = resp.Get("response")
            
    Dim affectedRows As Int = resp.Get("data")
            App.Resume(
    "#form_changepassword")
            
    If strresp = "OK" And affectedRows = 1 Then
                BANano.CallSub(pgindex,
    "HideSignIn"Null)
                App.SweetModalSuccess(
    "Change Password""Your password was changed successfully " & sName)
            
    Else
                App.Resume(
    "#form_changepassword")
                App.ToastError(
    "We experienced a problem whilst processing your request, please try again later!")
            
    End If
     
    joulongleu likes this.
  6. Mashiane

    Mashiane Expert Licensed User

    DELETES

    This happens when a user wants to de-activate an account. A user will enter an email address, this will be verified, once verified, a confirmation provided.

    Once the user confirms, a delete will be executed. You guessed it right, here is the code (not included in code example)

    Code:
    Sub DeleteUser(sEmail As String)
        mysql.Initialize
        
    Dim sqldata As String = mysql.DeleteWhere("users", CreateMap("email":sEmail))
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL",CreateMap("data":sqldata))
        
    'get affected records
        Dim resp As Map = App.json2map(res)
        
    Dim strresp As String = resp.Get("response")
        
    Dim affectedRows As Int = resp.Get("data")
        
    If strresp = "OK" And affectedRows = 1 Then
            App.SweetModalSuccess(
    "Account De-Activated""Your account was de-activated successfully!")
        
    Else
            App.ToastError(
    "We experienced a problem whilst processing your request, please try again later!")
        
    End If
    End Sub
    On Ok, then one can process other functionalities needed for their app.

    #HelpingOthers2Succeed!
     
    joulongleu likes this.
  7. Mashiane

    Mashiane Expert Licensed User

    UPDATE 2019-07-09

    Hi there

    Here is a simpler example that does not use UOENow but just basic html stuff to demonstrate CRUD MySQL functionality...


    BANanoMySQL2.gif

    Steps on XAMPP

    1. Ensure you have changed the connection setting on the PHP scripts so that it points to the right database. Create the database first, the script here creates a table in the named database.

    2. Ensure that your PHP settings for your BANano project are set up propertly..

    Code:
    'set php settings
        BANano.PHP_NAME = "bananomysql2.php"
        BANano.PHPHost = 
    $"http://localhost/${AppName}/"$
        BANano.PHPAddHeader(
    "Access-Control-Allow-Origin: *")
    3. In BANano_Ready we are creating the table...

    Code:
    ' HERE STARTS YOUR APP
    Sub BANano_Ready()
        
    Dim mysql As BANanoMySQL
        mysql.Initialize
        
    'create items table with the structure
        Dim els As Map = CreateMap()
        els.Put(
    "id", mysql.DB_VARCHAR_20)
        els.Put(
    "jsoncontent", mysql.DB_STRING)
        els.Put(
    "tabindex", mysql.DB_INT)
        els.put(
    "parentid", mysql.DB_STRING)
        
    Dim sql As String = mysql.CreateTable("items", els, "id""")
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL", CreateMap("data"sql))
        
    Dim rs As ResultSet = mysql.GetResultSet(sql,res)
        
    Log(rs.response)
        
    Log(rs.result)
        pgIndex.Init
    End Sub
    4. CREATE / INSERT

    Code:
    Sub insert1
        ClearFirst
        
    'initialize bananosqlite
        mysql.Initialize()
        
    'create a record
        Dim rec As Map = CreateMap()
        rec.Put(
    "id""A")
        rec.Put(
    "jsoncontent""B")
        rec.Put(
    "tabindex"1)
        rec.put(
    "parentid""form")
        
    'indicate field data types
        mysql.AddStrings(Array("id"))
        mysql.AddIntegers(
    Array("tabindex"))
        
    'execute the insert
        Dim sql As String = mysql.Insert("items", rec)
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL", CreateMap("data"sql))
        
    'return the resultset
        Dim rs As ResultSet = mysql.GetResultSet(sql,res)
        
    '
        elCommand.SetText("Command: " & rs.query)
        elArgs.SetText(
    "Arguements: " & BANano.ToJson(rs.args))
        elNotif.SetText(
    "Result: " & BANano.ToJson(rs.result) & " rowid!")
    End Sub
    5. READ

    Code:
    Sub Select1
        ClearFirst
        
    'initialize bananosqlite
        mysql.Initialize()
        
    'indicate field data types
        mysql.AddStrings(Array("id"))
        
    'execute select where
        Dim sql As String = mysql.SelectWhere("items"Array("*"), CreateMap("id":"A"), Array("id"))
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL", CreateMap("data"sql))
        
    'get the result
        Dim rs As ResultSet = mysql.GetResultSet(sql,res)
        
    '
        elCommand.SetText("Command: " & rs.query)
        elArgs.SetText(
    "Arguements: " & BANano.ToJson(rs.args))
        elNotif.SetText(
    "Result: " & BANano.ToJson(rs.result))
    End Sub
    6. UPDATE

    Code:
    Sub update1
        ClearFirst
        
    'initialize bananosqlite
        mysql.Initialize()
        
    'define field types
        mysql.AddStrings(Array("id","parentid"))
        
    'generate random data
        dummy.Initialize
        
    Dim parentid As String = dummy.Rand_Company_Name
        
    'define query data & execute
        Dim sql As String = mysql.UpdateWhere("items", CreateMap("parentid":parentid), CreateMap("id":"A"))
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL", CreateMap("data"sql))
        
    'get the result
        Dim rs As ResultSet = mysql.GetResultSet(sql,res)
        
    '
        elCommand.SetText("Command: " & rs.query)
        elArgs.SetText(
    "Arguements: " & BANano.ToJson(rs.args))
        elNotif.SetText(
    "Result: " & BANano.ToJson(rs.result))
     
    End Sub
    7. DELETE

    Code:
    Sub Delete1
        ClearFirst
        
    'initialize bananosqlite
        mysql.Initialize()
        
    'define field types
        mysql.AddStrings(Array("id"))
        
    'define query data & execute
        Dim sql As String = mysql.DeleteWhere("items", CreateMap("id":"A"))
        
    Dim res As String = BANano.CallInlinePHPWait("BANanoMySQL", CreateMap("data"sql))
        
    'get the result
        Dim rs As ResultSet = mysql.GetResultSet(sql,res)
     
        elCommand.SetText(
    "Command: " & rs.query)
        elArgs.SetText(
    "Arguements: ")
        elNotif.SetText(
    "Result: " & BANano.ToJson(rs.result))
    End Sub
    Source Code Updated on Post #1
     

    Attached Files:

  8. José J. Aguilar

    José J. Aguilar Active Member Licensed User

    Offtopic: so you are adding to your db my surname “Aguilar”?

    Hum :-D
     
    Mashiane likes this.
  9. Mashiane

    Mashiane Expert Licensed User

    Hey, you just became famous!!! ;)
     
    José J. Aguilar likes this.
  10. Mashiane

    Mashiane Expert Licensed User

    Update: 2019-08-11

    This code is very similar to the BANanoSQLite code discussed here.

    In an attempt to make porting from BANanoSQL to BANanoSQLite and then to BANanoMySQL, this class has been updated for simplicity.

    1. Update your connection settings on the attached config.txt, rename is config.php and add it to the Files tab of your project.
    2. Add the attached class to your project for simple crud & other db related statements.

    In AppStart, update your PHP settings.

    Code:
    'set php settings
        BANano.PHP_NAME = "project.php"
        BANano.PHPHost = 
    $"http://localhost/project/"$
        BANano.PHPAddHeader(
    "Access-Control-Allow-Origin: *")
    Examples of statements, lets create a table

    Code:
    Dim mysql As BANanoMySQL1
        mysql.Initialize
        
    Dim nt1 As Map = CreateMap()
        nt1.Initialize
        nt1.Put(
    "id""INT")
        nt1.Put(
    "a""TEXT")
        nt1.Put(
    "b""TEXT")
        nt1.Put(
    "c""INT")
        
    Dim rsc As MySQLResultSet1 = mysql.CreateTable("test", nt1, "id""")
        rsc.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsc)))
    Let's add some records, we are keeping our own auto-increment numbers by getting the max of the id field and incrementing it.

    Code:
    mysql.ResetTypes
        
    Dim rsadd As MySQLResultSet1 = mysql.GetMax("test","id")
        rsadd.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsadd)))
        
    Dim nextid As String = mysql.GetNextID("id", rsadd.result)
        
    '
        Dim nr1 As Map = CreateMap()
        nr1.Put(
    "id", nextid)
        nr1.Put(
    "a""a")
        nr1.put(
    "b""b")
        nr1.Put(
    "c""1")
        mysql.ResetTypes
        mysql.AddIntegers(
    Array("id","c"))
        
    Dim rsnew As MySQLResultSet1 = mysql.Insert("test", nr1)
        rsnew.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsnew)))
        
    '
        'add another record
        mysql.ResetTypes
        
    Dim rsadd1 As MySQLResultSet1 = mysql.GetMax("test","id")
        rsadd1.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsadd1)))
        
    Dim nextid1 As String = mysql.GetNextID("id", rsadd1.result)
        
    '
        Dim nr2 As Map = CreateMap()
        nr2.Put(
    "id", nextid1)
        nr2.Put(
    "a""b")
        nr2.put(
    "b""c")
        nr2.Put(
    "c""2")
        mysql.ResetTypes
        mysql.AddIntegers(
    Array("id","c"))
        
    Dim rsnew1 As MySQLResultSet1 = mysql.Insert("test", nr2)
        rsnew1.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsnew1)))
        
    Log(rsnew1)
    Let's select all the records from a table, these are saved as .result variable.

    Code:
    'try and select records
        mysql.ResetTypes
        
    Dim rssel As MySQLResultSet1 = mysql.SelectAll("test"Array("*"), Array("c"))
        rssel.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rssel)))
        
    Log(rssel.result)
    Let's update an existing record in a table..

    Code:
    'do an update
        mysql.ResetTypes
        mysql.AddIntegers(
    Array("id","c"))
        
    Dim nr3 As Map = CreateMap()
        nr3.Put(
    "id""1")
        nr3.Put(
    "a""c")
        nr3.put(
    "b""d")
        nr3.Put(
    "c""0")
        
    Dim rsup As MySQLResultSet1 = mysql.UpdateWhere("test", nr3, CreateMap("id":"1"))
        rsup.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsup)))
        
    Log(rsup.result)
    Let's delete an existing record

    Code:
    mysql.ResetTypes
        mysql.AddIntegers(
    Array("id","c"))
        
    Dim rsdel As MySQLResultSet1 = mysql.DeleteWhere("test", CreateMap("c":"0"))
        rsdel.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsdel)))
        
    Log(rsdel.result)
    Let's replace an existing record. This deletes and inserts the record if its exists.

    Code:
    Dim nr4 As Map = CreateMap()
        nr4.Put(
    "id""7")
        nr4.Put(
    "a""z")
        nr4.put(
    "b""z")
        nr4.Put(
    "c""7")
        mysql.resettypes
        
    Dim rsrep As MySQLResultSet1 = mysql.InsertReplace("test", nr4)
        rsrep.result = BANano.FromJson(BANano.CallInlinePHPWait(
    "BANanoMySQL1", mysql.Build(rsrep)))
        
    Log(rsrep.result)
    That's it!
     

    Attached Files:

    joulongleu and José J. Aguilar like this.
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