Showcase: Part-2 / Database Insert, DataStore with Array fields - UPDATED

[famousmag]

IMPORTANT NOTE: Correction of Price value on form_Order
After posting this “Part-2 , Database-Insert”, I got a message from @Ben pointing out that there is a possibility of hacking values (especially the price one) that can be changed because the inputs from the datastore are not to be trusted and can easily be changed by a skilled person.
THANKS @BEN FOR YOUR VALUABLE ADVICE!!

So, I apologize and in order to fix this “open door” we will just pull all of the Price Inputs of the frm_Order DIRECTLY from the database and not from the DataStore

Please scroll down to to the bottom of this post to see the changes:

Hey guys,

On the first Part, we created a Shopping Cart with the DataSore component and saw the manipulation of a Data Store containing Array Field using an alternative-temporary Data Store for the Array fields.
The Part-1 is here: Showcase: DataStore, Manipulating Array fields inside DataStore (Client_side)

Now in Part-2 we will continue from where we left off…

We have our Data Store filled with our Order’s products and their Variations, ready to be submitted.
Main DataStore “yourCart”:

{
    "$type": "datastore",
    "data": [
        {
            "$id": 7,
            "prd_id": 9,
            "prd_qnt": 1,
            "prd_prc": "23.00",
            "prd_name": "TShirt-3",
            "prod_vars": [
                {
                    "$id": 1,
                    "var_id": 3,
                    "var_name": "Color",
                    "var_val_id": "14",
                    "var_val_name": "Black",
                    "var_val_prc": 1
                }
            ]
        },
        {
            "$id": 8,
            "prd_id": 9,
            "prd_qnt": 1,
            "prd_prc": "23.00",
            "prd_name": "TShirt-3"
        },
        {
            "$id": 9,
            "prd_id": 2,
            "prd_qnt": 1,
            "prd_prc": "165.00",
            "prd_name": "Mobile-2",
            "prod_vars": [
                {
                    "$id": 1,
                    "var_id": 1,
                    "var_name": "RAM Memory",
                    "var_val_id": 41,
                    "var_val_name": "64GB",
                    "var_val_prc": 18
                },
                {
                    "$id": 2,
                    "var_id": 4,
                    "var_name": "Color",
                    "var_val_id": "16",
                    "var_val_name": "White",
                    "var_val_prc": 1
                }
            ]
        }
    ]
}

At this point we have to solve a problem…
The DataStore contains all the information/data we need but we must find a way to transform/translate their structure in a form and submit them in server to complete the desired Database Insert.

Here is our Orders table schema:

Our Insert must have this Payload:

ord_usr_id: 1
ord_date: 2023-08-07
formRepeatProducts[0][ord_product_id]: 9
formRepeatProducts[0][ord_prd_quantity]: 1
formRepeatProducts[0][ord_prd_price]: 23.00
formRepeatProducts[0][formRepeatVariations][0][ord_prd_var_Var_id]: 3
formRepeatProducts[0][formRepeatVariations][0][ord_prd_var_Val_id]: 14
formRepeatProducts[0][formRepeatVariations][0][ord_prd_var_Val_exprice]: 1.00
formRepeatProducts[1][ord_product_id]: 9
formRepeatProducts[1][ord_prd_quantity]: 1
formRepeatProducts[1][ord_prd_price]: 23.00
formRepeatProducts[2][ord_product_id]: 2
formRepeatProducts[2][ord_prd_quantity]: 1
formRepeatProducts[2][ord_prd_price]: 165.00
formRepeatProducts[2][formRepeatVariations][0][ord_prd_var_Var_id]: 1
formRepeatProducts[2][formRepeatVariations][0][ord_prd_var_Val_id]: 63
formRepeatProducts[2][formRepeatVariations][0][ord_prd_var_Val_exprice]: 18.00
formRepeatProducts[2][formRepeatVariations][1][ord_prd_var_Var_id]: 4
formRepeatProducts[2][formRepeatVariations][1][ord_prd_var_Val_id]: 16
formRepeatProducts[2][formRepeatVariations][1][ord_prd_var_Val_exprice]: 1.00

This payload needs this POST structure in our “order_add” Server Action.

So, we create one more modal(Modal_Checkout) to keep the data from our DataStore and insert a form in it once the “CheckOut” is clicked.

Here is the final layout of the Modal_Checkout:

And here is the code of the form “frm_Order” holding the data to be sumbmitted:

