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

Discussion in 'Tutorials & Examples' started by KMatle, Dec 26, 2014.

  1. KMatle

    KMatle Expert Licensed User

    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")"


    Code:
    <?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":

    [​IMG]

    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:

    [​IMG]

    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)

    [​IMG]

    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"

    [​IMG]

    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:

    Code:
    <?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.

    Code:
    <?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:

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

    Attached Files:

    Last edited: Feb 28, 2016
    yiankos1, cambopad, tpakis and 18 others like this.
  2. KMatle

    KMatle Expert Licensed 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:

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

    Attached Files:

  3. BarryW

    BarryW Active Member Licensed User

    how to insert blobl using this method? Tnx . . .
     
  4. KMatle

    KMatle Expert Licensed 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.
     
  5. BarryW

    BarryW Active Member Licensed User

    Can u just post the link?
     
  6. aidymp

    aidymp Active Member Licensed 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

    Code:
    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

    Code:
    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
     
  7. f0raster0

    f0raster0 Member Licensed User

    What about replace Dim PersonAge As Int to Dim PersonAge As String
     
  8. aidymp

    aidymp Active Member Licensed User

    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
     
  9. Pilar-JLSineriz

    Pilar-JLSineriz Active Member Licensed 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
     
  10. ArminKH

    ArminKH Well-Known Member

    ANOTHER Great Tutorial
    thank u
     
    KMatle likes this.
  11. KMatle

    KMatle Expert Licensed User

    Please open a new thread
     
  12. cyiwin

    cyiwin Active Member Licensed User

    Thanks for this! Anyone have an example of sending an Array of data? Aside from creating multiple jobs :)
     
  13. DonManfred

    DonManfred Expert Licensed User

    You better should create a nw thread, describe what exactly you want to do.

    Maybe this can be of help.

    I´m not sure i understand the question:

    http://www.b4x.com/android/forum/threads/create-dynamic-jpg-using-b4a-and-php.40880/
    It is creating a set of maps, convert them to json and post that json to php. The php reads this json and do the work...
     
  14. le_toubib

    le_toubib Active Member Licensed 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 ?
     
  15. DonManfred

    DonManfred Expert Licensed User

    As i already wrote
    Do this for ANY Issue you have.
     
  16. le_toubib

    le_toubib Active Member Licensed 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 ?
     
  17. le_toubib

    le_toubib Active Member Licensed 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
    i also tried the "Duckdns" service , and it was setup succesfully , but here i got the same error.
     
    Last edited: Sep 11, 2015
  18. KMatle

    KMatle Expert Licensed User

    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.
     
  19. Endang Rohana

    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 ?
     
  20. le_toubib

    le_toubib Active Member Licensed 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 },{
     
Loading...