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

Discussion in 'B4J Tutorials' started by Mashiane, Apr 8, 2019.

Tags:
  1. Mashiane

    Mashiane Expert Licensed User

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

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

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

    Attached Files:

    Last edited: Apr 8, 2019
    joulongleu and José J. Aguilar like this.
  2. Mashiane

    Mashiane Expert Licensed User

    With getll,

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

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

    Code:
    'build the php to execute
    Sub BuildPHP(pQuery As Map, phpFile As StringAs 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.

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

    Code:
    #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
     
    Last edited: Apr 8, 2019
    joulongleu likes this.
  3. Mashiane

    Mashiane Expert Licensed User

    The resulting output...

    mysqltable.png

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

    Code:
    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!
     
    Last edited: Apr 8, 2019
    joulongleu likes this.
  4. joulongleu

    joulongleu Active Member

    :)Quite helpful, look forward to Part 3
     
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