B4J Tutorial [SithasoDaisy5] Creating your first MySQL CRUD WebApp using REST API With API-Key (Php)

Hi Fam

Created with pleasure using
PHP CRUD API
BANano
Laragon 6
B4j
SithasoDaisy5
MySQL
DaisyUI

In this tutorial we will create our first MySQL CRUD WebApp using REST API. SithasoDaisy5 is still in BETA and this is just one of those test examples.

1. We will create and save categories to a MySQL Database.
2. We will use a SDUI5Table and SDUIPreferences for listing and add edit functionality.
3. The REST API for security purposes will use an API Key

By the end of this tutorial you will have created something like this.

Steps:

Preparing MySQL Back-End

1. Get a unique API key from this website
2. Update the env.json file with your API key. This is the key you will use with MySQL.
3. Update the env.json file with the API URL. This is the exact path in your server the REST API will access records from. In our case its, http://localhost/stock. When deploying on public server, use the actual URL of your server where you deployed your WebApp.

{
    "api-key": "jNOEqK8xvAqWWRf7B4jlw2ppOCeBoHunex4ViA1txPrG7V9DW1dG737HhseS4E5Ca3xVaUtUwbDRIOrkwEZv7SEvUQP6jClRpDESkRUnshgyngNDd2epbJWjF48xAzKp",
    "api-url": "http://localhost/stock"
}

4. Let's create a database called stock, in here we will create a table named categories. We set the auto-increment column id, a name field and a color field. For now we wont optimize the sizes of the columns, as this is a demonstration.

For more details see the Main.BANano_Ready subroutine of how this information is read for use.
5. In our project, we update the categories.php file. This is the rest api file the app will use.
From line 12765, update the stock.php file to use the credentials to connect to your database. You will also use the api key here. This way when your app passes the api key, the rest api scripts will validate it against the one specified in the php file. You can use any file name you want for the REST API scripts. We have called ours stock.php.

$config = new Config([
        'driver' => 'mysql',
        'address' => 'localhost',
        'port' => '3306',
        'username' => 'root',
        'password' => '',
        'database' => 'stock',
        'debug' => false,
        'tables' => 'all',
        'middlewares' => 'sslRedirect,apiKeyAuth,sanitation',
        'apiKeyAuth.keys' => 'jNOEqK8xvAqWWRf7B4jlw2ppOCeBoHunex4ViA1txPrG7V9DW1dG737HhseS4E5Ca3xVaUtUwbDRIOrkwEZv7SEvUQP6jClRpDESkRUnshgyngNDd2epbJWjF48xAzKp',
        'apiKeyAuth.header' => 'X-API-Key',
    ]);

You can also specify the username and password for the connection.

Preparing the UI

When our app starts, BANAno_Ready will be executed.

Sub BANano_Ready
    'get the env.json content
    Dim env As Map = BANano.Await(BANano.GetFileAsJSON("./assets/env.json?" & DateTime.Now, Null))
    'get the api key
    APIKey = env.Get("api-key")
    'get the path to the URL
    ServerURL = env.Get("api-url")
    pgIndex.Initialize
End Sub

This will read the env.json file and then execute pgIndex.Initialize, which is where our app will be built.

When pgIndex.Initialize starts, it initializes the app, load a base layout. This base layout is made up of the drawer, navbar, pageview and drawermenu. In most cases, there is no need to change anything in the base layout. This layout is COMPULSORY for all SithasoDaisy5 WebApps.

We have used Generate Members, to generate the components in code

Private appdrawer As SDUI5Drawer        'ignore
    Private appnavbar As SDUI5NavBar        'ignore
    Private pageView As SDUI5Container        'ignore
    Private drawermenu As SDUI5Menu        'ignore

The Drawer

In the drawer menu, we will create our items to access the various pages of our application. This looks like this and is accessible when clicking the hamburger menu icon in the navbar.

This is built from this code, which also creates parent child relationships.

Sub CreateDrawerMenu
    drawermenu.AddMenuItemIconText("pg-categories", "", "Categories", False)
 
    drawermenu.AddItemParent("", "settings", "", "Settings")
    drawermenu.AddItemChild("settings", "pg-users", "", "Users")
    drawermenu.AddItemChild("settings", "pg-permissions", "", "Permissions")
 
End Sub

We have prefixed each link with pg- so that we can differentiate between the pages. Each time we click an item in the drawer, we receive the item key

