Android Tutorial MySQL data to webview using PHP

Mashiane · Mar 28, 2019

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

B4X:

'convert a json string to a list
Sub Json2List(jsonTxt As String) As 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

B4X:

'convert a json string to a map
Sub Json2Map(jsonText As String) As 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

Mashiane · Mar 28, 2019

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.

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.

B4X:

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

B4X:

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

B4X:

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.

B4X:

convert a map to a querystring
Sub Map2QueryString(sm As Map) As 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...

Mashiane · Mar 28, 2019

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

B4X:

$action = $_GET["action"];

The php file executes, establishes a connection to the db and then executes validateuser

Opening the database to perform actions...

B4X:

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

B4X:

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.

B4X:

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

B4X:

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.

Mashiane · Mar 28, 2019

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.

B4X:

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

B4X:

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.

B4X:

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

B4X:

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

B4X:

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.

B4X:

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

B4X:

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

Mashiane · Mar 28, 2019

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.

B4X:

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

B4X:

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.

B4X:

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.

B4X:

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

softmicro · Jul 8, 2020

Hi, Mashiane

Very interesting tutorial.

But I have a problem when I load users.php in Visual Studio Code and run.
It is in the line that says:
$ action = $ _GET ["action"];

[Running] php "c: \ xampp \ htdocs \ phplogin \ users.php"
PHP Notice: Undefined index: action in C: \ xampp \ htdocs \ phplogin \ users.php on line 6

Notice: Undefined index: action in C: \ xampp \ htdocs \ phplogin \ users.php on line 6

[Done] exited with code = 0 in 0.684 seconds

Can you help me solve this?
Thank you very much

afields · Jul 9, 2020

hello.
if what you've writen is what you have in your program then you must be careful with php variables!
variables in php begin always with a $ and next ( whitout any space) the variable name.
so in you problem it would be : $action and not $ action...

Android Tutorial MySQL data to webview using PHP

Attachments

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

softmicro

Member

afields

Member

Similar Threads