Android Tutorial MySQL data to webview using PHP

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!
 

Attachments

Last edited:

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

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

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.

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

WebViewScreen.png
 

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

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

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.

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

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.

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.
 
Last edited:
Top