Private Sub drawermenu_ItemClick (item As String)
    'close the drawer on menu item click
    appdrawer.OpenDrawer(False)
    'close the swap button
    appnavbar.Hamburger.Active = False
    'to force the drawer to close you can execute
    'appdrawer.ForceClose

    Dim sprefix As String = App.UI.MvField(item, 1, "-")
    Dim ssuffix As String = App.UI.MvField(item, 2, "-")
    Select Case sprefix
    Case "pg"
        'only mark this item as active
        BANano.Await(drawermenu.SetItemActive(item))
        Select Case ssuffix
        Case "categories"
            pgCategories.Show(App)
        Case "users"
            'pgInfoBox.Show(App)
        Case "permissions"
'                pgGroupSelect.Show(App)
        End Select
    End Select 
End Sub

When each item is clicked, we get its prefix and suffix, if its pg, we show the respective page.

One of the scripts you will notice in pgIndex.Initialize is this code

App.AddDataModel("categories", "id", True)
    App.AddDataModelStrings("categories", Array("name", "color"))

What we are doing here is to tell the app the database table and schema we will use. If for example we were to use the categories table on many pages in the app, we dont have to define the schema to *expect* each time, we will just make a call to read it.

The NavBar

Our navbar, shows the hamburger, our logo and a title.

To change the logo, add a file via the Files tab. This will be stored in the assets folder of your app. In the property bag, change the Logo Image to point to the image you are using.

The title of the current navbar is showing Dashboard. This is because we navigated to the Dashboard page and changed the title.

You can change the navbar title in the property bag

Running the App for the first time

You will note that when you run the app for the first time, it shows the dashboard page. This is by design as you can have a welcome page to your app to suit your needs.

Showing another page in the app is easily made with

pgDashboard.Show(App)

PS: All pages in the app should be created a CODE modules

 

[Mashiane]

The Categories Page

This is created using a code Module. There is a BlankPage code module that you can use as a start.

We have saved this as categoriesview

This has a container, a row and 2 columns. The columns span 8x4. 8 for the table and 4 for the preference dialog.

We have set some properties for the table to make it usable etc.

When the categories page is loaded, a couple of things happen. The UI is build, this includes the table and preference dialog and also loading existing records from the DB. This happens on the Show sub routine.

Sub Show(MainApp As SDUI5App)            'ignore
    app = MainApp
    'load the layout
    BANano.LoadLayout(app.PageView, "categoriesview")
    'change the title of the pgIndex navbar
    pgIndex.UpdateTitle("categories")
    'define the table columns
    tblcategories.AddColumn("id", "#")
    tblcategories.SetColumnVisible("id", False)
    tblcategories.AddColumn("name", "Name")
    tblcategories.AddColumnBadge("color", "Color", "color")
    tblcategories.AddDesignerColums
    'build the preference dialog
    prefcategories.Clear
    prefcategories.AddPropertyTextBox("id", "Category ID", "-1", True)
    prefcategories.SetPropertyVisible("id", False)
    prefcategories.AddPropertyTextBox("name", "Name", "", True)
    prefcategories.AddPropertyColorWheel("color", "Color", "#f5375f", False, "16", 200, 20, "bottom-end")
    BANano.Await(MountCategories)
    BANano.Await(BuildPage)
End Sub

The table loaded by the layout is blank, so we need to create columns for it. We do that by calling

 'define the table columns
    tblcategories.AddColumn("id", "#")
    tblcategories.SetColumnVisible("id", False)
    tblcategories.AddColumn("name", "Name")
    tblcategories.AddColumnBadge("color", "Color", "color")
    tblcategories.AddDesignerColums

The last call, AddDesignerColumns, ensures that any Edit Delete Clone etc columns are also added. These are turned on and off via the abstract designer.

When ran, this is built to be like this..

The columns in our table are based on the columns in the database. Its preferable that these are lowercase and dont have _ or - in field names and definately no special characters. _ and - are used heavily internally to make the table work, thus having such in field names will raise some issues.

The other part is the Preference Dialog. This is where we will add details for our records.

Also the fields used here are those defined in the database table.

This is built with

'build the preference dialog
    prefcategories.Clear
    prefcategories.AddPropertyTextBox("id", "Category ID", "-1", True)
    prefcategories.SetPropertyVisible("id", False)
    prefcategories.AddPropertyTextBox("name", "Name", "", True)
    prefcategories.AddPropertyColorWheel("color", "Color", "#f5375f", False, "16", 200, 20, "bottom-end")

