Android Tutorial Complete tutorial: B4A & Your own (local) php & MySql Server (Xampp) with b4a

Update:

Newer releases of XAMPP come with MariaDB except of MySQL. Basically the same but the php scripts have to be changed a little bit:

- mysql is changed to mysqli
- "$con" (variable that holds the connection) has to be added to every sql statement "mysqli_query($con,"SELECT * FROM persons")"


B4X:
<?php

$host = "127.0.0.1";
$user = "root";
$pw = "";
$db = "persons";

$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
mysqli_select_db($con,$db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");

$action = $_GET["action"];
switch ($action)

{
    case "CountPersons":
        $q = mysqli_query($con,"SELECT * FROM persons");
        $count = mysqli_num_rows($q);
        print json_encode($count);
    break;
 
    Case "GetPersons":
        $q = mysqli_query($con,"SELECT name, age FROM persons");
        $rows = array();
        while($r = mysqli_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
    break;
 
    case "InsertNewPerson":
        $name = $_GET["name"];
        $age = $_GET["age"];
        $q = mysqli_query($con,"INSERT INTO persons (name, age) VALUES ('$name', $age)");
        print json_encode("Inserted");
    break;
 
}

?>



Today I want to show how to

1. install Xampp, a "ready to run" installation package to have an own webserver including php and MySql
2. create a database and a table
3. create a php script to access this database & table
4. use this script via B4A app

Ready?

First download the complete package from https://www.apachefriends.org/de/index.html

Chose your OS, download and install. I use WIN 8.1 for my example but the others should work the same. XP is not supported but you can install an older version which is ok for a test environment.

During the installation there are some warnings from Xampp saying that your antivirus protection my cause some problems. On my machine I use Avira and there were no issues.

After installation start the "Xampp Control Panel":

CP.JPG


Click on "Start" at "Apache" and MySql. Both servers should start now without any problems. Take care that both can communicate via your firwall. Congrats. Now you have a working Web- and MySql Server.

Next step is to check if everything is ok.

Open your browser and type: 127.0.0.1. Chose you language and click on "Status". Here you can see which "services" are working:

status.JPG


Now let's get back to my php & MySql example and create a database and a table. Very easy.

In the Menu (orange area to the left) locate "Tools" and click on "phpMyAdmin". With this Admin tool we can do all things needed to create databases, tables, etc.

First we crate a new Database. Just click on "new" (most upper menue)

new.JPG


Name the database "persons" (lowe case to fit my example).Collation is utf8_unicode_ci (to store special characters like äöüß or éèáàô, etc.). Click on "Create".

On the left side the new database will appear now. Click on "+" next to it to expand the menu. Click on "new" (to create a table inside the database).

A new screen opens where you can define the culumns the new table contain.

Name the table "persons" (lowe case to fit my example) and enter the two culumns we need: "name" and "age"

table.JPG


Even if "age" should be an integer, please define it as varchar(30). I was lazy here and did not want to change my example :)

Click on "Save" to create the table.

Now we will create a small php script to test if php is working ok.

Create a file with this content:

B4X:
<?php

print ("Hello B4A & Xampp");

?>

It is important that you use "<?php" and not "<?" as the opening tag inside the script. It took me some time to get my scripts working.

In xampp/htdocs/ create a folder called "persons" (we need it later) and save the file "test.php" here. The xampp folder can be found directly under "C:".

The full path is: C:/xampp/htdocs/persons/text.php

Open your browser and enter: 127.0.0.1/persons/test.php

You should see our little message now. If not, check the error message (most likely the script has a typo).

Now we want to get access to our Database via php. Very simple, too. I will use my older example. The only change is the "opening tag" and the login MySql parameters.

B4X:
<?php

$host = "127.0.0.1";
$user = "root";
$pw = "";
$db = "persons";

$con = mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES 'utf8'");

$action = $_GET["action"];
switch ($action)

{
    case "CountPersons":
        $q = mysql_query("SELECT * FROM persons");
        $count = mysql_num_rows($q);
        print json_encode($count);
    break;
 
    Case "GetPersons":
        $q = mysql_query("SELECT name, age FROM persons");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
    break;
 
    case "InsertNewPerson":
        $name = $_GET["name"];
        $age = $_GET["age"];
        $q = mysql_query("INSERT INTO persons (name, age) VALUES ('$name', $age)");
        print json_encode("Inserted");
    break;
 
}

?>


$host = "127.0.0.1";
$user = "root";
$pw = "";
$db = "persons";

As you can see, Xampp comes almost with no security features switched on like there is no password set for the user. You can change that via admin tools.

Copy the script to xampp/htdocs/persons/

Uncompress the attached B4A App to a folder you like. Edit the following line:

B4X:
ServerIP="192.168.178.21" ' The ip address where you Xampp installation runs

Change it to the address the pc/laptop has where Xampp runs. I use the same machine for B4A and for Xamp (could be 2 different devices).

Enjoy & Feel free to ask...
 

Attachments

  • Example_B4A_APP_Xampp.zip
    9.7 KB · Views: 4,285
  • persons.zip
    582 bytes · Views: 3,705
Last edited:

Rafal_sdc

New Member
Licensed User
Working :)