<form id="frm_Order" is="dmx-serverconnect-form" method="post" action="dmxConnect/api/orders/order_add.php" dmx-on:success="notifies1.success('Order successfully sent...');frm_Order.reset(true);modal_Checkout.hide();yourCart.clear()">
    <div class="row g-0">
        <div class="col-12 bg-secondary bg-opacity-50 text-white pt-3 ps-2 pe-2">
            <div class="form-group mb-3 row">
                <label for="ord_usr_id" class="col-sm-2 col-form-label text-sm-end pe-0 fw-bold">Client :</label>
                <div class="col-sm-10">
                    <input class="form-control" id="ord_usr_id" name="ord_usr_id" placeholder="Client" dmx-bind:value="srvc_ProdList.data.qr_userLoggedIn.usr_id" type="hidden">
                    <p class="rounded text-primary bg-white fw-bold mb-0 pt-1 pb-1 ps-3 pe-2 lh-lg" dmx-text="srvc_ProdList.data.qr_userLoggedIn.usr_fname+' '+srvc_ProdList.data.qr_userLoggedIn.usr_lname+' ('+srvc_ProdList.data.qr_userLoggedIn.usr_username+')'">Fancy display heading</p>

                </div>
            </div>
            <div class="form-group mb-3 row">
                <label for="ord_date" class="col-sm-2 col-form-label text-sm-end pe-0 fw-bold">Date :</label>
                <div class="col-sm-10">
                    <input type="text" class="form-control text-primary fw-bold bg-white" id="ord_date" name="ord_date" placeholder="Client" is="dmx-date-picker" dmx-bind:customdates="srvc_ProdList.data.serverTime" customdates-start="srvc_ProdList.data.serverTime" customdates-end="srvc_ProdList.data.serverTime" dmx-bind:value="srvc_ProdList.data.serverTime">
                </div>
            </div>

        </div>
    </div>

    <div class="row g-0 pt-2 fw-bold small" style="/* min-width: 760px */ /* overflow-x: auto */ white-space: nowrap;">
        <h5 class="ps-2">Your Products:</h5>
        <section class="row g-0 px-1 align-items-center py-1 border-bottom border-secondary">
            <div class="pe-1 small text-center align-self-end col-4">
                <p class="mb-0">Product</p>
            </div>
            <div class="pe-1 small text-center col-sm-1 col-2 align-self-end">
                <p class="mb-0 text-center">Quantity</p>
            </div>
            <div class="pe-1 small text-center col-2 align-self-end">
                <p class="mb-0">Price</p>
            </div>
            <div class="pe-1 small text-center col-sm-1 align-self-end col-3">
                <p class="mb-0">Amount</p>

            </div>

            <div class="bg-light d-none d-sm-block col-sm-4">
                <div class="row g-0">
                    <p class="small text-center lh-sm mb-0 border-bottom">Variations</p>
                </div>
                <div class="row g-0 align-items-center">
                    <div class="col-5 ps-1 pe-0 small text-center">
                        <p class="mb-0 lh-1">Variation</p>

                    </div>
                    <div class="col-4 ps-1 pe-0 text-center small">
                        <p class="mb-0 lh-1">Value</p>
                    </div>
                    <div class="col-3 ps-1 pe-0 text-center small">
                        <p class="mb-0 lh-1">Price</p>
                    </div>
                </div>

            </div>
        </section>
        <div id="formRepeatProducts" is="dmx-repeat" dmx-bind:repeat="yourCart.data">

            <div class="row g-0 align-items-center  border-bottom border-dark">
                <dmx-value id="prod_index" dmx-bind:value="$index"></dmx-value>
                <div class="pe-1 align-self-center col-4">
                    <input id="ord_product_id" dmx-bind:name="'formRepeatProducts['+$index+'][ord_product_id]'" type="hidden" class="form-control bg-white" readonly="true" dmx-bind:value="prd_id.toNumber()">

                    <legend dmx-text="prd_name" class="col-form-label-sm mb-0 ps-1">Product</legend>

                </div>
                <div class="pe-1 col-sm-1 col-2 text-sm-center">
                    <input id="ord_prd_quantity" dmx-bind:name="'formRepeatProducts['+$index+'][ord_prd_quantity]'" type="number" class="form-control bg-white border-0 form-control-sm text-center" readonly="true" dmx-bind:value="prd_qnt">
                </div>
                <div class="pe-1 col-2">
                    <dmx-serverconnect id="srvc_GetProductPrice" url="dmxConnect/api/products/prod_readProductPrice.php" dmx-param:prid="ord_product_id.value"></dmx-serverconnect>

                    <input id="ord_prd_price" dmx-bind:name="'formRepeatProducts['+$index+'][ord_prd_price]'" type="number" class="form-control bg-white border-0 form-control-sm text-end" readonly="true" dmx-bind:value="srvc_GetProductPrice.data.qr_GetProductPrice.prd_price">
                </div>
                <div class="pe-1 col-sm-1 text-end col-3">
                    <legend dmx-text="(prd_qnt*ord_prd_price.value).toFixed(2)" class="col-form-label-sm mb-0 w-100">Amount</legend>

                </div>

                <div class="col-3 px-0 d-none d-sm-block col-sm-4">
                    <div id="formRepeatVariations" is="dmx-repeat" dmx-bind:repeat="prod_vars">
                        <div class="row g-0 p-0 rounded rounded-1 bg-light border-info border-bottom">
                            <div class="col-5 ps-1 pe-0 align-self-center fw-normal">
                                <input id="ord_prd_var_Var_id" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_Var_id]'" type="hidden" class="form-control bg-transparent form-control-sm text-center" readonly="true" dmx-bind:value="var_id">
                                <legend dmx-text="var_name" class="col-form-label-sm mb-0 text-primary w-100 text-center lh-1">Var</legend>
                            </div>
                            <div class="col-4 ps-1 pe-0 align-self-center fw-normal">
                                <input id="ord_prd_var_Val_id" class="form-control bg-transparent text-primary form-control-sm text-center" readonly="true" type="hidden" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_Val_id]'" dmx-bind:value="var_val_id">
                                <legend dmx-text="var_val_name" class="col-form-label-sm mb-0 text-primary w-100 text-center lh-1">Var</legend>
                            </div>
                            <div class="col-3 px-1 py-0 lh-1">
                                <input id="ord_prd_var_Val_exprice" class="form-control bg-transparent border-0 text-end form-control py-0" readonly="true" type="number" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_Val_exprice]'" dmx-bind:value="var_val_prc.toNumber().toFixed(2)">
                            </div>
                        </div>
                    </div>

                </div>
            </div>

        </div>
    </div>