In the demo app, you can see how you can create a preference dialog to suit your needs on Playground > Preference Dialog Builder. You select the component you want to add to your preference dialog, set some properties and the code is generated for you.

After both the table columns are created and the property bag is created, we load the data from the database with

  BANano.Await(MountCategories)

 

[Mashiane]

MountCategories

This is a process of extracting the categories records from the database and then loading them to the table.

1. We created a database called stock with a table called categories.

1.1. We created the following columns in the database

2. We then created a table that uses the same columns in the database table AS IS.

tblcategories.AddColumn("id", "#")
    tblcategories.SetColumnVisible("id", False)
    tblcategories.AddColumn("name", "Name")
    tblcategories.AddColumnBadge("color", "Color", "color")

So MountCategories

'load categories from the database
Sub MountCategories
    'turn to add mode
    AddMode
    'select contacts from the database
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    'the api file will be stock.php
    api.ApiFile = "stock"
    'we are using an api key to make calls
    api.UseApiKey = True
    'specify the api key
    api.ApiKey = Main.APIKey
    'link this api class to the data models
    api.SetSchemaFromDataModel(app.DataModels)
    'clear any where clauses
    api.CLEAR_WHERE
    'order the details by name
    api.ADD_ORDER_BY("name")
    'execute a select all
    BANano.Await(api.SELECT_ALL)
    'load the records to the database
    tblcategories.SetItemsPaginate(api.result)
End Sub

Executes a fetch to our REST API and loads the records to the table. If we used something like PostMan, we would execute this call like this.

The SDUIMySQLREST class is just an interface to the REST API using javascript fetch api, this is called BANanoFetch. You can explore the source code to get more details about how this works inside the SithasoDaisy5.b4xlib.

 

[Mashiane]

CREATING CATEGORIES

When you create a category, a couple of things happen.

AddMode

private Sub AddMode
    'turn the mode to CREATE
    Mode = "C"
    'change the title of the pref dialog
    prefcategories.Title = "Add Category"
    'set the default properties
    prefcategories.SetPropertyBagDefaults
    'focus on the name
    prefcategories.SetPropertyFocus("name")
End Sub

You enter the category name, choose the color and when done click Save.

Clicking Save will execute a call to save the category to the database. This is executed on this call

Private Sub prefcategories_Yes_Click (e As BANanoEvent)
    e.PreventDefault
    'validate the property bag details
    Dim bValid As Boolean = BANano.Await(prefcategories.IsPropertyBagValid)
    If bValid = False Then
        BANano.Await(app.ShowSwalErrorWait("Category", "The category details are not complete!", "Ok"))
        Return
    End If
    'get the property bag fields as a map
    Dim pb As Map = BANano.Await(prefcategories.PropertyBag)
    app.pagepause
    'open the database gateway
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    api.SetSchemaFromDataModel(app.DataModels)
    api.ApiFile = "stock"
    api.UseApiKey = True
    api.ApiKey = Main.APIKey
    'pass the map record
    api.SetRecord(pb)
'    rolesapi.ShowLog = False
    Select Case Mode
    Case "C"
        Dim nid As String = BANano.Await(api.CREATE)
    Case "U"
        Dim nid As String = BANano.Await(api.UPDATE)
    End Select
    If nid <> "" Then
        app.ShowToastSuccess("Category has been created/updated.")
    Else
        app.ShowToastError("Category has NOT been created/updated.")
    End If
    BANano.Await(MountCategories)
    app.pageresume
End Sub

This used a Mode, C-reate, U-update. When you click + (add) the mode is C. This validates the required fields of the property bag and then saves the record by reading the property bag to a map and the pushing that to the DB.

The ease of this is ensuring that the DB fields, the Table fields, the property bag fields ARE THE SAME. When done saving, the records are mounted again from the DB.

 

[Mashiane]

UPDATING CATEGORIES

Updating categories, is a process of changing something about the category. It could be the name or the color in this instance. You edit a category by clicking it on the table. This activates the preference dialog for editing changing the Mode to U-pdate.

You can then change the details of the category and click Save, which will execute _yes_click as explained above.

This is the EditRow method of the table

Private Sub tblcategories_EditRow (Row As Int, item As Map)
    'set the mode to U
    Mode = "U"
    'update the title of the preference dialog
    prefcategories.Title = "Edit Category"
    'set the pref dialog defaults
    prefcategories.SetPropertyBagDefaults
    'set the property bag record
    prefcategories.PropertyBag = item
    'set focus on the name
    prefcategories.SetPropertyFocus("name")