$result = $polaczenie->query("SELECT name, age FROM persons");
$rows = array();
while($r = $result->fetch_assoc())
{
$rows[] = $r;
}
print json_encode($rows);
 

rjanelli

Member
Licensed User
Longtime User
I am using a 1and1 server to house my php persons.php and the mysql database and I continue to get the following error:

Error: Not Found

Any suggestions?


<?php
print json_encode("Inserted");
?>

I even went as far as reducing my php code to the above and then tested it with the Insert new person button. I still receive ... Error: Not Found
 

rjanelli

Member
Licensed User
Longtime User
I am using a 1and1 server to house my php persons.php and the mysql database and I continue to get the following error:

Error: Not Found

Any suggestions?


<?php
print json_encode("Inserted");
?>

I even went as far as reducing my php code to the above and then tested it with the Insert new person button. I still receive ... Error: Not Found
 

rjanelli

Member
Licensed User
Longtime User
Sorry my error. I have multiple servers and I was pointing to the wrong one. I spent two hours debugging code and it was a simple mistake. I hate that!
 

ciapw

Member
Licensed User
Hi. I have tried this.. but it didnt work. when i insert new person, it logs Back from Job:CountP, but there is nothing in my database. When i press the show person, it only logs <br>
The app stop when i want to count the person, it says json error. Where do I miss? thanks
my php code:
PHP:
<?php
$host = "127.0.0.1";
$db = "persons";
$user = "root";
$pw = "";

