More efficient way? (MySQL database, etc.)

[SCIS]

Hello,

Currently what I'm trying to do is display my database in a listview using this tutorial: http://www.b4x.com/forum/basic4android-getting-started-tutorials/8339-connect-android-mysql-database-tutorial.html and then displaying all of my entries with their id and their first name. This works perfectly fine, but when you press an item in the list, I want to display their first name and their surname in two seperate labels. I've managed a way to this, but it's extremely slow. What is a more efficient way on doing this?

Sub ListView1_ItemClick (Position As Int, Value As Object)
   Dim tl As TwoLines
   tl = Value
   intListviewID = tl.First
   lblCountry.Text = tl.Second
   
   ExecuteRemoteQuery("SELECT familienaam FROM users WHERE id='" & tl.First & "'", USER_VOORNAAM)
End Sub

Sub JobDone(Job As HttpJob)
   ProgressDialogHide
   If Job.Success Then
   Dim res As String
      res = Job.GetString
      Log("Response from server: " & res)
      Dim parser As JSONParser
      parser.Initialize(res)
      Select Job.JobName
         Case USERS_LIST
            Dim USERS As List
            USERS = parser.NextArray 'returns a list with maps
            For i = 0 To USERS.Size - 1
               Dim m As Map
               m = USERS.Get(i)
               'We are using a custom type named TwoLines (declared in Sub Globals).
               'It allows us to later get the two values when the user presses on an item.
               Dim tl As TwoLines
               tl.First = m.Get("id")
               tl.Second = m.Get("voornaam")
               ListView1.AddTwoLines2(tl.First, tl.Second, tl)
            Next
         Case USER_VOORNAAM 'THIS PART RIGHT HERE
            Dim l As List
            l = parser.NextArray
            If l.Size = 0 Then
               lblPopulation.Text = "N/A"
            Else
               i = 0
               Do Until i = l.Size
                  Dim m As Map
                  Dim id As Int
                  m = l.Get(i)
                  id = m.Get("id")
                  If id = intListviewID Then
                     lblPopulation.Text = m.Get("familienaam")
                  End If
                  i = i + 1
               Loop
            End If
      End Select
   Else
      ToastMessageShow("Error: " & Job.ErrorMessage, True)
   End If
   Job.Release
End Sub

Thanks in advance,
SCIS.

 

[Erel]

You should download the surname together with the id and firstname.

Now you have two options. You can store the surname in tl (assuming that id is not needed there), or you can modify TwoLines type and add another field.

 

[SCIS]

That's not the thing I want to do though. I want to get the surname of the user according to the item in the listview pressed, so I need to check first if they are in the same row in my table in my database and then display it in the label. I made it work but it shows it very slow.

Greetings,
SCIS.

 

[Erel]

Where is the query that you use to populate the list?

 

[SCIS]

#Region Module Attributes
   #FullScreen: False
   #IncludeTitle: True
   #ApplicationLabel: lol
   #VersionCode: 1
   #VersionName: 
   #SupportedOrientations: unspecified
   #CanInstallToExternalStorage: False
#End Region

#Region Module Attributes
   #FullScreen: False
   #IncludeTitle: True
   #ApplicationLabel: MySQL Example
   #VersionCode: 1
   #VersionName: 
   #SupportedOrientations: portrait
   #CanInstallToExternalStorage: False
#End Region

'Activity module
Sub Process_Globals
   Private USERS_LIST = "users_list", USER_VOORNAAM = "user_voornaam" As String
End Sub

Sub Globals
   Type TwoLines (First As String, Second As String)
   Dim lblPopulation As Label
   Dim ListView1 As ListView
   Dim lblCountry As Label
   Dim intListviewID As Int
End Sub

Sub Activity_Create(FirstTime As Boolean)
   Activity.LoadLayout("1")
   FetchCountriesList
End Sub
Sub FetchCountriesList
   ProgressDialogShow("Fetching list of countries")
   'Gets all the available countries
   ExecuteRemoteQuery("SELECT voornaam, id FROM users ORDER BY id", USERS_LIST)