End Sub

 

[Mashiane]

DELETING CATEGORIES

To delete a category, you click the delete button for it. This executes a confirmation dialog (it looks better than the gif).

This is the DeleteRow sub of the table

Private Sub tblcategories_DeleteRow (Row As Int, item As Map)
    'switch to add mode
    AddMode
    'get the id and name of the item to delete
    toDeleteID = item.Get("id")
    toDeleteName = item.Get("name")
    'build the confirmation message
    Dim sMsg As String = $"<h2 class="text-2xl font-bold mt-2">${toDeleteName}</h2><br>Are you sure that you want to delete this category?"$
    'wait for the user to confirm
    Dim bConfirm As Boolean = BANano.Await(app.ShowSwalConfirmWait("Confirm Delete", sMsg, "Yes", "No"))
    'the user has click No, exit
    If bConfirm = False Then Return
    'check if the roles has users asssigned to it
    app.pagepause
    'execute a delete using the id of the category
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    api.SetSchemaFromDataModel(app.DataModels)
    api.ApiFile = "stock"
    api.UseApiKey = True
    api.ApiKey = Main.APIKey
    BANano.Await(api.DELETE(toDeleteID))
    'reload the categories
    BANano.Await(MountCategories)
    app.pageresume
End Sub

 

[Mashiane]

READING CATEGORIES

Currently in this example, the only way to read records is via the mounted call. When Updating records on post #5, we assigned an already read record that was sitting in the table. Thing is such records might be updated by other users, whilst you are busy having coffee.

We can then ensure that in each update functionality, if the refresh button is not clicked to load the latest record, you have the latest record from the DB.
This can be done by actually reading it.

We will update the EditRow call to read the record from the DB and show it in the property bag.

Private Sub tblcategories_EditRow (Row As Int, item As Map)
    'set the mode to U
    Mode = "U"
    'update the title of the preference dialog
    prefcategories.Title = "Edit Category"
    'set the pref dialog defaults
    prefcategories.SetPropertyBagDefaults
    'check if the roles has users asssigned to it
    app.pagepause
    Dim sID As String = item.Get("id")
    'execute a delete using the id of the category
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    api.SetSchemaFromDataModel(app.DataModels)
    api.ApiFile = "stock"
    api.UseApiKey = True
    api.ApiKey = Main.APIKey
    Dim rec As Map = BANano.Await(api.READ(sID))
    'set the property bag record
    prefcategories.PropertyBag = rec
    'set focus on the name
    prefcategories.SetPropertyFocus("name")
    app.pageresume
End Sub

 

[Mashiane]

The Complete Source Code - Categories

'Static code module
Sub Process_Globals
    Private BANano As BANano        'ignore
    Private app As SDUI5App            'ignore
    Public name As String = "categories"
    Public title As String = "categories"
    Public icon As String = "./assets/page.svg"
    Public color As String = "#000000"
    Private prefcategories As SDUI5Preferences            'ignore       
    Private tblcategories As SDUI5Table                'ignore
    Private Mode As String = ""                            'ignore
    Private toDeleteID As String                        'id of item to delete
    Private toDeleteName As String                        'name of item to delete
End Sub

Sub Show(MainApp As SDUI5App)            'ignore
    app = MainApp
    'load the layout
    BANano.LoadLayout(app.PageView, "categoriesview")
    'change the title of the pgIndex navbar
    pgIndex.UpdateTitle("Categories")
    'define the table columns
    tblcategories.AddColumn("id", "#")
    tblcategories.SetColumnVisible("id", False)
    tblcategories.AddColumn("name", "Name")
    tblcategories.AddColumnBadge("color", "Color", "color")
    tblcategories.AddDesignerColums
    'build the preference dialog
    prefcategories.Clear
    prefcategories.AddPropertyTextBox("id", "Category ID", "-1", True)
    prefcategories.SetPropertyVisible("id", False)
    prefcategories.AddPropertyTextBox("name", "Name", "", True)
    prefcategories.AddPropertyColorWheel("color", "Color", "#f5375f", False, "16", 200, 20, "bottom-end")
    BANano.Await(MountCategories)
End Sub