$con = mysqli_connect($host,$user,$pw) or die(mysql_error());
mysqli_select_db($db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");
$action = $_GET["action"];
 
switch ($action)
{
    case "CountPersons":
        $q = mysqli_query($con,"SELECT * FROM persons");
        $count = mysqli_num_rows($q);
        print json_encode($count);
    break;
 
    Case "GetPersons":
        $q = mysqli_query($con,"SELECT name, age FROM persons");
        $rows = array();
        while($r = mysqli_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
    break;
 
    case "InsertNewPerson":
        $name = $_GET["name"];
        $age = $_GET["age"];
        $q = mysqli_query($con,"INSERT INTO persons (name, age) VALUES ('$name', $age)");
        print json_encode("Inserted");
    break;
 
}
?>

my b4a code :
B4X:
#Region  Project Attributes
    #ApplicationLabel: Xampp php MySql
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: portrait
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Private PersonsListview As ListView
    Private CountPersonsButton As Button
    Private GetPersonsButton As Button
    Private NameET As EditText
    Private AgeET As EditText
    Private InsertNewPersonButton As Button
    Private Label1 As Label
    Private Label2 As Label
    
    Private ServerIP As String
    
    
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    
    
    Activity.LoadLayout("1")
    Activity.Title ="Playing with php/MySql"
    
    CountPersonsButton.SetLayout(0,0,30%x,10%y)
    GetPersonsButton.SetLayout(33%x,0,30%x,10%y)
    InsertNewPersonButton.SetLayout (66%x,0,30%x,10%y)
    
    Label1.SetLayout(0,InsertNewPersonButton.Top+InsertNewPersonButton.Height+30dip, 30%x,10%y)
    Label2.SetLayout(0,Label1.Top+Label1.Height+5dip, 30%x,10%y)
        
    NameET.SetLayout(33%x,Label1.Top, 60%x,10%y)
    AgeET.SetLayout(33%x,Label2.Top, 60%x,10%y)
        
    PersonsListview.SetLayout(0,Label2.Top+Label2.Height+5dip, 100%x,10%y)
    PersonsListview.Height=100%y-PersonsListview.Height
    
    ServerIP="192.168.0.106" ' The ip address where you Xampp installation runs
    
End Sub

Sub CountPersonsButton_Click
    Dim CountPersons As HttpJob
    CountPersons.Initialize("CountP", Me)
    CountPersons.download2("http://" & ServerIP & "/tarmistor/myscript.php", Array As String ("action", "CountPersons"))
    
End Sub

Sub GetPersonsButton_Click
    Dim GetPersons As HttpJob
    GetPersons.Initialize("GetP", Me)
    GetPersons.download2("http://" & ServerIP & "/tarmistor/myscript.php", Array As String ("action", "GetPersons"))
End Sub

Sub InsertNewPersonButton_Click
    
    If NameET.Text.Length<2 Then
       Msgbox("Name is missing or to short", "Name")
       Return
    End If
    
    If AgeET.Text = "" Then
       Msgbox("Age is missing", "Age")
       Return
    End If
            
    Dim InsertNewPerson As HttpJob
    InsertNewPerson.Initialize("InsertNewP", Me)
    InsertNewPerson.download2("http://" & ServerIP & "/tarmistor/myscript.php", Array As String ("action", "InsertNewPerson", "name", NameET.Text, "age", AgeET.Text))
End Sub


Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
        Dim res As String
        res = Job.GetString
        Log("Back from Job:" & Job.JobName )
        Log("Response from server: " & res)
                
        Dim parser As JSONParser
        parser.Initialize(res)
        
        Select Job.JobName
                        
            Case "GetP"
                Dim ListOfPersons As List
                Dim PersonName As String
                Dim PersonAge As Int
                
                ListOfPersons = parser.NextArray 'returns a list with maps
                
                PersonsListview.Clear
                
                If ListOfPersons.Size=0 Then
                    PersonsListview.AddSingleLine ("No persons found...")
                Else
                    For i = 0 To ListOfPersons.Size - 1
                        Dim Person As Map
                        Person = ListOfPersons.Get(i)
                                            
                        PersonName = Person.Get("name")
                        PersonAge = Person.Get("age")
                        
                        PersonsListview.AddSingleLine (PersonName & ", " & PersonAge)
                        
                    Next
                End If
        
            Case "CountP"
                PersonsListview.AddSingleLine ("Persons in table: " & parser.NextValue)
                
            Case "InsertNewP"
                'Do nothing
                
        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

The logs:
B4X:
Logger connected to:  asus Nexus 7
--------- beginning of main
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
Back from Job:InsertNewP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
Back from Job:CountP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
Back from Job:GetP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
Error occurred on line: 167 (Main)
java.lang.RuntimeException: JSON Array expected.
    at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
    at XamppphpMysql.example.main._jobdone(main.java:571)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:339)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA$2.run(BA.java:360)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5254)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
Back from Job:InsertNewP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
Back from Job:InsertNewP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
Back from Job:CountP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
Back from Job:GetP
Response from server: <br />
<b>Warning</b>:  mysqli_select_db() expects exactly 2 parameters, 1 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
<br />
<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>C:\xampp\htdocs\tarmistor\myscript.php</b> on line <b>8</b><br />
Error occurred on line: 112 (Main)
java.lang.RuntimeException: JSON Array expected.
    at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
    at XamppphpMysql.example.main._jobdone(main.java:571)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:339)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA$2.run(BA.java:360)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5254)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
 

OliverA

Expert
Licensed User
Longtime User
mysqli_select_db($db) or die(mysqli_error());
Should have been
B4X:
mysqli_select_db($con, $db) or die(mysqli_error($con));
java.lang.RuntimeException: JSON Array expected.
PHP's die does not return JSON, yet your code assumes that it does. Also, PHP's die does not change the HTTP return code to a non-2xx value, therefore Job.Success evaluates to true. Why did CountP not throw a JSON error? Because you're using JSONParsers.NextValue. NextValue pretty much grabs anything. NextArray expects a valid JSON array, which in your case is not returned (you just die) and your app "blows up". If you don't want to get caught off guard by invalid JSON, you may want to check first if GetString returns JSON to begin with (an array should start with [ and end with ]).
 

