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

Ola


Kinda excited that this is starting to form a shape. This second part of the tutorial deals with reading the users from the MySQL users table and then displaying them in a table.

The first part we just did an intro into what we intend doing and showed how to

1. CREATE a user
2. READ a user (validateuser + checkemail)

all of this using a PHP file sitting on our XAMP server and calling it using CallAjax. I also experimented with CallAjaxWait and realised my error. CallAjaxWait does not return a value. ha ha ha.

To recap, you can visit this link here...https://www.b4x.com/android/forum/threads/banano-mysql-crud-with-php-part-1.104622/

To explain this part, instead of using CallAjax I am using CallAjaxWait. To achieve this, I needed to build my querystring and then pass this to the BANano Ajax call.

First things first

I needed to update the page to have an empty table and also add a button to get all the records...

banano.GetElement("#body").Append($"<button id="btngetall">GET ALL (WAIT)</button><br><br><br>"$)
    banano.GetElement("#body").Append($"<table id="users"></table>"$)

Then execute the code to get all records from the MySQL db.

Sub getall(e As BANanoEvent)
    If banano.CheckInternetConnectionWait Then
        Dim dbAction As Map = CreateMap("action":"getall")
        'build the php
        Dim m As Map = BuildPHP(dbAction,"users.php")
        Dim headers As Map = m.Get("headers")
        Dim sCommand As String = m.Get("command")
        'execute the php
        Dim result As String = banano.CallAjaxWait(sCommand, "GET", "json","",False, headers)
        'convert the json string to a list, the list already contains map records for each user
        Dim res As List = Json2List(result)
        ShowReport(res)
    End If
End Sub

Let's take a deeper look here...

 

[Mashiane]

With getll,

We needed to link an event to the button like before...

banano.GetElement("#btngetall").On("click", Me, "getall")

We first, check if the internet connection is available with BANano.CheckInternetConnectionWait. If we have an internet connection, this returns True. I have added a BuildPHP sub that builds up our command and headers that we need to pass to CallAjaxWait as CallAjaxWait CANNOT return a value. Well it does return a promise, but that cannot be a variable that gets returned from a sub. That was by BIG mistake!

BuildPHP is just another version of ExecutePHP from our previous example, but it returns a map that we pass to our Ajax Call.

'build the php to execute
Sub BuildPHP(pQuery As Map, phpFile As String) As Map
    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", "*")
    'create map things
    Dim m As Map = CreateMap("headers":headers,"command":sCommand)
    Return m
End Sub

This returns the complete encoded URL of our end point and then the headers to pass to the AjaxCall. As noted in the code in the post #1 above, we read this map and then execute the ajax call. When the ajax call is finished, the result of our getall php script is returned as a json string. This gets converted into a b4j list and then passed to ShowReport to generate the table.

NB: The returned list in this case is already containing Object i.e. {} records for each users, thus the sub as per our B4A project where this port is from is not needed.

'generate a webview of the records
Sub ShowReport(res As List)
    'load the report layout
    'lets build the report
    Dim sb As StringBuilder
    sb.Initialize
    'define the table and heading
    sb.Append($"<table id="users"><thead><tr>"$).Append(CRLF)
    'lets get the first record from to determine the fields
    'get the records, its json
    Dim recm As Map = res.Get(0)
    'define a list to store column names
    Dim cols As List
    cols.Initialize
    For Each strKey As String In recm.Keys
        cols.Add(strKey)
    Next
    'create each column as table heading
    For i = 0 To cols.Size - 1
        sb.Append("<th>").Append(cols.get(i)).Append("</th>")
    Next
    sb.Append("</thead>")
    sb.Append("</tr>").Append(CRLF)
    Dim row As Int
    'add the column data per record
    For Each recm As Map In res
        If row Mod 2 = 0 Then
            sb.Append("<tr>")
        Else
            sb.Append("<tr class='odd'>")
        End If
        For i = 0 To cols.Size - 1
            sb.Append("<td>")
            Dim colName As String = cols.Get(i)
            Dim colValue As String = recm.GetDefault(colName,"")
            sb.Append(colValue)
            sb.Append("</td>")
        Next
        sb.Append("</tr>").Append(CRLF)
        row = row + 1
    Next
    sb.Append("</table></body></html>")
    banano.GetElement("#users").Replace(sb.ToString)
    'banano.GetElement("#users").SetStyle($"{"width": "100%", "border": "1px solid #cef", "text-align": "left"}"$)
    'SetStyle("th",CreateMap("font-weight": "bold", "background-color": "#acf",    "border-bottom": "1px solid #cef"))
    'SetStyle("td,th", CreateMap("padding": "4px 5px"))
End Sub

Sub SetStyle(elID As String, styleMap As Map)
    Dim strStyle As String = Map2Json(styleMap)
    banano.GetElement(elID).SetStyle(strStyle)
End Sub


'convert a map to a json string
Sub Map2Json(mp As Map) As String
    Dim JSON As BANanoJSONGenerator
    JSON.Initialize(mp)
    Return JSON.ToString
End Sub

Whilst going through the code, I remembers that BANano #If Css transpiler condition, thus commented out the SetStyle calls at the bottom of ShowReport in favour of that.

#if css
    table {width: 100%;border: 1px solid #cef;text-align: left; }
    th { font-weight: bold;    background-color: #acf;    border-bottom: 1px solid #cef; }
    td,th {    padding: 4px 5px; }
    .odd {background-color: #def; }
    .odd td {border-bottom: 1px solid #cef; }
    a { text-decoration:none; color: #000;}
#End If

That's all folks.

#HelpingOther2Succeed

 

[Mashiane]

The resulting output...

The resulting output, is based on this php get call..

case "getall":
        $sql = "select * from users order by username";
        $result = $conn->query($sql);
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        print json_encode($rows);
        break;

Which returns a json string as discussed above. The php file is located on the Files tab of the app and you can explore it for more details. You might want to have a separate db.config file that will be required once on this file so that your connection credentials are not on file.

Hope this was helpful!!!

Ta!

 

[joulongleu]

Quite helpful, look forward to Part 3