load data from DB with url request param/token (CF6)

Hi

first of all: thank you for chronoroms and all the possibilities, that come with it!

I would like to create an event registration form in chronoforms 6 and ask for support for some issues.

I have a DB table with the events details such as titles, prices etc and I want to load the data into the form (DBRead) with help of an param/token in the url.
I have tried a lot of possible solution I found in the forum and the howtos, but I had no success – maybe because most of them are for CF4 or CF5.

The situation:
Let's say I have an url:
https://www.domain.tld/registration.html?event_id=34

and a table with the event details, jos_chronoengine_events with the following fields:
id (key), event_id (varchar), event_title (varchar), prices_adult (array like {"300":"300","200":"200"} or {"500":"500"}) … (and some more, which are not of importance a this time)

With the event_id in the url I would like to load from the DB the data with the same event_id:
1) the event_title in a dropdown menu (with the event title as single preselected entry)
2) the array of the prices_adult in a dropdown menu (with one two or three entries) and the first price is preselected

So how do I do this in CF6 (not CF5 or CF4).
As I'm not a real coder I possibly need instructions for dummies …

Thank you
Hekla

Hekla,

In the DB Read action Where conditions box please try event_id:{data:event_id} or event_id:{url:event_id} - I'm not sure if the URL variables are added to the data array or the url array.

Bob

Thank you Bob.

A follow up question:
and how do I load the data into the dropdown menu?
I couldn't figure that out …

Hi hekla,

If your Read Data action "Select type" is set to "Key/value" pairs then you can just add the following shortcode to the "Options" box in the dropdown:

{var:read_data_name}

Best regards,
Max

Thank you Max,

somehow I don't do it the right way – the dropdown menu stays empty.
It seems I really need basic instructions for chronoform beginners, sorry!

my questions:
[list]I guess I have to replace "read_data_name" in {var:read_data_name}, but with what exactly?
How does the dropdown menu "know" that I want the content of the DB field "event_title" to be loaded?
Are there other settings/params I have to set correctly (there are so many of them)?
[/list]
Thank you.

Hi hekla,

"read_data_name" is the name of the "Read data" action, please check this FAQ:
https://www.chronoengine.com/faqs/74-chronoforms6/5287-dynamic-dropdown-options.html

If you still can not get it to work then please let me know and I will prepare a tutorial with images.

Best regards,
Max

Hi Max,

Thanks. Unfortunately I still don't get it. The FAQ seems clear, but the dropdown menues stay empty.
A tutorial would be great, also for potential successor.
And I'm also happy to let you look at the form and to pay for your service.

Greetings
Hekla

Hi Hekla,

Please send me a message using the "Contact us" page to get a link for the latest manual revision which should hopefully help.

Best regards,
Max

Max, thank you for the manual! It helped me to take some steps and to understand chronoforms some more.
I succeeded to get DB Read to work – it loads the correct event title and price according to the request params in the url.

I did not succeed though with dispalying the retrieved data correctly:

Question 1)
When I load the event title into a dropdown menu, then it is displayed correctly: This is my event title
When I load the event title into custom html, then it isn't displayed correctly: {"This is my event title":"This is my event title"}
How can I display it in custom html in the way: This is my event title?

Question 2)
For each event I have an array in the DB with the prices to choose from, e.g. {"230":"230","170":"170"} or {"290 - Privat":"290","350 - Non-Profit":"350","450 - Business":"450"}'
How can I load them into a dropdown menu as label:value pairs to choose from?

Thank you.

Hi hekla,

What is the format of the data stored in the database table in both cases ?

What is the shortcode you have used to output the data ?

Best regards,
Max

Hi Max,

in question 1 it is it a varchar field and just the text: This is my event title

in question 2 it is an array, displayed as saved in den DB: {"230":"230","170":"170"} or {"290 - Privat":"290","350 - Non-Profit":"350","450 - Business":"450"}.
The formatting of the array in the DB does not necessarily have to be like this – it can easily be changed. When you say its better to have it in a different format then I can do it (the table is filled once a year for the whole event season)

Thank you
Hekla

Hi Hekla,

I think that you using a wrong "select type" in the "read data" settings.

Did you check the "Read data" section and the "shortcode" section in the manual ? they explain the data returned and how to extract what you need.

So for the custom html you will need to set the select type to "first matching" and the shortcode should be {var:read_data_name.Model.field}

For the dropdown options you need to set the select type to "key/value pairs" and set the options setting to {var:read_data_name}

Best regards,
Max

Hi Max

I went through the manual again – still learning and improving :-)

For the dropdown options you need to set the select type to "key/value pairs" and set the options setting to {var:read_data_name}

I did it, but the output stay the same, e.g. {"230":"230","170":"170"}
Or do I need to have them in a different way in the DB?
I just did it like param arrays are represented in Joomla

