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

Mashiane

Expert
Licensed User
Hi there

Update: 2019-12-17 BANanoMySQL class updated, see post #11, https://www.b4x.com/android/forum/t...mysqli-crud-functionality.106858/#post-700010

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.

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

Attachments

Last edited:

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

B4X:
'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.

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

B4X:
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: *")
 

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.
B4X:
Dim data As Map = App.Form2Map("form_register")
Then when inserting the record to the db, one executes

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

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

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

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

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...
B4X:
Dim data As Map = App.Form2Map("form_changepassword")
We do some validations...

B4X:
'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..

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

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)

B4X:
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!
 

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

B4X:
'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...

B4X:
' 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

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

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

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

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

Attachments

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.

B4X:
'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

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

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

B4X:
'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..

B4X:
'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

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

B4X:
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!
 

Attachments

Mashiane

Expert
Licensed User
Updates to BANanoMySQL class

This class is the final class for MySQL and replaces any BANanoMySQL related class.

We have added some additional properties of the resultSet type. These are

B4X:
json As String - this to hold the result of the CallInlinePHPWait() call
response As String - Success/Error depending on the success/failure of the operation
error As String - the error string when a faillure happens
affectedRows As Long - the number of affected rows
Operators - the following methods now include a variable to pass the operators for your queries e.g. =,>,<,<=,>=,<>

These are:

  • DeleteWhere
  • UpdateWhere
  • UpdateAll

If you pass Null for the operators variable an equal sign (=) will be assigned in the where clause.

Examples:

Adding

B4X:
Dim dbsql As BANanoMySQL
    dbsql.Initialize
    dbsql.AddIntegers(design.Integers)
    Dim dbAdd As MySQLResultSet
    dbAdd = dbsql.Insert(tablename, nrec)
    dbAdd.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build(dbAdd))
    dbsql.JSONToResultSet(dbAdd)
Update

B4X:
dbAdd = dbsql.UpdateWhere(tablename, nrec, frec, Null)
   dbAdd.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build(dbAdd))
   dbsql.JSONToResultSet(dbAdd)
   Dim scount As String = dbAdd.affectedRows
Delete

B4X:
Dim dbsql As BANanoMySQL
    dbsql.Initialize
    dbsql.AddIntegers(Array(pk))
    Dim rslt As MySQLResultSet = dbsql.Delete(tablename, pk, pkvalue)
    rslt.json = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(rslt))
    dbsql.JSONToResultSet(rslt)
    'get count
    Dim scount As String = rslt.affectedRows
Execute

B4X:
Dim dbsql As BANanoMySQL
    dbsql.Initialize
    '
    Dim query As String = $"SELECT * from ${tablename} order by agnt_name;"$
    Dim tbl As MySQLResultSet = dbsql.Execute(query)
    tbl.json = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(tbl))
    dbsql.JSONToResultSet(tbl)
log(tbl.result)
Errors/Success

B4X:
Select case tbl.Response
Case "Success"
log(tbl.result)
log(tbl.affectedRows)
Case "Error"
log(tbl.Error")
End Select
 

Attachments

Last edited:
Top