How to Use Database Multi Insert - Can You Help Me Please?

[Antony]

I'm trying to do my first multi-insert and am going round in circles... could someone tell me where my simple error is please?

Situation

I have a test_item table with various fields like first_name, last_name. A test_item can also have one of several regions they are associated with, so I have:

• A lookups table with columns id and lookup_name which provide the list of regions.
  In my example I have lookups[8]=North, lookups[9]=South.

• A test_item_regions table to store the list of regions for each test_item.

Setup - Array of Selected Regions - selected_regions

I have created a user interface to select the different regions:

These are stored in the array selected_regions. I know this is populated as it creates the display showing North and South in the above image.

Setup - Server Action - update_test_item

In my Server Action, I bring the reference to my array selected_regions through with a $_GET:

and then created a multi-insert

In the Database Insert part of the multi-action, I insert the array value as follows:

And to set the $_GET variable, back in App Connect I have a Server Connect element for the Server Action, setup as follows:

What Happens

The form which activates the update_test_item Server Action:

1. Writes the simple field values such as first_name, last_name okay
2. Deletes existing entries in test_item_regions okay.
3. The Insert Multi doesn't insert the new values.

What I'd Like to Know

Is where I have gone wrong in passing the array selected_regions through to the Server Action multi-insert!

[George]

Maybe this will help you Antony:

Best practice Dynamic Checkbox arrays per DB row or seperate DB rows?

Hi Freddy, In order to store the checkboxes as new rows you need to: All of our checkbox names must be the same, followed by [] For example: name="mycheckbox[]" and add different values to them: [Screenshot_8] 2, Import the form under globals, the checkboxes will appear as an array multiple in POST vars: [Screenshot_9] Add repeat step, and repeat the POST var created by your checkboxes: [Screenshot_10] Add the insert step there, inside the repeat, and as vallue select $value return…

And also:

Database: Multiple checked records in a separate table

Brian, essentially in your server connect action for updating the checked selection, you need two things: Delete all checked records for the order record Loop through the id’s and Insert checked records You just have to make sure have selected the $_POST.fid as loop expression and inside it, the current id of the repeater, you can access it with $value

[Antony]

Thanks for that @George… (especially on a Sunday evening! )

Those situations are quite different to mine… and they may provide the answer but not without several more hours of experimentation, and my patience has run out with regard to trying lots of things that just don’t work…

It would seem to me that writing an array into the database is a pretty fundamental requirement of a app development system, so I would rather wait and be pointed to some documentation that just shows me the steps to do it!

Best wishes,
Antony.

[George]

So how are you passing an array with $_GET?

As usually only simple strings are passed there. If you are passing a comma separated list you need to split it by comma to get the array out of it.

[Antony]

Well @George, I am just rather confused!

It is very hard to know what to search for on the forum to know how to find the answer...

As I try to find my way around doing this, I read a post when I started on Friday which I recall showed doing this by passing an array through a $_GET... but I've opened so many posts and tried so many things that I can't be sure which post it was now...

... so the $_GET may be a complete "red herring" as we say in England (like something leading me in the wrong direction!).

What I really want!

In reality, all I want to do it to write the contents of my selected_regions array into the database table test_item_regions along with the id of my test_item (e.g. 1). So if my selected_regions array contains the values 8, 9, 10 then I want to write the entries:

1, 8
1, 9
1, 10

and I want this to happen when I submit a form with some other data in such as first_name, last_name.

I hope that helps to explain the problem!

Best wishes,

Antony.

[George]

Well I’m just trying out to understand what you have done.

Normally people use checkboxes for this and when they get submitted you can save them as indicated in the articles above.

You definitely shouldn’t use GET for posting data.

So make sure your form is with method post and you use $_POST in your server actions.

You can also for debugging check in dev tools how the form is submitted and see it’s data.

[Antony]

Hi @George…

Thanks for the feedback about not using $_GET. That is really helpful!

So what I need to know then is how to send the value of an array to the Server Action via a $_POST variable.

I am really unclear on how to do this!

Best wishes,
Antony.

[George]

as a small tip on passing multiple values:

If you use a multi value select control - its name has to end with [] - to indicate it is a multi value/array
like <select name="myvalues[]" ...

This is usually done automatically within Wapplers standard selects or multiple checkboxes - but I see you are using a custom one so it might not have being done.

[Antony]

Thanks for that @George!

Yes, I have built my own nice little multi-select…

very much like the one in the docs… which as per the one in the docs, saves the values to an array variable. The challenge I have is that the one in the docs doesn’t go as far as actually writing the values selected into the database, so I have been a bit stumped.

So are you saying then that if I have a hidden field with name=selected_regions[], and I dmx-bind to it the value of my array (so selected_regions or selected_regions.items?) then that should work?

Best wishes,
Antony.

PS - Here the the docs page I refer to…

Working with Arrays

[George]

yes - client side arrays - have nothing to do with server side arrays.
When you submit a form its values are passed as key/value strings to the server side that has to parse them and make its values that needs to be converted as well.

server side is smart enough to see that if you pass multiple values with the same name and [] to merge them in an array.

But if you pass a single value like from a hidden field, with a single name - then you usually pass it as a comma separated string and then server side you need run it through a formatter to split on comma and convert it to array.

[Antony]

Aaaahhh… okay…

So now I have a hidden input test_item_regions with the value set to selected_regions.items. That works, and has the values 8,9,10.

Then when I bring that over to the Server Action, is this what I do?..

And then have a repeat based on that?

[George]

Exactly!

[Antony]

Yippee… it works!

Thanks @George!

[Antony]

I've just written up how to do this, as it can be really time consuming trying to work things out from a long rambling forum post like this!

Here is the post:

Multi-Select Inputs - How to Write an Array to the Database

So you may have an input that can have more than one value. You can use the article from Wappler Docs to see how to create a nice interface for selecting the multiple values, which is linked to below. You then end up with an array of values. But how do you write that array to your database, maybe along with other single dimensional data? Let’s assume you have: An array to write to the database called my_array A form that writes the other data to your database too You want to insert a row…

Best wishes,
Antony.