An other question:
I need all the information in the DB which is called by the request param.
Do I need to have an extra DB Read for every cell in the DB of is there a way of combining it?
For instance: I already get the event title with {var:read_data_name.Model.field} and Select type to "First matching record".
Is it possible to get the price array with the same DB Read, even though select type is set to „key/value pairs“?

Hi hekla,

Apologies, I forgot to reply here earlier, please let me know the current issues you have.

Best regards,
Max

Hi Max,

no problem and thanks for coming back.

my main question is:
in which format should the data in the DB be saved, that I get a dropdown menu.
I have one to four values there – depending on with table is loaded – and I would like to get a dropdown menu with these options.

And I have been preparing a requirement list for my form and will ask you for a payed job soon.

Hi Max

I just sent you and Bob a detailed description about my form and a request for a quote by email.

Thank you
Hekla

Hi Max,

I would appreciate to get some support.

1)
Could you please give me an information, if the form, how I would like it is possible with CFv6 and if you can do it.
I need the form by mid of September and would have to find an alternative like paid service at Breezing Forms or similar – but I would prefer to stay with chronoforms.
I attach the form description here again.
As I said, I willing to pay for support – I just need it.

2)
or at least if I could get a solution for my main question

in which format should the data in the DB be saved, that I get a dropdown menu.
I have one to four values there – depending on with table is loaded – and I would like to get a dropdown menu with these options.

Thanks,
Michael

Hi Michael,

You need a dropdown "menu", like a website menu ? or just a dropdown field ?

Best regards,
Max

Hi Max

Oh, I'm not sure about the difference. Just have a look at
https://registration.zegg.de/en/?event_id=17101 under "Price", right on the top.

And in fact, I also need the same with radio buttons like under "Accommodation" in the given form.

Thank you.

Hi hekla,

So you need to populate the dropdown with the read data values, you need to use the request parameter in the "where conditions" of the "read data"

table_field:{data:url_param}

And then set the "select type" to "key/value" pairs
Then go to your dropdown and set the options to:

{var:read_data_name}

This is explained in details in the manual available on the downloads page!

Best regards,
Max

Yes I got that.

But how should the data be represented in the DB so that it is read correctly and displayed in a dropdown menu like
- 230 € - full price
- 170 € - reduced price
- 120 € - sponsored price

When I try I get the DB table as stored in the DB:
{"230€ – fullprice":"230€ – fullprice (1)","170 € - reduced price":"170 € - reduced price (2)","120 € - sponsored price":"120 € - sponsored price (3)"}

Or when I try with
["230€ – fullprice"=>"230€ – fullprice (1)","170 € - reduced price"=>"170 € - reduced price (2)","120 € - sponsored price"=>"120 € - sponsored price (3)"]

then I get a dropdown menu with:
"230€ – fullprice (1)","170 € - reduced price"=>"170 € - reduced price (2)","120 € - sponsored price"=>"120 € - sponsored price (3)"]

So to cut it short:
I want a dropdown menu with two or three entries, which are loaded with DB Read.
How do I store the date in the DB, so they are read correctly and displayed as dropdown menu?

Thanks
Hekla

Hi Hekla,

What do you add in the dropdown options box ? could you please send me sceenshots of your settings or a form backup ?

Best regards,
Max

Also the "Read data" action should be BEFORE the "Display section".

Best regards,
Max

Also the "Read data" action should be BEFORE the "Display section".

Yes, this is the case

An here come the screenshots:
Dropdown:[attachment=84099_20170901013849_dropdown-png.png][/attachment]
DBRead:[attachment=84099_20170901013906_dbread-png.png][/attachment]

Hi hekla,

Please drag a debugger after the read data, what do you get ?

Best regards,
Max

I get

Array
(
   [read_data11] => Array
        (
            [log] => Array
                (
                    [0] => SELECT `loadprices`.`prices_adult` AS `loadprices.prices_adult`, `loadprices`.`prices_adult` AS `loadprices.prices_adult` FROM `z0reg_chronoengine_events` AS `loadprices` WHERE `loadprices`.`event_id` = '17102';
                )

            [var] => Array
                (
                    [["290€ Privat" => "290€ Privat (RP1)", "350€ Non-Profit" => "350€ Non-Profit (RP2)", "450€ Business" => "450€ Business (RP3)"]] => ["290€ Privat" => "290€ Privat (RP1)", "350€ Non-Profit" => "350€ Non-Profit (RP2)", "450€ Business" => "450€ Business (RP3)"]
                )

        )

)

and in the DB from where the data is loaded there is the following entry in the filed "prices_adult"