</form>

So, now we have our form set let’s view our Sercer Action “order_add”…

1. We add a Database Insert step inserting only the main 2 fields:
   

   

2. We add a Repeat step based on $_POST.formRepeatProducts:
   

   

3. We add a Database Insert step inserting only the 1st level Subtable’s fields and the 2nd level Subtable:
   

   

Back to our page to submit and check the results.
So, now by clicking the “Send Order” button we submit the order.

Let’s confirm that the data were inserted correctly:

And we are done with the Insert…

I’m working on the Update trying to solve a few problems and go for Part-3 / Database Update.

Any correction, instruction or advice will be much appreciated!!

For those who don’t really understand something from this small demo please “ring the bell” for explanations.

Thanks Wappler Community, thanks Wappler Team!

**

CHANGES MADE:

Here is the code for the Price and Amount Input in our form BEFORE the correction:

Here is the (small) changes that @Ben proposed and we have to make:

1. We create a ServerConnect “prod_readProductPrice” and add a single record query in it with a condition driven by a GET parameter “prid” (get the product with product_id=prid)
   

   

2. We place the ServerConnect “prod_readProductPrice” inside the “formRepeatProducts” and assign as a prid parameter the current DataStore product_id in order to pull the database product details.
   

   

3. We change the Price input value to dmx-bind:value=“srvc_GetProductPrice.data.qr_GetProductPrice.prd_price”:
   

   

4. We should change also the Amount value to match the multiple of quantity*price :
   

   

My apologies for this open door, the only reason that happened is because my project is going to be used only by autherized users with granted permissions to register client’s orders. There is no excuse, I am sorry for this.
And thanks to Ben one more time for everything!

[Valtenci]

very good, but in my humble opinion, I think there should be an easier way to pass this data from the datastore to the database

[famousmag]

Hey @Valtenci,

When I needed to implement this order form and I did a search about it here in community forum, I really wished that an easier way exists…

Here are some of the posts and solutions that guided/helped me to build my workaround on this order form:

Datastore not showing array fields when trying to add or update

Well you can use any valid javascript object structure, if you code it manually: dmx-on:click="datastore1.insert({name: 'name_1', description: 'desc 1', unit_amount: [{currency_code: 'xxx', value: 22}, {currency_code: 'yyy', value: 33}]})"

How to save data in 'DataStore (local storage)' to MySQL Table?

I have used the Pay Now button in the Checkout page as a trigger to post a form. The form contains hidden inputs with the values obtained from the Data Store as in [image] Remember that the Data Store stores the information as an array, meaning that you will need a multiple insert database action. Edit: I have just updated the Git repository using Data Store. You can find it here: https://github.com/Wappler/wCart. If you do not want to install the project, open _checkout.html and go to the b…

How Can I Pass an Array to a Server Action to Write to the Database?

