Get query results into an array

I am thinking of creating a config table to hold various values for the web app making it easier to change things globally later (and the client to change them, too) so I have a simple table containing just a couple of fields - configName and configValue. I will then store things like…

configName: 'admin_email'
configValue: 'admin@domain.com'

There probably won’t be many records but I will likely be adding to them.

I want to create a query in my globals api to get all the records and store them in an array so I can then use them easily throughout the app.

So the above example could be used like this:

config['admin_email']

to get the value of 'admin@domain.com'.

Is this possible or does the array have to be referenced with it’s id, eg. config[1] ?

And if this is possible, what’s the best technique to achieve it and also the most efficient?

I hope this makes sense.

I may be wrong, but would it be better if you create a config table with admin_email as a field/column

Thanks Ben. Yes, that’s one way but it means editing the database structure when adding new settings. I want to create something that can easily grow just by adding records.

Have you considered using the JSON data type that was recently added?

Thanks Ben. I did wonder about that but I’m not sure it will give many benefits from @ben’s suggestion.

To my mind, having a table with multiple records is the easiest to maintain and getting that data into an array is the easiest to then use. But my experience with arrays isn’t too great!

You could create a variable like this:
<dmx-value id="configtext" dmx-bind:value="sc_config.data.qry.toKeyedObject('configName', 'configValue')>

.. and then use an expression like this for displaying the values:
{{configtext.value.admin_email)}}

Ooh, that looks like it could be exactly what I need. Thanks @TomD. I will give it a go.

Is there a way to do this within server connect? I want to set this up in my globals.

You could just create a single query in server connect to get all the terms (value pairs), and then probably cache them. (I'm not sure if this is answering your question.)

I want to get values which will mostly be used in server connect actions (like sending a notification to an admin email when someone uploads something) but I may also use them in the front end.

The JSON data type support with Wappler is quite flexible. You would have an entry for each customer/user that needs a config and the JSON contains all the relevant fields you want, already in keyed objects. If you want to add an option, simply add it to the UI in Database Manager and it will be usable in queries/data picker - no client-side manipulation required. The database is not being changed each time, you are just specifying the structure of what is stored in an already present field.

Cool. I’ll give that a go. Thanks @bpj.

1 Like

If you are using NodeJS and don't need to keep these values in the DB, you can use environment variables.

Jon, if you don’t have any reason to not go with JSON you should. If at some point you need to nest data you will be grateful for not having gone with arrays over json.

1 Like

Thanks Sid. This project is PHP I’m afraid.

Yep, I’m going with the JSON solution. :+1:

I’ve created a new MSSQL column with array text in it.
Then I went to DB manager and changed the datatype on that field to JSON/Array.
(as you reference in this post)

But that column still has a “A” Text icon whenever I see my ServerConnect structure as I’m trying to select fields.

Am I supposed to do something to update all the references to this manually-changed column?

thx