Schakalaka

Active Member
Licensed User
Hello.
i'm using your example for my app.
i need to pick a single value from table. The table is for user fields and have many fields like (name, surname,,address, city, energy, etc.)

i would like pick the value "energy" from user with id "2"

So this is my code:

B4X:
Public Sub pickEnergy

    Dim DoUsersEnergy As HttpJob
    DoUsersEnergy.Initialize("", Me)
    DoUsersEnergy.download2("http://192.168.1.43/persons/persons.php", Array As String ("action", "GetUserEnergy","deviceid",functions.GetDeviceId))
    Wait For (DoUsersEnergy) JobDone(DoUsersEnergy As HttpJob)
    If DoUsersEnergy.Success Then
    Dim res As String
        res = DoUsersEnergy.GetString
        Log("Back from Job:" & DoUsersEnergy.JobName )
    Log("Response from server: " & res)
                Dim l As List
                l.Initialize   
    Dim parser As JSONParser
    parser.Initialize(res)
        l.Add(parser.NextValue)
    Log(l.Size)   
Log(l.Get(0))
        energy_Label.Text =  "Total energy: " & l.Get(0) & "/50"

    
Else
        ToastMessageShow("Error: " & DoUsersEnergy.ErrorMessage, True)
End If
    DoUsersEnergy.Release
        
End Sub

on php file, this is the code:

B4X:
    case "GetUserEnergy":
    $txtdeviceid =$_GET["deviceid"];
        $q = mysqli_query($con,"SELECT my_energy, FROM tbl_users WHERE my_deviceid = '$txtdeviceid'" );
        $rows = array();
        print json_encode($q);

     break;


All the code works, but return the value FALSE inside the editext...

How can i solve this?
 

le_toubib

Active Member
Licensed User
Longtime User
Hello.
i'm using your example for my app.
i need to pick a single value from table. The table is for user fields and have many fields like (name, surname,,address, city, energy, etc.)

i would like pick the value "energy" from user with id "2"

So this is my code:

B4X:
Public Sub pickEnergy

    Dim DoUsersEnergy As HttpJob
    DoUsersEnergy.Initialize("", Me)
    DoUsersEnergy.download2("http://192.168.1.43/persons/persons.php", Array As String ("action", "GetUserEnergy","deviceid",functions.GetDeviceId))
    Wait For (DoUsersEnergy) JobDone(DoUsersEnergy As HttpJob)
    If DoUsersEnergy.Success Then
    Dim res As String
        res = DoUsersEnergy.GetString
        Log("Back from Job:" & DoUsersEnergy.JobName )
    Log("Response from server: " & res)
                Dim l As List
                l.Initialize  
    Dim parser As JSONParser
    parser.Initialize(res)
        l.Add(parser.NextValue)
    Log(l.Size)  
Log(l.Get(0))
        energy_Label.Text =  "Total energy: " & l.Get(0) & "/50"

   
Else
        ToastMessageShow("Error: " & DoUsersEnergy.ErrorMessage, True)
End If
    DoUsersEnergy.Release
       
End Sub

on php file, this is the code:

B4X:
    case "GetUserEnergy":
    $txtdeviceid =$_GET["deviceid"];
        $q = mysqli_query($con,"SELECT my_energy, FROM tbl_users WHERE my_deviceid = '$txtdeviceid'" );
        $rows = array();
        print json_encode($q);

     break;


All the code works, but return the value FALSE inside the editext...

How can i solve this?
False usually means an error in the query
The is a coma in the query ..
Try this:
$q = mysqli_query($con,"SELECT my_energy FROM tbl_users WHERE my_deviceid = '$txtdeviceid'" );
 

Schakalaka

Active Member
Licensed User
no,..
without come, it crash and return this:

B4X:
Response from server: {"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

Error occurred on line: 315 (Main) (l.Add(parser.NextValue))
java.lang.RuntimeException: Simple value expected.
    at anywheresoftware.b4a.objects.collections.JSONParser.NextValue(JSONParser.java:74)
    at com.clickand.claim.main$ResumableSub_pickEnergy.resume(main.java:704)
 

OliverA

Expert
Licensed User
Longtime User
The JSON returned is a map, not a simple value. You need to use NextObject, not NextValue. For anymore issues, please start a new thread.
 
Top