howto use a subform with a temporary table generated from a json value

[plata]

Hi everyone,

i am trying to map a database field with a json list to an object on an edit form.

All data comes from a single mysql table "customers".
The json list lives in the column named "email" and is a simple list of email addresses associated to a customer entry.

(I cannot use a fixed number of columns for email addresses in the database because i want the list length to be dynamic.
I can also not use a separate mysql table and cross-references because i need the customerdatabase to be "flat" like a spreadsheet.)

If i just use an "Input" object of type "Text" i would require my webui users to write valid json (works but not ideal).

Using a "Textarea" object and transforming the json array to a newline separated list before-edit and back ?before-save? would do the job (i'm not focusing on sanitizing the input here )

For another approach I managed to use a "Select" object to display all addresses that are already in the database (the selected entry could represent a primary address, not important).
My difficulty with that approach is to create buttons to add/delete/edit list items.

My favourite solution would be to use a subform with data from a temporary table that i generate from the json list.
In mariadb 10.6.0 a json function "JSON_TABLE" was added that makes generating that temporary table in PHP quite easy.
For the sake of testing with mariadb 10.5.x i'm using the following PHP in BE (which obviously does not support a dynamic length json list but limits it to 10 items):

Code: Select all

$tmp = nuTT();

$s   = "CREATE TABLE $tmp
SELECT
  JSON_UNQUOTE(JSON_EXTRACT(email, CONCAT('$[', idx, ']'))) AS email
FROM customers
JOIN (
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  SELECT  3 AS idx UNION
  SELECT  4 AS idx UNION
  SELECT  5 AS idx UNION
  SELECT  6 AS idx UNION
  SELECT  7 AS idx UNION
  SELECT  8 AS idx UNION
  SELECT  9
  ) AS indexes
WHERE id = #USER_ID# AND
JSON_EXTRACT(email, CONCAT('$[', idx, ']')) IS NOT NULL;"

nuRunQuery($s);

But how do i depict the resulting table as an editable list (maybe in a separate window after clicking an "edit email addresses" button on the main edit form)?
The "saving-procedure" then will somehow have to translate the list back to a json array and insert it into the corresponding mysql column.


Does anyone maybe have a better idea how to do this?
Or can give me some hints on how to make any of the approaches work (preferably the last one )?

Thanks a lot,
Julian

[kev1n]

Hi Julian

Does your JSON contain a simple array like this?

Code: Select all

["email1", "email2", "email3"]

Then you could use a Select object with (Select2, Multiple ticked)

select_json.png

[kev1n]

Hi Julian,

Could you please let me know if my answer helped and if you were able to get it to work? I would greatly appreciate any feedback you can provide. Thank you!

[plata]

Hi Kev1n,

thanks for the quick reply.
Yes my json array looks exactly like this.

I tested the select object with "select2" an "multiple" ticked.
The way i implemented it, it is lacking the capability to remove or add emails from/to the list though.
But that is the important part.
Editing emails in the list could of course be done via remove+add but i would prefer it, if one could edit in-place.

select_object.png

edit_form.png

[plata]

I tried to get the subform solution to work, because the "grid" style has everything i want (adding new list items, selectively removing, in-place editing).

(i did some renaming internally, the "customers" table is now called "members" instead).
I started testing with the following persistent mysql table (member_id is the foreign key. For this test its hardcoded):

Code: Select all

MariaDB [nubuilder4]> select * from emailtest;
+----+--------------------+-----------+
| id | email              | member_id |
+----+--------------------+-----------+
|  1 | email1@example.com |        56 |
|  2 | email2@example.com |        56 |
|  3 | email3@example.com |        56 |
+----+--------------------+-----------+
3 rows in set (0.000 sec)

Then i created a subform "FF1" using that table:

FF1_Main.png

FF1_Browse.png

And put a subform object on the edit form of my main Browse & Edit

Object_Subform_All.png

Object_Subform_Subform.png

It looks like this:

Object_Subform.png

But now i want to make the same thing work with a generated temporary table.
I put the following PHP in the main Browse & Edit form's BE:

BE_php.png

which creates tables like the following (for my test entry with the id "56"):

Code: Select all

MariaDB [nubuilder4]> select * from ___nu164024a43e4df7___;
+----+--------------------+-----------+
| id | email              | member_id |
+----+--------------------+-----------+
|  1 | email1@example.com |        56 |
|  2 | email2@example.com |        56 |
|  3 | email3@example.com |        56 |
+----+--------------------+-----------+
3 rows in set (0.000 sec)

How do i make the subform "FF1" use the generated tables?
Setting `sfo_table`='#TABLE_ID#' in mysql does not work.

Thanks

[kev1n]

Do I understand correctly that the subform is solely used to display and edit email addresses and that it should be converted back to JSON when the form is saved? I don't think that a temporary table can be used in this way with a subform and you will have to resort to a Browse or use a js plugin like tabullet or similar

[plata]

Yes, that is exactly what i am trying to accomplish
Any approach that enables the application-users to modify the json array is fine by me as long as the work-flow does not get over complicated. It does not have to be a subform.

What do you mean by "resort to a Browse"? Can i use a browse form somehow to still be able to add/remove/modify the json array? And if so, which objects do i have to create and how would they work together?

Thanks for the tabullet hint. I'll need some time to look into that.
And thank you for taking the time to look into my problem!

[kev1n]

On second thought, a Browse (/Edit form) is not suitable in your case as it must be linked to a table in order to make changes.

[plata]

I found this other thread with a similar problem and some good explanations on how to use generated tables in a browse form:
viewtopic.php?t=10607&hilit=edit+table+ ... in+subform

My problem does not seem easily solveable with the preconditions i set.
So I ended up dropping my condition "not to have a second mysql table" to implement the workflow i had in mind.

The reason for keeping everything in one table was so i could easily implement CSV exports that can then in turn be used to reconstruct the database.
(Enabling git based version-control, human-readable backups, emergency roll-back to spreadsheets ... stuff like that )

What i did was implementing the translation between json-array and a second table in the import/export procedures.
It boils down to using mysql's json_extract()/json_arrayagg() functions and some joining of the two tables by id (nothing too fancy).
And in the end the subform with that second table works like a charm!