B4J Library [SithasoDaisy] Plug n Play PHP CRUD REST API (MySQL, SQLite, MSServer, PostGreSQL) for BANano

Hi

Plug n Play? Yep...

1. Add the api.php file to your assets. Get it from this GitHub repo

I spoke about this in 2022 on the BVAD3 library.

[BANanoVuetifyAD3] VFlexDialog + MySQL CRUD REST API (PHP) - using Axios

Hi there Download BVAD3 7.35+ Download Source Code Its its your first time with BVAD3, please follow instructions on Github ReadMe on how to get started By the time you finish this, you will have a working VFlexDialog based app with MySQL CRUD REST API (PHP) working. Category Listing...

www.b4x.com

2. Change the connection parameters in the api.php file.

Save the file and click 'Sync' in the IDE. You are good to go.

Example: We have a categories table in our db named bvad3products with column 'id' auto-increment.

Lets do some crud...

 

[Mashiane]

Create records

We initialize the class first! sdmysqlapi below is our sub-domain where our app is hosted on

Dim mysqlapi As SDUIMySQLAPI
    mysqlapi.Initialize(Me, "mysqlapi", "https://localhost/sdmysqlapi/assets/api.php", "categories")
    mysqlapi.ShowLog = False
    mysqlapi.SchemaAddInt(Array("id"))
    mysqlapi.SchemaAddText(Array("name","color"))
    mysqlapi.PrimaryKey = "id"
    mysqlapi.AutoIncrement = "id"

mysqlapi.PrepareRecord
    mysqlapi.SetField("name", "Anele Mbanga " & SDUIShared.DateNow)
    Dim id As String = BANano.Await(mysqlapi.CREATE)
    Log(id)

    mysqlapi.PrepareRecord
    mysqlapi.SetField("id", 23)
    mysqlapi.SetField("name", "Cars " & SDUIShared.DateNow)
    Dim id As String = BANano.Await(mysqlapi.CREATE_OR_UPDATE)
    Log(mysqlapi.RowCount)
    Log(id)

You can also pass a map variable with

mysqlapi.SetRecord(MapName)

 

[Mashiane]

Update records

mysqlapi.PrepareRecord
    mysqlapi.SetField("id", 67)
    mysqlapi.SetField("name", "Shandukani " & SDUIShared.DateNow)
    BANano.Await(mysqlapi.UPDATE)
    Log(mysqlapi.RowCount)

mysqlapi.PrepareRecord
    mysqlapi.SetField("color", "green")
    BANano.Await(mysqlapi.UPDATE_BY("name", "Shandukani " & SDUIShared.DateNow))

 

[Mashiane]

Read records

Dim found As Map = BANano.Await(mysqlapi.READ(97))
    Log(found)

    Dim found As Map = BANano.Await(mysqlapi.READ_BY("name", "Shandukani " & SDUIShared.DateNow))
    Log(found)

mysqlapi.CLEAR_WHERE
    mysqlapi.ADD_WHERE("name", mysqlapi.OPERATOR_EQ, $"Cars ${SDUIShared.DateNow}"$)
    BANano.await(mysqlapi.SELECT_WHERE)
    Log(mysqlapi.result)

mysqlapi.CLEAR_WHERE
    mysqlapi.ADD_ORDER_BY("name")
    BANano.await(mysqlapi.SELECT_ALL)
    Do While mysqlapi.NextRow
        Dim rec As Map = mysqlapi.Record
        'Log(rec)
    Loop

mysqlapi.CLEAR_WHERE
mysqlapi.ADD_ORDER_BY_DESC("name")
    BANano.await(mysqlapi.SELECT_ALL)
    Do While mysqlapi.NextRow
        Dim rec As Map = mysqlapi.Record
        Log(rec)
    Loop

 

[Mashiane]

Delete records

BANano.Await(mysqlapi.DELETE(23))
    Log(mysqlapi.RowCount)

mysqlapi.CLEAR_WHERE
    mysqlapi.ADD_WHERE("name", mysqlapi.OPERATOR_EQ, $"Anele Mbanga ${SDUIShared.DateNow}"$)
    BANano.await(mysqlapi.DELETE_WHERE)

BANano.await(mysqlapi.DELETE_ALL)

 

[Mashiane]

Interesting reading...

Add a REST API to an existing database

I've written PHP-CRUD-API (3k Github stars) that lets you publish an instant REST API for an existing database. The latest version supports a mapping to allow you to clean up the names of your tables and columns. Other important features such as authentication and authorization...

tqdev.com

PHP-CRUD-API v2 now supports SQLite

After 1 year of slowly growing PHP-CRUD-API v2 another milestone is reached. Today SQLite support, the fourth supported database engine, is (re)added to the project. It was removed with the introduction of v2. This feature is added...

tqdev.com

 

[yiankos1]

I can't describe how helpfull was this one using sithasodaisy.

Can we add an authentication in order to make our calls safe. Because anyone who has the api link (https://localhost/sithasomultiuser/assets/api.php/records/posts/1) can get data.

I see at first post that there is SDUIFetch module, where there is Basic authentication method but i am not sure how to use it...

At github info page there are five types of authentication supported (API key, API key DB, Database, Basic, JWT). Can we have them?

 

[Mashiane]

SDUIFetch

This is just a wrapper to make using BANanoFetch easier to use and nothing else.

Can we add an authentication in order to make our calls safe

I am not a PHP expert sadly and I would also love to have such things working. Perhaps if someone could figure it out it would help.

Do you have any ideas perhaps? I had shared this in the forum because it was so easy plug and play.