["290€ Privat" => "290€ Privat (RP1)", "350€ Non-Profit" => "350€ Non-Profit (RP2)", "450€ Business" => "450€ Business (RP3)"]

Hi hekla,

Ok, the read_data is just returning what you have in the field, I'm not sure why do you have multiple values stored in the same field ? are you able to change the table so that only 1 value is stored inside the field per record ?

if not then the returned values will need to be modified using PHP, I can help you with that but it's better to have one value per record.

Best regards,
Max

The table can be changed in any way we need.

So, do you mean something like
price_adult1
price_adult1_val
price_adult2
price_adult2_val
price_adult3
price_adult3_val

yes, this would be possible.

I have two more "difficulties" to master:
1) sometimes there are only 2 prices to choose from and sometimes there is only one price. Can I have displayed only 2, respectively 1 price in these cases?
2) When the dropdown works, I would like to have an if-then condition:
if today is before a given date (table "earlybird_date", type date) from the db: choose the price from the table price_adult_earylbird
else: choose the price from the table price_adult

Maybe with these "difficulties" it would be better to use PHP anyway?

Hi hekla,

I'm a bit confused, could you please copy the value stored in the field here ? please use phpmyadmin to get the value so I can understand in which format you have it stored.

You can have an "Event switcher" action to check the date and run one of two different "Read data" actions, the "date check" itself will happen inside a PHP action which will provide the check result to the "Event switcher" action.

Best regards,
Max

Hi Max,

I tried different formats:
["230€ – fullprice"=>"230€ – fullprice (1)","170 € - reduced price"=>"170 € - reduced price (2)","120 € - sponsored price"=>"120 € - sponsored price (3)"]

and

{"230€ – fullprice":"230€ – fullprice (1)","170 € - reduced price":"170 € - reduced price (2)","120 € - sponsored price":"120 € - sponsored price (3)"}

If you suggest an other format, I'm happy to change it according to your specification.

Thank you,
Hekla

Hi Max

I had a look at "event switcher", also in the CFv6 manual, but it is nor cleat to me, whot I have to do to check the date of the DB against the current date …

Could you please help me with that too.

Thanks
Hekla

Hi Hekla,

So you have multiple values stored in 1 single entry ?

What value would you like to receive from the dropdown ? the whole "230€ – fullprice" string or just "230" ?

You want to do the DB date check when you are reading the data ? in this case the check must be in the WHERE conditions.

Best regards,
Max

So you have multiple values stored in 1 single entry ?

Yes at them moment it is like this, but I could change that if needed for the form to function.
The result I want is a dropdown menu with 1, 2 or 3 option to choose from, depending on the loaded values.

What value would you like to receive from the dropdown ? the whole "230€ – fullprice" string or just "230" ?

The whole string

You want to do the DB date check when you are reading the data ? in this case the check must be in the WHERE conditions.

Yes, that would be good. The visitor of the form then immediately sees the right prices.

Thank you
Hekla

PS. And remember, I'm also willing to pay or donate for a working solution.

Hi hekla,

You have multiple values in one field then your "read data" will need to retrieve the first record only, unless you want to mix the values or multiple rows into one single dropdown.

It's also not yet clear for me in which format the multiple values are stored, I would like to check the results returned by the read data from the debugger output, could you please add a debugger and post the results here ?

Best regards,
Max

You have multiple values in one field then your "read data" will need to retrieve the first record only

OK, I changed the "read data" to first record only.
Now in the dropdown I get only one selection "Array", not the content of the table

… unless you want to mix the values or multiple rows into one single dropdown

No, no mixing

It's also not yet clear for me in which format the multiple values are stored

They can be stored in any way we want.
At the moment this is:
["290€ Privat" => "290€ Privat (RP1)", "350€ Non-Profit" => "350€ Non-Profit (RP2)", "450€ Business" => "450€ Business (RP3)"]

So what format do we need it to be stored for the dropdown?

I would like to check the results returned by the read data from the debugger output, could you please add a debugger and post the results here ?

Array
(
[read_data11] => Array
(
[log] => Array
(
[0] => SELECT `loadprices`.`prices_adult` AS `loadprices.prices_adult` FROM `z0reg_chronoengine_events` AS `loadprices` WHERE `loadprices`.`event_id` = '17102';
)

[var] => Array
(
[loadprices] => Array
(
[prices_adult] => ["290€ Privat" => "290€ Privat (RP1)", "350€ Non-Profit" => "350€ Non-Profit (RP2)", "450€ Business" => "450€ Business (RP3)"]
)

)

)

)

Hi hekla,

strong apologies for missing this post, what is the latest update you have on this ?

Thanks for your answer.
I went on with this issue in topic "radios group with data from DB" (http://www.chronoengine.com/forums/posts/t104314/radios-group-with-data-from-db) as my question there proved to be the same as here.