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

Discussion in 'Chit Chat' started by KMatle, Feb 1, 2015.

  1. KMatle

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

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

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

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

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

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

    Code:
    phpResult = myWebClient.DownloadString(TextBox1.Text & "?Action=GetA")
    is like

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

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

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

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

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

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

    Attached Files:

    inakigarm likes this.
  2. imbault

    imbault Well-Known Member Licensed 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?
     
  3. KMatle

    KMatle Expert Licensed 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)?
     
    Peter Simpson likes this.
  4. imbault

    imbault Well-Known Member Licensed 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?
     
    KMatle likes this.
  5. KMatle

    KMatle Expert Licensed User

    Of course :) Which requirements are needed to run it on a hosted server? PhP is supportet but often to run other components you have to upgrade (= $$$).
     
  6. imbault

    imbault Well-Known Member Licensed User

    Requirement is a IIS internet server.
    All the Webservices are based on SOAP protocols
     
  7. KMatle

    KMatle Expert Licensed 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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice