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,286
  • persons.zip
    582 bytes · Views: 3,705
Last edited:

KMatle

Expert
Licensed User
Longtime User
Here's a new version of the app. I've added a button to start 1000 (!) httpjobs (one after another) to test your database and the Apache server. Install it on several devices to create a lot of traffic on the server.

Via Xampp Control Panel press "Apache -> Logs access.log" to see any access to the server:

B4X:
192.168.178.23 - - [26/Dec/2014:17:48:43 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:43 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:43 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:43 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:43 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"
192.168.178.23 - - [26/Dec/2014:17:48:44 +0100] "GET /persons/persons.php?action=CountPersons HTTP/1.1" 200 5 "-" "-"

In Release mode my S4 sends/receives 1000 Jobs in 50 secs. Of yourse you could send 10 jobs parallel. I prefer sending from several real devices with different ip addresses.
 

Attachments

  • 1000Jobs.zip
    9 KB · Views: 1,750

aidymp

Well-Known Member
Licensed User
Longtime User
Thanks for this! very useful! i have no clue about MySQL or PHP but using this example i managed to create a database on my host and add info to it from my own app!!
I have not changed the code, or database fields or the php (just named it trial.php) file, but looks like its pretty straight forward! This will be very useful to me as currently I 'Talk' to my server by uploading and downloading txt files! ;)

However on the android side i tried to enter text in the age column like so

B4X:
Dim InsertNewPerson As HttpJob
    InsertNewPerson.Initialize("InsertNewP", Me)
    InsertNewPerson.download2("http://" & ServerIP & "/php/trial.php", Array As String ("action", "InsertNewPerson", "TESTLINE", accountName, "age", "B4A-ANDROID"))

and get the message back saying "inserted" but nothing is inserted!

if i use the same code but with a number

B4X:
Dim InsertNewPerson As HttpJob
    InsertNewPerson.Initialize("InsertNewP", Me)
    InsertNewPerson.download2("http://" & ServerIP & "/php/trial.php", Array As String ("action", "InsertNewPerson", "TESTLINE", accountName, "age", "123456789"))

I get the message back saying "inserted" and the record IS inserted!

after messing about in my browser to test if the problem is in the database i entered

http://URL_REMOVED-FOR-SECURITY/php/trial.php?action=InsertNewPerson&name="TESTLINE"&age="B4A-ANDROID"

And the record was saved! I presume the code converts the age to a Number?? if so Where? I cant find it! (I can see it does it in job done, but that's after the actual transaction)

Sorry about these silly questions!

Thanks

Aidy
 

aidymp

Well-Known Member
Licensed User
Longtime User
What about replace Dim PersonAge As Int to Dim PersonAge As String

Yes, I noticed and changed that but like i said that isn't called until after the actual data is 'inserted'

Im a bit at a loss as I only use the sub jobdone the httpjob and downloadservice modules and the 3 lines of code above, but get the same result each time!
its not the database or the php its something in the code!

I may try and run it on another device as my Tablet often gives unexpected results.

Thanks

Aidy
 

Pilar-JLSineriz

Active Member
Licensed User
Longtime User
There are some examples of inserting blobs to a Sql table. Please browse the forum for it because this thread is about installing and using Xamp.

Hello.. Could you help me? I am working with B4A and sqlite database.. I want to put a button to "export" sqlite database to sql server.. I think that it should not very complicated but I don´t know to do it. Thanks
 

KMatle

Expert
Licensed User
Longtime User
Hello.. Could you help me? I am working with B4A and sqlite database.. I want to put a button to "export" sqlite database to sql server.. I think that it should not very complicated but I don´t know to do it. Thanks

Please open a new thread
 

cyiwin

Active Member
Licensed User
Longtime User
Thanks for this! Anyone have an example of sending an Array of data? Aside from creating multiple jobs :)
 

DonManfred

Expert
Licensed User
Longtime User

le_toubib

Active Member
Licensed User
Longtime User
Very useful indeed
I have no knowledge of php nor mysql , but the tutorial is simple enough.
Now suppose i want to set the server pc to be online on the internet rather than on a local network , what changes do I need to do ?
 

DonManfred

Expert
Licensed User
Longtime User
Now suppose i want to set the server pc to be online on the internet rather than on a local network , what changes do I need to do ?

As i already wrote
You better should create a new thread, describe what exactly you want to do.
Do this for ANY Issue you have.
 

le_toubib

Active Member
Licensed User
Longtime User
You wrote to put the ip address of the machine ... Do you mean to put the ip address of the internet router ? And will I need to setup the router for that ?
 

le_toubib

Active Member
Licensed User
Longtime User
hi
i tried the local ip address of the server and it worked correctly .
but when i tried my router public ip , with port forwarding , it dindnt work , and i got the following error :
i tried the get persons button , i got this error
window.location.replace(pageName);

</script>

</head>

</body>

</html>

Error occurred on line: 112 (Main)
java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
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:668)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:334)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:244)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:132)
at anywheresoftware.b4a.BA$3.run(BA.java:334)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:155)
i also tried the "Duckdns" service , and it was setup succesfully , but here i got the same error.
 
Last edited:

KMatle

Expert
Licensed User
Longtime User
i tried the local ip address of the server and it worked correctly .
but when i tried my router public ip , with port forwarding , it dindnt work , and i got the following error :
i tried the get persons button , i got this error

You have to use the ip-address of the machine where Xampp is running on. As a Standard, your router blocks requests from the Internet. You could open it in the router's menue, but that is dangerous (attacks from outside).

Xamp must be configured to accept that, too. See the Xamp's documentation. Xamp itself is good for Intranets (like your home or Company Network) but not ok for a real internet server. If you Need one, get an Apache Installation (some deep knowledge is required to get the required safety, otherwise your Server will be hacked in a Minute).

This is the reason why I use a hosted Server for my apps.
 

Endang Rohana

New Member
"Error occurred on line: 112 (Main)
java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
....
...."


Can you help me ?
 

le_toubib

Active Member
Licensed User
Longtime User
This means (from my humble experience) that the server didn't send back a valid Json result .
U can set a break point inside job_done event to see what s the server response exactly :
I encountered some issues here :
1- the server sent a valid Json but it s preceded by an unwanted single character .. thus is a problem from the server .. that either u correct it from server settings ( donno how ) or u just use b4a substring to remove it.
2- for some reason the Json response was not correctly returned , so instead of being :
[{Bla},{bla},{bla}]
It s returned this way :
[{bla}{bla}{bla}]
So I used string replace function to replace }{ with },{
 

Rafal_sdc

New Member
Licensed User
I have some problem

when i want to get persons i get: An error has occured in sub:main_jobdone( java line: 483) java.lang.RuntimeExeption: JSON Array expected. Continue?
I have trial version b4a, mayme that is a problem?

and when press Count persons get: Persons in table: <br

I buy full version and still the same error :(
 
Last edited:
Top