'load categories from the database
Sub MountCategories
    'turn to add mode
    AddMode
    'select contacts from the database
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    'the api file will be stock.php
    api.ApiFile = "stock"
    'we are using an api key to make calls
    api.UseApiKey = True
    'specify the api key
    api.ApiKey = Main.APIKey
    'link this api class to the data models
    api.SetSchemaFromDataModel(app.DataModels)
    'clear any where clauses
    api.CLEAR_WHERE
    'order the details by name
    api.ADD_ORDER_BY("name")
    'execute a select all
    BANano.Await(api.SELECT_ALL)
    'load the records to the database
    tblcategories.SetItemsPaginate(api.result)
End Sub


private Sub AddMode
    'turn the mode to CREATE
    Mode = "C"
    'change the title of the pref dialog
    prefcategories.Title = "Add Category"
    'set the default properties
    prefcategories.SetPropertyBagDefaults
    'focus on the name
    prefcategories.SetPropertyFocus("name")
End Sub

Private Sub tblcategories_EditRow (Row As Int, item As Map)
    'set the mode to U
    Mode = "U"
    'update the title of the preference dialog
    prefcategories.Title = "Edit Category"
    'set the pref dialog defaults
    prefcategories.SetPropertyBagDefaults
    'check if the roles has users asssigned to it
    app.pagepause
    Dim sID As String = item.Get("id")
    'execute a delete using the id of the category
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    api.SetSchemaFromDataModel(app.DataModels)
    api.ApiFile = "stock"
    api.UseApiKey = True
    api.ApiKey = Main.APIKey
    Dim rec As Map = BANano.Await(api.READ(sID))
    'set the property bag record
    prefcategories.PropertyBag = rec
    'set focus on the name
    prefcategories.SetPropertyFocus("name")
    app.pageresume
End Sub

Private Sub tblcategories_DeleteRow (Row As Int, item As Map)
    'switch to add mode
    AddMode
    'get the id and name of the item to delete
    toDeleteID = item.Get("id")
    toDeleteName = item.Get("name")
    'build the confirmation message
    Dim sMsg As String = $"<h2 class="text-2xl font-bold mt-2">${toDeleteName}</h2><br>Are you sure that you want to delete this category?"$
    'wait for the user to confirm
    Dim bConfirm As Boolean = BANano.Await(app.ShowSwalConfirmWait("Confirm Delete", sMsg, "Yes", "No"))
    'the user has click No, exit
    If bConfirm = False Then Return
    'check if the roles has users asssigned to it
    app.pagepause
    'execute a delete using the id of the category
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    api.SetSchemaFromDataModel(app.DataModels)
    api.ApiFile = "stock"
    api.UseApiKey = True
    api.ApiKey = Main.APIKey
    BANano.Await(api.DELETE(toDeleteID))
    'reload the categories
    BANano.Await(MountCategories)
    app.pageresume
End Sub

Private Sub tblcategories_Add (e As BANanoEvent)
    AddMode
End Sub

Private Sub tblcategories_Refresh (e As BANanoEvent)
    e.PreventDefault
    app.PagePause
    BANano.Await(MountCategories)
    app.pageresume
End Sub

Private Sub tblcategories_Back (e As BANanoEvent)
    e.preventdefault
    'show the dashboard
    pgDashboard.Show(app)
End Sub

Private Sub prefcategories_Yes_Click (e As BANanoEvent)
    e.PreventDefault
    'validate the property bag details
    Dim bValid As Boolean = BANano.Await(prefcategories.IsPropertyBagValid)
    If bValid = False Then
        BANano.Await(app.ShowSwalErrorWait("Category", "The category details are not complete!", "Ok"))
        Return
    End If
    'get the property bag fields as a map
    Dim pb As Map = BANano.Await(prefcategories.PropertyBag)
    app.pagepause
    'open the database gateway
    Dim api As SDUIMySQLREST
    api.Initialize(Me, "categories", Main.ServerURL, "categories")
    api.SetSchemaFromDataModel(app.DataModels)
    api.ApiFile = "stock"
    api.UseApiKey = True
    api.ApiKey = Main.APIKey
    'pass the map record
    api.SetRecord(pb)
'    rolesapi.ShowLog = False
    Select Case Mode
    Case "C"
        Dim nid As String = BANano.Await(api.CREATE)
    Case "U"
        Dim nid As String = BANano.Await(api.UPDATE)
    End Select
    If nid <> "" Then
        app.ShowToastSuccess("Category has been created/updated.")
    Else
        app.ShowToastError("Category has NOT been created/updated.")
    End If
    BANano.Await(MountCategories)
    app.pageresume
End Sub

Private Sub prefcategories_No_Click (e As BANanoEvent)
    AddMode
End Sub