B4J Tutorial [BANano] MySQL CRUD with PHP - Part 1

Ola

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

[BANAno] MySQL CRUD with PHP - Part 2

Well, the Business College website I'm creating has a portion of where the potential students need to register online. The data needs to be stored in a MySQL database. I intend to use various php files that should be sitting on a webserver so the inline php option in BANano for me is kinda out.


A couple of weeks ago, I features a thread here about mysql data to webview. This was nothing more that showing how I perform crud functionality to mysql database directly from an android application. In it I showed how one can create the db, use a php file and httputils to call the php scripts and return these as json strings to an android app.

This thread is a port of that android app, but specifically for BANano. I will not recap how to create the db here and all the setup that needs to be done, you can read from that thread. Here however we are not using BlueStacks, just xamp.

For this to work, one needs to initialize their BANano project as "BANano" so that its BANano_Ready, as of writing, anything else did not work with CallAjax. This might change in the future. #WishIKnew.

The users.php file to be used for the execution should be added on your Files tab. You can create all your php files and then add them to the files tab. These will be saved to the assets folder of your project.

My project name is AJAX and I will be calling the php scripts from the assets folder.

So I define a global variable to store the path of my scripts.

Public PhpPath As String = $"http://127.0.0.1/AJAX/assets/"$

Im just adding a few controls to demo this, so basic html5 without the make-up will do.

Sub Init()
    banano.GetElement("#body").Empty
    banano.GetElement("#body").Append($"<input id="txtusername" type="text" placeholder="User Name"></input><br><br>"$)
    banano.GetElement("#body").Append($"<input id="txtpassword" type="password" placeholder="Password"></input><br><br>"$)
    banano.GetElement("#body").Append($"<button id="btnlogin">LOGIN</button><br><br>"$)
    banano.GetElement("#body").Append($"<button id="btnloginw">LOGIN WAIT</button><br><br>"$)
    banano.GetElement("#body").Append($"<button id="btnregister">REGISTER</button><br><br>"$)

    banano.GetElement("#btnlogin").On("click", Me, "login")
    banano.GetElement("#btnloginw").On("click", Me, "loginw")
    banano.GetElement("#btnregister").On("click", Me, "register")
End Sub

 

[Mashiane]

For this to work, I have also just adjusted my code to suit my intensions, but there is similar code from my b4a project, for example...

'convert a json string to a map
Sub Json2Map(strJSON As String) As Map
    Dim json As BANanoJSONParser
    Dim Map1 As Map
    Map1.Initialize
    Map1.clear
    Try
        If strJSON.length > 0 Then
            json.Initialize(strJSON)
            Map1 = json.NextObject
        End If
        Return Map1
    Catch
        Return Map1
    End Try
End Sub

' convert a json string to a list
Sub Json2List(strValue As String) As List
    Dim lst As List
    lst.Initialize
    lst.clear
    If strValue.Length = 0 Then
        Return lst
    End If
    Try
        Dim parser As BANanoJSONParser
        parser.Initialize(strValue)
        Return parser.NextArray
    Catch
        Return lst
    End Try
End Sub

I had changed my ExecutePHP sub to suit the BANano experiment, so it looks like.... now..

Sub ExecutePHP(pQuery As Map, phpFile As String, phpTag As String)
    Dim json As String
    Dim sCommand As String = ""
    json = Map2QueryString(pQuery)
    If Len(json) = 0 Then
        sCommand = $"${PhpPath}${phpFile}"$
    Else
        sCommand = $"${PhpPath}${phpFile}?${json}"$
    End If
    'create the headers
    Dim headers As Map
    headers.Initialize
    headers.put("Content-Type", "application/json")
    headers.Put("Access-Control-Allow-Origin", "*")
    banano.CallAjax(sCommand, "GET", "json","",phpTag,False, headers)
End Sub

ExecutePHP seeks to be a universal method to call any php file I want. The same principle as per B4A project is followed here.

 

[Mashiane]

