Share My Creation Php.Show - a php script generator for httputils usage

Php.Show is a small utility app that has been built with b4j as a php source code generator. One is able to define a table and its fields (currently text and int) and then Compile a single php file based on the table name that can be used in a web server.

NB: Latest update, 07 August 2016, ensures that the php scripts are protected from sql injections. also fixed some minor bugs.

I developed this after I needed some functionality for my b4i app to use with MySQL, however the generated php source can also be used with b4a apps with httputils. You can download the phpshow jar file from here. However the source has been included here. (Copy the phpshow.db database file to the Objects folder first)

Using the generated scripts can be done with httputils for example..

Sub ExecuteRemoteQuery(pQuery As Map, JobName As String)
    Dim job As HttpJob
    job.Initialize(JobName, Me)
    Dim json As String = Map2QueryString(pQuery)
    job.Download("http://www.myserver/anele.php?" & json)
End Sub

Sub Map2QueryString(sm As Map) As String
    ' convert a map to a querystring string
    Dim SU As StringUtils
    Dim iCnt As Int
    Dim iTot As Int
    Dim sb As StringBuilder
    Dim mValue As String
    sb.Initialize
    ' get size of map
    iTot = sm.Size - 1
    iCnt = 0
    For Each mKey As String In sm.Keys
        mValue = sm.Get(mKey)
        mValue = SU.EncodeUrl(mValue, "UTF8")
        sb.Append(mKey).Append(mValue)
        If iCnt < iTot Then sb.Append("&")
        iCnt = iCnt + 1
    Next
    Return sb.ToString
End Sub

Sub Json2List(jsonTxt As String) As List
Dim res As List
Dim parser As JSONParser
parser.Initialize(jsonTxt)
res = parser.NextArray
Return res
End Sub

Calling executeremote...

dim uID as string = "a"
Dim lareas As Map
    lareas.Initialize
    lareas.Put("action=", "get")
    lareas.Put("xx=", uID)
    ExecuteRemoteQuery(lareas, "get")

Then on job is done...

Sub JobDone (Job As HttpJob)
    If Job.Success = True Then
        Select Job.JobName
        Case "get"
            Dim lAreas As List = Json2List(Job.GetString)
            Dim aTot As Int = lAreas.Size - 1
            Dim aCnt As Int
            Dim aMap As Map
            Dim aid As String
            Dim aname As String
            Select Case aTot
            Case -1
                b4iMash.HideProgress
                Msgbox("There are no records captured in the app.","No Areas")
            Case Else
                ' loop through each record and load it to the listview
                For aCnt = 0 To aTot
                    ' get each area
                    aMap = lAreas.Get(aCnt)
                    aid = aMap.Get("field1")
                    aname = aMap.Get("field2")
                    AddTwoLinesAndBitmap2(lstAreas,aname,"", LoadBitmap(File.DirAssets, "areablue.png") , aid)
                Next
                lstAreas.ReloadAll
            End Select
        End Select
    Else
        Msgbox(Job.ErrorMessage, "Error")
    End If
    Job.Release
End Sub

For my example, I pass a map object which gets converted to a querystring and then encoded before it gets passed to the web server. On job done, I return the result as a list and loop through each item and process it. Each item is a map object.

Enjoy.

 

[Mashiane]

The generated php script is basically for CRUDV functionality

To create a record ... /anele.php?action=create&id=x...
To delete a record .../anele.php?action=delete&id=x...
To update a record .../anele.php?action=update&id=x...
To get all records ... /anele.php?action=getall
To get a single record ..../anele.pgp?action=get&id=x...

where anele.php will be the name of your php file depending on the table name you use.

 

[Mashiane]

There are some minor adjustments that I have made.

1. Added functionality to specify the MySQL connection details
2. Added functionality to indicate a field as primary, sort order and whether records can be returned using it.

The jar file is available here, same as first post. Below is an example php generated with the latest app.

<?
    // define the database connection parameters.
    $servername = "myhost";
    $dbname = "mydb";
    $username = "myuser";
    $password = "mypassword";
    // get the action to the performed from the querystring.
    $action = $_GET["action"];
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    //process the actions
    switch ($action)
    {
        case "update":
        // update the table record using the primary key
        $field1 = $_GET["field1"];
        $field2 = $_GET["field2"];
        $field3 = $_GET["field3"];
        $field4 = $_GET["field4"];
        $sql = "UPDATE anele SET ";
        $sql = $sql . "field2='$field2',";
        $sql = $sql . "field3='$field3',";
        $sql = $sql . "field4='$field4'";
        $sql = $sql . " WHERE field1 = $field1";
        $result = $conn->query($sql);
        break;
        case "create":
        // insert a new record to the table, the primary key is assumed auto increment
        $field2 = $_GET["field2"];
        $field3 = $_GET["field3"];
        $field4 = $_GET["field4"];
        $sql = "INSERT INTO anele";
        $sql = $sql . " (field2,field3,field4) VALUES";
        $sql = $sql . " ('$field2','$field3','$field4')";
        $result = $conn->query($sql);
        break;
        case "delete":
        // delete a record from the table using the primary key
        $field1 = $_GET["field1"];
        $sql = "DELETE FROM anele";
        $sql = $sql . " WHERE field1 = $field1";
        $result = $conn->query($sql);
        break;
        case "get":
        // get a single record from the table using the primary key
        $field1 = $_GET["field1"];
        $sql = "SELECT * FROM anele";
        $sql = $sql . " WHERE field1 = $field1";
        $sql = $sql . " ORDER BY field3,field4";
        $result = $conn->query($sql);
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        print json_encode($rows);
        break;
        case "getall":
        // get all records from the table with a sort order if specified
        $sql = "SELECT * FROM anele";
        $sql = $sql . " ORDER BY field3,field4";
        $result = $conn->query($sql);
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        print json_encode($rows);
        break;
        case "getfield2":
        // get records by field2 with sort order if specified
        $field2 = $_GET["field2"];
        $sql = "SELECT * FROM anele";
        $sql = $sql . " WHERE field2 = '$field2'";
        $sql = $sql . " ORDER BY field3,field4";
        $result = $conn->query($sql);
        $rows = array();
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        print json_encode($rows);
        break;
    }
    //close the connection to the database
    $conn->close();
?>

 

[Mashiane]

With the latest version, one can create their additional methods besides the CRUD methods available using SELECT, INSERT, DELETE and UPDATE methods. The "CREATE" and "UPDATE" php code now returns the inserted and updated record using last_id.

 

[tufanv]

Thank you !

 

[tufanv]

With the latest version, one can create their additional methods besides the CRUD methods available using SELECT, INSERT, DELETE and UPDATE methods. The "CREATE" and "UPDATE" php code now returns the inserted and updated record using last_id.

I just cant find the jrlmessagedialogs library to run it

 

[Mashiane]

I just cant find the jrlmessagedialogs library to run it

Hi, please see Help > About, there is a link there.

 

[Mashiane]

Note:

This project, due to it not using Parameter queries is only good for learning purposes. As SQL injections are a real thread to your PHP project, sanitizing content befire CRUD operations is recommended. One can add sanitizing code for each variable defined here before performing the CRUD actions.

Ta!