Example: VS 2013 calling php (MySQL) similar to httputils

KMatle

Expert
Licensed User
Longtime User
This example can easily done by using B4J. Great benefit of B4J is to use almost the same code as you use in you B4A app.

However, you have created a great app calling php scripts on a server to get & store data from/in a MySql (or other) database. Now you need a Visual Studio VBA for some desktop users (f.e. because the company you develop for only accepts VS applications).

For my example I use one of my tables ("Artikel"):

AID (INT)
Agruppe (CHAR)
AName (CHAR)
APreis (DECIMAL)

The Php code loooks like (MySql is running on the same machine so it's local host 127.0.0.1)

B4X:
<?php

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

$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 "GetA":
        $q = mysql_query("SELECT aid, agruppe, aname, apreis FROM artikel order by aname");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
        break;
   

default:
        print json_encode ("Error: Function not defined!");
}

In B4A I call the php with

B4X:
Dim GetA As HttpJob
    GetA.Initialize("GetA", Me)
    GetA.Download2("http://192.168.178.21/order/order.php", _
              Array As String("Action", "GetA"))

In Job.Done

B4X:
 ListofArtikel = parser.NextArray
                       If ListofArtikel.Size > 0 Then
                          artikelLV.Clear
                           For i = 0 To ListofArtikel.Size - 1
                            Dim SingleArtikel As Map
                            SingleArtikel = ListofArtikel.Get(i)
                            artikelLV.AddSingleLine(SingleArtikel.Get("aid")&":"& SingleArtikel.Get("aname") & ":" & SingleArtikel.Get("agruppe") & ":" & SingleArtikel.Get("apreis"))
                           Next
                       End If

No we do it in Visual Studio:

Open a new "Windows Forms" Project.

Under "Project->Add Reference" and add "System.Web.Extensions" (for JSON)

Add a module "Project -> add module" for our global definitions

In that add the following:

B4X:
Module Module1
    Public Class Artikel
        Public Property aid As String
        Public Property agruppe As String
        Public Property aname As String
        Public Property apreis As Decimal
        Public Property anzverk As Integer
    End Class

    Public x As Integer
    Public PHPResp As Array
    Public arrsize As Integer

    Public Parm As String
End Module

Using a Class is very simple and is the equivalent to the table structure. The field "anzverk" is only used for statistics in my app and it is not used in the example.

Add a button to Form1 and check if "System.Web.Script.Serialization" is imported:

B4X:
Imports System.Web.Script.Serialization

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim myWebClient As System.Net.WebClient
        myWebClient = New System.Net.WebClient()
        myWebClient.Encoding = System.Text.Encoding.UTF8
        Dim phpResult As String
        phpResult = myWebClient.DownloadString(TextBox1.Text & "?Action=GetA")
        TextBox2.Text = phpResult

        Dim json As New JavaScriptSerializer
        PHPResp = json.Deserialize(Of Artikel())(phpResult)

        x = 0
        arrsize = PHPResp.GetUpperBound(0) + 1 

            TextBox3.Text = PHPResp(x).aid
            TextBox4.Text = PHPResp(x).agruppe
            TextBox5.Text = PHPResp(x).aname
            TextBox6.Text = PHPResp(x).apreis

        MsgBox("Items loaded: " & arrsize, MsgBoxStyle.Information, "Download complete")

    End Sub

Compare B4A to VS:

B4X:
phpResult = myWebClient.DownloadString(TextBox1.Text & "?Action=GetA")

is like

B4X:
GetA.Download2("http://192.168.178.21/order/order.php", _
              Array As String("Action", "GetA"))

In Textbox1.text I've put "http://192.168.178.21/order/order.php"

In phpresult we will get back the data sent from the php script

B4X:
$q = mysql_query("SELECT aid, agruppe, aname, apreis FROM artikel order by aname");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);

which is a JSON string which will be deserialized with

B4X:
 Dim json As New JavaScriptSerializer
            PHPResp = json.Deserialize(Of Artikel())(phpResult)

The Class "Artikel" is like a map which will be used in an array called PHPResp so we can address it with an index like

B4X:
TextBox3.Text = PHPResp(x).aid
            TextBox4.Text = PHPResp(x).agruppe
            TextBox5.Text = PHPResp(x).aname
            TextBox6.Text = PHPResp(x).apreis

Here I put the data to some Textboxes (=Edittext's) and that's it

B4X:
PHPResp.GetUpperBound(0)

is to check how many elements are in our array (starts with 0). If there are 2 elements, it is 0 and 1 (so add 1 if you want to display it)

Variables (f.e. Insert/update a new row) are used as in httputils:

B4X:
 Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
            Dim myWebClient As System.Net.WebClient
            myWebClient = New System.Net.WebClient()
            myWebClient.Encoding = System.Text.Encoding.UTF8
            Dim phpResult As String
            Parm = "?Action=NewA&agruppe=" & TextBox4.Text & "&aname=" & TextBox5.Text & "&apreis="               & TextBox6.Text
            phpResult = myWebClient.DownloadString(TextBox1.Text & Parm)
            TextBox2.Text = phpResult
    End Sub


In Textbox2.Text I put the php raw response for test reasons

Attached is the complete project for VS 2013 (Express). It has some more features like try&catch. And yes, the code could be better. It is good to show how it works. Have fun!
 

Attachments

  • Order.zip
    18 KB · Views: 217

imbault

Well-Known Member
Licensed User
Longtime User
In your example, you use PHP, to send info to your mobile app, and a windows app.

For communicate between MS SQL to Android, I simply create Windows web services, one which read SQL (using generally a view) , then another one which stores data into SQL (using Stored procedures), and my B4a app, so only 2 apps.

I don't understand the benefits of your solutions?
 

KMatle

Expert
Licensed User
Longtime User
There is no benefit. There are a lot of ways to do it. I wanted to show how it works when you come from httputils calling php scripts and now want a desktop solution using Visual Studio to create a DESKTOP application (instead of B4J) doing something similar.

With "webservice" you mean a real webservice (like asp.net)?
 

imbault

Well-Known Member
Licensed User
Longtime User
Yes, a real Webservice done in VS, with very easy JSON serialisation, it's vb.net web service

Are you interested by the code?
 

imbault

Well-Known Member
Licensed User
Longtime User
Requirement is a IIS internet server.
All the Webservices are based on SOAP protocols
 

KMatle

Expert
Licensed User
Longtime User
For the ones who have an own server or an (expensive) hosted server: Perfect

For the ones who only have a cheap (but good) server with PHP+MySQl: That is the reason why I develop everything with php+MySQL. And it's thereason I wanted to have a Windows Desktop part which can call php like in b4x :D
 
Top