My Map2QueryString method was also adjusted to fit BANAno encoding... This we changed only 1 line of code and removed the stringutils that was referred.

'convert a map to a querystring
Sub Map2QueryString(sm As Map) As String
    ' convert a map to a querystring string
    Dim iCnt As Int
    Dim iTot As Int
    Dim sb As StringBuilder
    Dim mValue As String
    sb.Initialize
    
    ' get size of map
    iTot = sm.Size - 1
    iCnt = 0
    For Each mKey As String In sm.Keys
        mValue = sm.Getdefault(mKey,"")
        mValue = mValue.trim
        mValue = banano.EncodeURI(mValue)
        mKey = mKey.Trim
        If mKey.EndsWith("=") = False Then mKey = mKey & "="
        sb.Append(mKey).Append(mValue)
        If iCnt < iTot Then sb.Append("&")
        iCnt = iCnt + 1
    Next
    Return sb.ToString
End Sub

 

[Mashiane]

In the b4a app, we did most of the processing after we called ExecutePHP in JobDone sub, here we make use of...

Sub BANano_CallAjaxResult(Success As Boolean, UniqueID As String, Result As String)
    If Success Then
        'convert ths json result to a list
        Dim res As List = Json2List(Result)
        Select Case UniqueID
        Case "validateuser"
            If res.Size = 0 Then
                'user not validated
                banano.Msgbox("The user credentials could not be validated!")
            Else
                banano.Msgbox("Go on, show another screen...")
            End If
        Case "checkusername"
            If res.Size = 0 Then
                ' this username does not exist
                RegisterUser
            Else
                ' this username exists
                banano.Msgbox("A user with the specified username already exists!")
                End If
        Case "registeruser"
            If res.Size = 0 Then
                banano.Msgbox("User could not be registered successfully, please try again!")
            Else
                banano.Msgbox("User was successfully registered!")
            End If
        End Select
    End If
End Sub

I like the UniqueID of the ajaxcall as its like tha Job.Tag property for HTTPJob in this case. It's guiding perfectly in terms of the process flow of the application.

 

[Mashiane]

As you will note, some-where on the code I was exploring usage of CallAjaxWait i.e. LOGIN WAIT, as I would also like to see how that implementation might work. Sadly the limited knowledge on that front still poses challenges but if anyone can extend more on this would be extremely cool.

Enjoy

#HelpingOthers2Succeed

 

[Bladimir Carrillo]

Thanks @Mashiane, I was tested your sample on my host and runs perfect.

Please could you replicate this sample using Kendo UI Core - Wrapper? ... https://www.b4x.com/android/forum/threads/banano-kendo-ui-core-wrapper.103257/

Regards

 

[Mashiane]

Please could you replicate this sample using Kendo UI Core - Wrapper?

I'm sorry I have yet to explore the exiting Kendo UI Core Wrapper, as of yet I would'nt know where to start using it. Perhaps you can post a request directly in the forum of how to use the wrapper with MySQL PHP. I wish I was able to help.

 

[Declan]

Running this example on Server 2016, I continually get the following error:

Reading B4J INI in C:\Users\Administrator\AppData\Roaming\Anywhere Software\B4J to find Additional Libraries folder...
Found Additional Libraries folder: C:\B4X\Additional Libraries
Folder C:\Test\htdocs/ is write protected!
Build is cancelled...

I have removed all protection from the Folder "Test", but it persists in creating the error.

 

[alwaysbusy]

all protection from the Folder

maybe try generating it in another folder and then do a manual copy to C:\Test\htdocs?

 

[Declan]

Tried that, same error
I am attempting to connect to MySQL database

 

[alwaysbusy]

I don't know that project so you will have to wait for the OP

 

[Declan]

Any other way I can connect to MySQL?
jRDC does not work.

 

[Mashiane]

which webserver are you running?

 

[Declan]

which webserver are you running?

I have always used RDC2
Otherwise no dedicated webserver

 

[Mashiane]

For testing purposes install any of these webservers, laragon, usbwebserver or xampp