Perquisites: App Connect Data Store component added, ID has been set to: responses Set the Data Store Schema, added 1 variable called: attendee_id For the purposes of the demo, I have setup a flow to insert two records to the data store on page load, my code for this is: (This will be replaced with your own method for your app) <body is="dmx-app" id="test" dmx-on:load="run([{run:{action:`responses.insert({attendee_id: 2})`}},{run:{action:`responses.insert({attendee_id: 3})`}}])"> Form A f…

How to : Pass an Array to a Server Action to Write to the Database

Step 1 : Create server side custom formatter <?php namespace lib\core; function formatter_decode($val){ return json_decode($val,true); } Step 2 : Create client side custom formatter dmx.Formatter('array','stringify', function(val) { return JSON.stringify(val); }); Step 3: Create simple form on your page <form id="formTest" method="post" is="dmx-serverconnect-form" action="./dmxConnect/api/BackOfficeV2/Testler/apiCatcher.php"> <div class="form-row"> …

(I haven’t tested yet AppConnect 2 and I don’t know if there are “tools” for better/easier approach on this subject…)

*NOTE:
The database insert was kind of easy with straight forward Inserts because wappler can handle it … The database update will be a little harder because the Update throughs an error reported here:
500 Internal Server Error when updating subtable / form repeat

I haven’t got an answer or a comment about it… So, I don’t know if:

• this is doable in wappler database updater but there will be an update fixing the error

or

• this is not doable at all in wappler database updater (not acceptable update approach)

So, I must go on and started building it out from scratch.

[Valtenci]

because, friend, there was supposed to be a component that made this transition, because think with me, in a good way what we are doing is a quick fix, imagine a huge project the difficulty of maintaining the code, if it has several forms with this example.

[famousmag]

My friend @Valtenci,

You are right up to the point that “we want to be able to build whatever we want”, because that’s what Wappler is about…
Wappler is a low-code software that give you the ability/flexibility to build the structure you want and get the results/output that you want! I’m not an experienced user but other software out there give you an easy way to do things but its up to there… Going further is not doable!

So, Wappler (Team, Moderators and the whole Community) is working on giving us more tools to be able to “automate” these “difficult” or “custom” tasks.
See custom formatters, extensions, components and more that I don’t even know about yet.
All these tools are available in wappler and many more coming, so it’s up to us to get into that game and adjust our way of working with wappler.

So, as for myself, I’m learning more day by day and getting closer to the point that I will be able to create my own library of actions, components, extensions etc according to my needs.

Until then I will just have to be careful and:

• Avoid simple/minor mistakes that cause me hours/days of delay

• Push wappler to give me only whatever is really needed and whenever is really needed

"That’s it and that’s all" (Arnold said… )

Cheers

[Valtenci]

my friend, i’m frustrated, i can’t succeed how to insert the datastore data in the table and subtable without having to use inputs and forms with teodor show

[famousmag]

Hey @Valtenci,

What is the problem? Please show us what you have so far and where is the problem

[Valtenci]

first of all i want to thank you for answering me, as shown in the video, I’m saving the data in a datastore, the next step is to put this information in a form, to be able to insert it in the database,
I didn’t want to do it this way as Teodor shows.

Inserting Data in Main and Sub Table using App Connect Form Repeat

Intro App Connect Form repeat makes it easy to insert or update nested data in your database tables using a single insert or update action. This way you can easily insert data in your main table as well as in its sub tables. In this tutorial we will show you how to store a user and its contacts. The user will be stored in the main table, while its contacts will be stored in a sub table. Database Structure Our database structure is quite simple. We have a main table called users, having an un…

[ben]

Hi Valtenci,

How else do you propose feed the info from the datastore to the database?

[Valtenci]

the form that exists and with inputs?

[brad]

@Valtenci

I’m saving the data in a datastore, the next step is to put this information in a form, to be able to insert it in the database,

Why use the middle step? Just insert it to the database? What is the purpose of the datastore? Maybe I just don’t understand what you are trying to do. But it seems you are making it more complicated than it needs to be. I know I have done that many times.

[Valtenci]

give me an example, with all due respect to teodor, but this way he demonstrates it is weird, of course in a good way.

[famousmag]

Hey @Valtenci,

I haven’t understand what is your problem…
No matter what is the problem I am sure we can solve it my friend.
In order to help you please:

1. Show us what you have (even screenshots of your desired design layout…)
2. Is this a public ordering system or a back-end type of administration order management system (used only from Administrators of your site or it is used from clients (simple users)?
3. If I remember correct you use php, right?

We can only imagine what is the problem for you right now… Give us something