Android Tutorial MySQL data to webview using PHP

Discussion in 'Tutorials & Examples' started by Mashiane, Mar 28, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    Hi there

    The purpose of this is to demo how one can add, update, read and display mysql database records into a webview.



    1. Setting up development environment

    For this we have used, Xampp with MySQL, BlueStacks android emulator to install and test our apk, some php with CRUD functions etc.

    1.1 B4A & BlueStacks

    To be able to use BlueStacks, ensure that after installation, you select the gear, then preferences and Enable Android Debug Bridge (ADB), by default this is turned off. Set up your account and install the B4A bridge.

    2. Developing the App

    2.1 Setting up XAMP folder for App

    With your XAMP installed, create a folder called phplogin under C:\xamp\htdocs\ and copy the users.php file on the files folder there.

    NB: The php code HAS TO start with <?php

    The username for the php is 'root' and a password has been specified, change this to be your mysql root password and save the file.

    2.2 Setting up MySQL database

    Fireup phpMyAdmin and ...

    1. Create a database called phplogin.
    2. Create a table called users
    3. Add fields id (int autoincrement), username (text), userpassword (text)

    3. The Source Code

    3.1 Login

    One is able to create a user by specifying the username and userpassword. On login, the username and userpassword are checked against existing records, if existing a msgbox indicates that one can change the screen. If not, a msgbox indicates that the user cannot be validated.

    3.2 Register

    To register, the username and userpassword are checked against existing records, if the username already exists, the user is told, if not the registration process happens.

    3.3 Update Password

    To update a userpassword, the username is used. The assumption is that the username should be unique. The new password is saved on the job.tag property and checked against the updated record in the database, if these match, the update went well.

    3.4 WebView Report

    This process selects all records from the users table, generates an HTML table and this is injected to the webview. All the fields in the users table are shown. As this is just to demo how one can do this, off course one can tweak the code to their needs.

    The code is rather simple and straight forward and can be tweaked as per ones needs. This could include using an include file on php for db settings so that the login credentials are not listed on the php file.

    The sky is no longer the limit as we have gone to the moon already. ;)

    #HelpingOthers2Succeed.

    Ta!
     

    Attached Files:

    Last edited: Apr 8, 2019
    wpalomo, mendiburen, amaxco and 5 others like this.
  2. Mashiane

    Mashiane Expert Licensed User

    Things to Note

    The information returned by the php scripts is returned as a JSON string. To be able to be used by the application, this needs to be converted to a list and then each of the records converted to map records. To do that we have two methods, Json2List and Json2Map

    Code:
    'convert a json string to a list
    Sub Json2List(jsonTxt As StringAs List
        
    Dim root As List
        root.Initialize
        
    Try
            
    Dim parser As JSONParser
            parser.Initialize(jsonTxt)
            root = parser.NextArray
            
    Return root
        
    Catch
            
    Return root
        
    End Try
    End Sub
    Code:
    'convert a json string to a map
    Sub Json2Map(jsonText As StringAs Map
        
    Dim Map1 As Map
        Map1.Initialize
        
    If Len(jsonText) = 0 Then Return Map1
        
    Dim json As JSONParser
        json.Initialize(jsonText)
        Map1 = json.NextObject
        
    Return Map1
    End Sub
     
    mendiburen and Myr0n like this.
  3. Mashiane

    Mashiane Expert Licensed User

    The Views

    The main screen has textboxes and buttons for end user to type in the username and userpassword and also for an updated database.

    LoginScreen.png

    When the login button is clicked, because the username and userpassword are compulsory, these are validated and if true the following sequence of events are executed.

    Code:
    'a user has typed in username and password to logon
    Sub btnLogin_Click
        
    If Validate = False Then Return
        
    'get the user details
        Dim strUser As String = txtUserName.Text
        
    Dim strPwd As String = txtPassword.text
        strUser = strUser.Trim
        strPwd = strPwd.trim
        
    Dim straction As String = "validateuser"
        
    'establish a connection to the php server
        'the field names should match fields in table
        Dim dbAction As Map
        dbAction.Initialize
        dbAction.Put(
    "action=", straction)
        dbAction.Put(
    "username=", strUser)
        dbAction.Put(
    "userpassword=", strPwd)
        ExecutePHP(Me,dbAction, straction,
    "users.php","")
    End Sub
    To execute the php scripts, we create a map, this map is converted into a query string using StringUtils and the download method is used to execute the HTTP calls.

    Lets validate first..

    Code:
    'validate username and password
    Sub Validate As Boolean
        
    Dim strUser As String = txtUserName.Text
        
    Dim strPwd As String = txtPassword.text
        strUser = strUser.Trim
        strPwd = strPwd.trim
        
    If strUser <> "" And strPwd <> "" Then
            
    Return True
        
    End If
        
    Msgbox("The username and passwords are required!","Error")
        
    Return False
    End Sub
    After building the map (querystring) to feed to the http call we run ExecutePHP. It is important that here we specify the "action" to be performed as the structure of our PHP used this "action" to know what needs to be done. The fieldnames passed on the map should match exactly the names of fields in your table.

    Code:
    execute a php call
    Sub ExecutePHP(frm As Object, pQuery As Map, JobName As String, phpFile As String, phpTag As String)
        
    ProgressDialogShow2("Communicating with the server...",False)
        
    Dim job As HttpJob
        
    Dim scommand As String
        
    Dim json As String
        
    'initialize a new job that will run from a class object
        job.Initialize(JobName, frm)
        job.Tag = phpTag
        json = Map2QueryString(pQuery)
        
    If Len(json) = 0 Then
            scommand = 
    $"${PhpPath}${phpFile}"$
        
    Else
            scommand = 
    $"${PhpPath}${phpFile}?${json}"$
        
    End If
        
    'execute a download on the php file
        job.Download(scommand)
    End Sub
    So ExecutePHP, shows a progressdialog indicating that we are communicating with the server. An HttpJob is created, we pass this the map we created, the jobName to be executed and the php file to execute. Earlier on the code we defined the phpPath that is used to make these calls. This is a universal method to execute the php calls Im using, I pass it any php file name I need depending on the actions I need to perform.

    As you have noted, this uses a method called Map2QueryString. I didnt want to use .download2 and opted for download and to use download, the complete query string is expected, thus the need to do this. For me this is rather consistent as I can always just define the map to execute and then just call ExecutePhp.

    Code:
    convert a map to a querystring
    Sub Map2QueryString(sm As MapAs String
        
    ' convert a map to a querystring string
        Dim SU As StringUtils
        
    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 = SU.EncodeUrl(mValue, 
    "UTF8")
            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
    Browser querystrings should always be encoded, thus the need to use StringUtils to encode each element of the querystring.

    The second screen is the webview for displaying the table...

    WebViewScreen.png
     
    mendiburen and Myr0n like this.
  4. Mashiane

    Mashiane Expert Licensed User

    The Sequence of Events

    1. A user clicks the LOGIN button.

    The entered content is validated, if ok, a map is built to be passed to the php file that will speak to the underlying database. An action called validateuser is called to be executed on the php file.

    The action is read from the passed querystring and saved into a variable

    Code:
    $action = $_GET["action"];
    The php file executes, establishes a connection to the db and then executes validateuser

    Opening the database to perform actions...

    Code:
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die(
    "Connection failed: " . $conn->connect_error);
     
    }
    Then a switch statement i.e select case...

    Code:
    switch ($action)
    {
    case "validateuser":
            // check 
    if email address and password combination exists on database
            $username = $_GET[
    "username"];
            $pwd = $_GET[
    "userpassword"];
            $
    sql = "SELECT * FROM users WHERE username = '" . $username . "' AND userpassword = '" . $pwd . "';";
            $result = $conn->query($
    sql);
            $rows = 
    array();
            
    while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            
    }
            print json_encode($rows);
            break;
    When we defined our map > querystring, we specified the username and userpassword, these are read with GET and then a select statement is executed against the database. The result is comverted into json with the encode method and returned to our Android App by the print method.

    Our android app receives the result of the call i.e. the json string via JobDone.

    Code:
    'when a job is done
    Sub JobDone (Job As HttpJob)
        
    ProgressDialogHide
        
    If Job.Success = True Then
            
    Dim jobResult As String = Job.GetString
            
    Dim jobTag As String = Job.tag
            Job.release
            
    'convert json string to list
            Dim res As List = Json2List(jobResult)
            
    Select Job.JobName
    The progress dialog is hidden, the success of the Http call is checked for success, the json string returned from the php code is read from the job and assigned to jobResult. As the result is a json string, we convert this into a list that we can process easily by executing Json2List on the jobResult.

    Code:
    Case "validateuser"
                
    If res.Size = 0 Then
                    
    Msgbox("The user profile could not be found, please register!","Profile Error")
                
    Else
                    
    Msgbox("Go on, show another screen!""Success")
                
    End If
    If the result of the execution does not return any result, the size of our res list will always be zero. That is where one can execute the code for other stuff.

    In this case if the user details using the username and password cannot be found, a msgbox indicates that the profile cannot be found. The second part where 'Go on, show another screen...', one can write code to display other content when the user is validated.

    The rest of the methods work the same way.
     
    mendiburen and Myr0n like this.
  5. Mashiane

    Mashiane Expert Licensed User

    2. A user clicks the REGISTER button.

    The assumption made to register a user is that the username should be unique. To do this some events take place, 1. We check if the username exists in the underlying database. If the username already exists, tell the user and stop the registration, otherwise register the user. After the user is registered, one can then ask the user to login using different screens, we didnt do it like that here as only 1 screen is used.

    To do this we run an action called checkusername.

    Code:
    'register a user, first check if the user is already registered
    Sub btnTest_Click
        
    If Validate = False Then Return
        
    'get the user details
        Dim strUser As String = txtUserName.Text
        
    Dim strPwd As String = txtPassword.text
        strUser = strUser.Trim
        strPwd = strPwd.trim
        
    Dim straction As String = "checkusername"
        
    Dim dbAction As Map
        dbAction.Initialize
        dbAction.Put(
    "action=", straction)
        dbAction.Put(
    "username=", strUser)
        ExecutePHP(Me,dbAction, straction, 
    "users.php","")
    End Sub
    As we expected the username, we validate the form details first, create a map of what to execute that will build our querystring and then pass this to php which will send it to mysql.

    This executes our php code...

    Code:
    case "checkusername":
            // check user name existence
            $uname = $_GET[
    "username"];
            $
    sql = "SELECT id FROM users WHERE username = '" . $uname . "';";
            $result = $conn->query($
    sql);
            $rows = 
    array();
            
    while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            
    }
            print json_encode($rows);
            break;
    This code, runs a select of any user with the matching username and returns again a json string to our android app. When JobDone receives this json string, its converted to a list, if the size of the list is 0, registration can continue as the username was not found, else, tell the user that the username is taken.

    Code:
    Select Job.JobName
            
    Case "checkusername"
                
    If res.Size = 0 Then
                    
    ' this username does not exist
                    RegisterUser
                
    Else
                    
    ' this username exists
                    Msgbox("A user with the specified username already exists!","Error")
                    
    Return       
                
    End If
    Code:
    'register the user
    Sub RegisterUser
        
    Dim strUser As String = txtUserName.Text
        
    Dim strPwd As String = txtPassword.text
        strUser = strUser.Trim
        strPwd = strPwd.trim
        
    Dim straction As String = "registeruser"
        
    Dim dbAction As Map
        dbAction.Initialize
        dbAction.Put(
    "action=", straction)
        dbAction.Put(
    "username=", strUser)
        dbAction.Put(
    "userpassword=", strPwd)
        ExecutePHP(Me,dbAction, straction, 
    "users.php","newregistration")
    End Sub
    As you might have guessed, this in essence makes two round trips to the database, one to check the username and then when NOT found, registers the user.

    On registration, the "registeruser" action is called and in our PHP file, this executes...

    Code:
    case "registeruser":
            $username = $_GET[
    "username"];
            $userpassword = $_GET[
    "userpassword"];
            $
    sql = "INSERT INTO users";
            $
    sql = $sql . " (username,userpassword) VALUES";
            $
    sql = $sql . " ('$username','$userpassword')";
            $result = $conn->query($
    sql);
            // get last inserted auto increment value
            $last_id = $conn->insert_id;
            $
    sql = "SELECT * FROM users";
            $
    sql = $sql . " WHERE id = $last_id";
            $result = $conn->query($
    sql);
            $rows = 
    array();
            
    while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
            
    }
            print json_encode($rows);
            break;
    The querystring is read and then the username and userpassword are read. We have already determined that the username does not exist, so there is no way for duplicate usernames to be created. With this information, an INSERT statement is called to add the record to the users table.

    You will remember that we created an ID field on our table which was an auto-increment field. We return this by calling the insert_id method.

    Code:
    // get last inserted auto increment value
            $last_id = $conn->insert_id;
    We use this last_id and select the just added user to the users table and return this to the user as a json string.

    Our android JobDone again does its job... If the record exist, our list record will be greater than zero, meaning that the user was successfully registered or else not.

    Code:
    Case "registeruser"
                
    If res.Size = 0 Then
                    
    Msgbox("User could not be registered successfully, please try again!","Error")
                
    Else
                    
    Msgbox("User was successfully registered!","Success")       
                
    End If
     
    mendiburen and Myr0n like this.
  6. Mashiane

    Mashiane Expert Licensed User

    So, after adding a couple of users, lets display these on a webview as a table, the crux of this topic. This is achieved by clicking the GET ALL button in our app.

    Code:
    'get all ids of the current existing records
    Sub btnGetIDS_Click
        
    Dim straction As String = "getall"
        
    Dim dbAction As Map = CreateMap("action=": straction)
        ExecutePHP(Me,dbAction, straction, 
    "users.php","")
    End Sub
    This executes a 'getall' action.

    In our PHP file, this executes...

    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 is a select statement of all existing users ordered by username and returned as a json [] string. This is again receives by JobDone, converted into a list using Json2List, a list name called res.

    Code:
    Case "getall"
                
    'show the report
                If res.Size > 0 Then
                    ShowReport(res)
                
    Else
                    
    Msgbox("Register some users first!","Register")
                
    End If
    If there are existing records, the list size will be greater than zero, if so, the ShowReport sub is called passing the list for the values/users returned.

    To show the report of all existing users, which will be dynamic at anytime, we have to build it each time. As we want to show a new layout on the current activity we have, we have to remove all views in the current activity, i.e. login screen and then show the one with the webview.

    Code:
    'generate a webview of the records
    Sub ShowReport(res As List)
        
    ProgressDialogShow2("Generating report, please be patient...",False)
        
    'remove all views
        Activity.RemoveAllViews
        
    'load the report layout
        Activity.LoadLayout("vReport")
        
    Activity.Title = "Users Report"
        
    'lets build the report
        Dim HtmlCSS As String = "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;}"
        
    Dim sb As StringBuilder
        sb.Initialize
        
    'define the body of the page
        sb.Append("<html><body>").Append(CRLF)
        
    'inject the css style
        sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
        
    'define the table and heading
        sb.Append("<table><thead><tr>").Append(CRLF)
        
    'lets get the first record from to determine the fields
        'get the records, its json
        Dim rec1 As String = res.Get(0)
        
    'convert the record to json
        Dim recm As Map = Json2Map(rec1)
        
    '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>")
        
    'inject to webview
        webReport.LoadHtml(sb.ToString)
    End Sub
    We load the vReport layout, change the title of our page and then start building the HTML table. You might have noticed that this code is very similar to the ExecuteHTML code in DBUtils. We just tweaked this to process the list which contains maps.

    We read the first record in the existing record, this will give us the keys or rather column named. We save these as we will use later. Json2Map starts becoming handy in this exercise. We create the table heading, loop through each record in the list, convert each to a map and then using the column names we saved, create each single row of the table.

    When the table is created, we inject the generated HTML to the webview using LoadHTML.

    When the webview has finished loading the html, we hide the progress dialog. Wala!

    That's all folks.

    #HelpingOthers2Succeed.
     
    Last edited: Apr 8, 2019
    mendiburen, omo and Myr0n 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