End Sub


Sub ListView1_ItemClick (Position As Int, Value As Object)
   Dim tl As TwoLines
   tl = Value
   intListviewID = tl.First
   
   ExecuteRemoteQuery("SELECT familienaam FROM users WHERE id='" & tl.First & "'", USER_VOORNAAM)
End Sub



Sub ExecuteRemoteQuery(Query As String, JobName As String)
   Dim job As HttpJob
   job.Initialize(JobName, Me)
   job.PostString("http://api.cookit-app.be/index.php", Query)
   Return job 
End Sub

Sub JobDone(Job As HttpJob)
   ProgressDialogHide
   If Job.Success Then
   Dim res As String
   Dim strFamilienaam As String
      res = Job.GetString
      Log("Response from server: " & res)
      Dim parser As JSONParser
      parser.Initialize(res)
      Select Job.JobName
         Case USERS_LIST
            Dim USERS As List
            USERS = parser.NextArray 'returns a list with maps
            For i = 0 To USERS.Size - 1
               Dim m As Map
               m = USERS.Get(i)
               'We are using a custom type named TwoLines (declared in Sub Globals).
               'It allows us to later get the two values when the user presses on an item.
               Dim tl As TwoLines
               tl.First = m.Get("id")
               tl.Second = m.Get("voornaam")
               ListView1.AddTwoLines2(tl.First, tl.Second, tl)
            Next
         Case USER_VOORNAAM
            Dim l As List
            l = parser.NextArray
            If l.Size = 0 Then
               lblPopulation.Text = "N/A"
            Else
               i = 0
               Do Until i = l.Size
                  Dim m As Map
                  Dim id As Int
                  m = l.Get(i)
                  id = m.Get("id")
                  If id = intListviewID Then
                     lblCountry.Text = m.Get("voornaam")
                     lblPopulation.Text = m.Get("familienaam")
                  End If
                  i = i + 1
               Loop
            End If
      End Select
   Else
      ToastMessageShow("Error: " & Job.ErrorMessage, True)
   End If
   Job.Release
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

This is the only code I adjusted right now. I just used the Main from the example and then adjusted it to my needs, is there any better way?
Also, our database server sends this as response:

Response from server: [{"0":"1 ","id":"1 ","1":"Raimo ","voornaam":"Raimo ","2":"Huybrechts","familienaam":"Huybrechts"},{"0":"2 ","id":"2 ","1":"Jordy ","voornaam":"Jordy ","2":"Van Pelt ","familienaam":"Van Pelt "},{"0":"3 ","id":"3 ","1":"Adriaan ","voornaam":"Adriaan ","2":"Marain ","familienaam":"Marain "}]

Every time I press an item, instead of just sending me the "voornaam" and "familienaam" as response? (The response is my whole database + some things which are not even in it. Like "0":"3.) Do you know a way to fix it so that it only sends the "voornaam" and "familienaam" as response like in your example it only sends the population as response and not the whole database.

Greetings,
SCIS.

 

[Erel]

I'm not sure where these fields come from. This is strange.

However about your problem. You should change this query:

"SELECT voornaam, id FROM users ORDER BY id"

To:

"SELECT voornaam, id, familienaam  FROM users ORDER BY id"

Now you should save the family name in the TwoLines type (add a field if you need).
This will allow you to return the family name without sending an additional query.

 

[SCIS]

Now you should save the family name in the TwoLines type (add a field if you need).
This will allow you to return the family name without sending an additional query.

How are we able to add a field to the TwoLines type? As it is supposed to only give two lines and it only has First and Second as a variable you can set.

Edit: I solved the issue with the response as well. Also, I can call the "familienaam" now without having the whole database returned as response. The reason was that it was hardcoded in my script of the database to always return the whole database. I also found how to add a field, just overlooked the custom type declared. I'm just using the exact example from the MySQL tutorial, adapted to my database.

 

[Erel]

How are we able to add a field to the TwoLines type? As it is supposed to only give two lines and it only has First and Second as a variable you can set.

This is a standard custom type. You can add any field that you need.